MS SQL 2005 timeout problem

[ThomasSoerensen]

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?

[crack_ho]

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.

[ThomasSoerensen]

thanks - I will try this tonight

[ThomasSoerensen]

The only thing to do is to run the query in a query window so type our 'SELECT* from ...' rather than the right click.

this works - thanks

[lightng]

Try upping value in registry of...

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

[ThomasSoerensen]

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

[eyetie]

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

?

[teclo]

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.