Query Analyzer....

[Vaderman]

Hi all,
I'm running a simple select * query in the QA. However, in QA it continues to state that the query is running which maybe taking about 2-3 mins!!
BUT, in the SQL profiler it comes up with batch completed, even though the query is still running in QA!!

Can anyone shed any light as to whats going on?
This is an actual Insert Into... Select [Column] that is causing problems within my application in that it times out after the default number of secs.

Regards

John

[cjard]

how long does the select query take to run? are there any triggers on the insert table that may be slowing things down?

[Vaderman]

The Query is as follows :

Insert into TableName
select T.ID, T.RecordType, T.Status, T.Available
from (TableName T inner join Selection S on S.selectionID = T.ID)
                              inner join Assessement A on S.EventID= A.ID
where A.highlighted = @InputParameter

The above query works as expected as it returns the number of rows as expected for a given @InputParameter

However, the following SQL is where the problem lies:

select count(*) Select_Count from TableName
where RecordType = 'T' and Status='C' and Available = 0

All the above SQL is wrapped up into a stored procedure and is called within my application.
But the last SQL select statement above times out after 30 seconds in my app, but in the Query analyzer it continues to be executed for as long as 10 minutes. There is no reason as to why it keeps on executing or timing out as the rows returned maybe 0 or 1000 (max) at any given time.

In the Profiler, *sometimes* it will state that the last SQL has completed, but in the Query Analyzer it will be shown that its still executing.

I think it may prove tto be helpful that these two SQL procedures are run when there is alot of traffick on the database server network.
Any explanations?

[Vaderman]

OK.. let me ask this question. In the profiler, I am tracking a Stored Procedure and it has Audit Login when it starts the SP and Audit Logout when it completes the SP.

Does this mean that my application has logged in and out of the database?

If this is the case, why has it not done this with my previous Stored Procedures?