Query Time Out
-
This question is about executing a query accessing a table with several million records with groupby and two sum columns. This query runs fine in Query Analyzer (1:56) But in Entriprise manager it will timeout. I already set the connection property to 0 which should not give a timeout limit. So why is this query timing out. Seems to involve the OLE DB driver.
-
What you are setting is the CONNECTION timeout (how long to wait while making a connection to a SQL instance, not the QUERY timeout (how long to allow a query to run for).
Enterprise manager uses ODBC to execute the queries, and unfortunately (despite a lot of searching), I know of no way to change the default ODBC timeout.
-
I am setting the connection time out property.
-
there are 2 thing to check
1- run regedit
and go to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC
and change connectionTimeout to 300 for example (5 minutes)
note: 0 = unlimited
2-open EM, then from Tools menu > choose options
then select andanced tab, and in login time out , make it more than 4 seconds ... (0 = unlimited)
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com -
You are discovering one of the subtlties between the two. The EM has a great gui builder, the QA does not. The EM times out, the QA does not. Rather than fight it, I work with it. I use the EM for prototyping, then cut and paste the sql into QA for production running.
-
This query takes 15 minutes to execute. QA works fine. I have set the connection to 0 and the ODBC timeout to 0... still it's a no go. The problem is that I need to run this remotely via a COM object, so it's the ODBC timeout that is the issue. Will need to resolve this by breaking up this query into smaller quicker steps.
Thanks to all for the quick response and excellent suggestions.
-
How is the COM object going to run the query?
If the COM object is going to run the query using something like ADO, then it's easy to change the ODBC query timeout at run time.
It's only when running the query using Enterprise Manager that you are likely to have the problem, as you don't seem to be able to change the timeout from there.
-
I ran into this also, but it was in Access hitting linked SQL Server objects. Check out MS Knowledge Base Article 247070. It looks like the problem is in the MDAC. I installed the latest version 2.8 and it still seems to have the problem.
What I like best is MS's "resolution". It's like the old joke: "Dr., it hurts when I do this." "Well, then don't do that."
Hopefully they will fix it eventually...
So long, and thanks for all the fish,shang
atk.com -
Howdy,
It is and always has been a "bug" with Enterprise Manager. I don't think you'll find a fix for it even on the Microsoft site (I've look for this fix about once a week and nothing yet. Not sure if it's in MDAC or not. Seems like a lot of other stuff would break as well if it were in MDAC.
How about the EM "Bug" that won't handle CASE statments?
I really like the visual JOIN building capabilities of EM so I build my "raw" SQL in EM and then flop the code into QA when I'm ready to add things like CASE. Not real convenient but, of course, works.
Dear Microsoft... would you fix this junk, please?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply