• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

MS SQL 2005 timeout problem

ThomasSoerensen

Super poster
Joined
Jul 20, 2007
Messages
3,055
Likes received
8
Thanks received
2
Location
Germania
Hi
I am working in the Management Studio on a MSSQL2005 and on a complex view I get a "Timeout expired" message after 30 seconds when I try to execute the query.

I have looked in several places and changed the timeout settings from the standard 30 sec to something else like 120 sec but I still get the timeout problem.

It is my own database so I can change settings without restrictions.

Do you guys know the magic bullet for this problem?
 
Seen this myself and can only offer a workaround.
If you right click on an object like a table or view and return the resultset - it will always impose the 30 seconds timeout.
I have tried changing the value in the client tool and the default on the database but neither affected this limit.
The only thing to do is to run the query in a query window so type our 'SELECT* from ...' rather than the right click.
 
Try upping value in registry of...

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout
 
Try upping value in registry of...

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\DataProject\SQLQueryTimeout

I had already done that.

crack_ho's tip is working.
Now I just need to learn how to "package" the execution of the view with a stored procedure or something similar
 
I had already done that.

crack_ho's tip is working.
Now I just need to learn how to "package" the execution of the view with a stored procedure or something similar


Can't you do

CREATE PROC GetViewData
(@Param1 varchar(100))
AS
SELECT * FROM MySlowView WHERE Field1 = @Param1

?
 
I was having a similar problem, until I used "no lock" in the query. That fixed the problem and I haven't had any time out issues since.
 

Advertisers

Back
Top