Select when the app is open not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Select when the app is open not working

Author

Topic

Fatalsniper
Starting Member

45 Posts

Posted - 2005-11-21 : 20:49:00

Hey guys,

When I'm using my client app (VB 6)which is connected to SQL 7 via ADO, if I'm using Query analyzer or enterprise manager, I just can't see the result of a query until I close my the app...not sure why that, and how to fix it...any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-21 : 21:11:01

When your app is connected and you run your query in the Query Analyzer is your query BLOCKed ? or No result return ?

[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-22 : 00:41:48

When running the application, Run the Profiler and see what happens
Also run sp_who,sp_who2

Madhivanan

Failing to plan is Planning to fail

Fatalsniper
Starting Member

45 Posts

Posted - 2005-11-22 : 22:33:59

Just as Khtan says, when my app is connected and I run a query via query analyzer it gets blocked, and as soon as Close the app it gets unblocked and I'm able to see the results...no idea!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-22 : 22:39:46

Did you run sp_who or sp_who2 and check for blocked processes?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Fatalsniper
Starting Member

45 Posts

Posted - 2005-11-22 : 22:50:52

Yes I did, and when my app is open I see three procesess related to the user that is logged via application, with status = "sleeping" and command = "AWAITING COMMAND" also the query I ran on Query Analyser with the sa user that returns: status = "sleeping" and command = "SELECT" and BlkBy = 8 as result of sp_who2...what's wrong?

Thanks!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 00:30:21

What does your application do?
Check whether it is doing any time consuming tasks such as transfer data, querying large table,etc

Refer this also
http://www.mindsdoor.net/SQLAdmin/sp_nrInfo.html

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-23 : 01:22:48

The App might have a BEGIN TRAN and did not COMMIT or ROLLBACK util it quit.

-----------------
[KH]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-23 : 02:06:16

My guess is that you have the issue KH is referring to. You need to figure out what the process is doing that's blocking and fix it in the application. You might want to look up blocks in Books Online and read up about them. You're going to need to figure out what the blocking process is (in the case you just ran, it would be process or SPID 8) and address from there. You can use Profiler for this. You can also use DBCC INPUTBUFFER(8) (replace 8 with whatever the spid is from running sp_who2) and combining that with the information in the sp_who queries to troubleshoot.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Fatalsniper
Starting Member

45 Posts

Posted - 2005-11-23 : 17:35:33

That's right I have a Begin Trans at the moment I open my app and I commit transaction at the time the forms are all complete
...rollback, if it is closed without saving...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources