I currently develop an application for my company that actually uses rather long queries, with many records.
I have a particular query (Written using SQL string inside the .NET application rather than Stored proceedures),that needs to run in 2 databases (both SQL Server):
The first one is a test database that we use when in developing time quota to test our data
The second one is the real thing a data base that contains lots of records.
When criteria are placed in the query, it returs few records in both the databases , but if no criteria are placed (So it fetches all the records..) In the test Database works ok, but in the real one it "jams" till 30 seconds pass and I get a time out message...
I tried to change the Query time out time from inside the SQL Server from
but it doesn't seem to work out... it still times out after 30 secs
Could it be that this is not a SQL problem but an ADO-ADO.NET problem, i'm sure the default commandtimeout for command objects in ADO-ADO.NET is 30 seconds if not specified, specify 0 for unlimited timeouts and a value in seconds if you want different from the default.
I'd go for a 45 seconds property on the commandtimeout property...
P.S. If i'm off base here I appologise, this is my first post, total newb...
Thanks a lot for the advise Dreamweaver, but I need to specify that I tested the queries from inside the SQL Server enterprise Manager creating two new Views (One in each separate Data Base) and pasting in their SQL section the SQL string that I "Pinched off/Copied " from the debugger of the .NET just before it is fed to the adapter...
So I have side by side two new viewes with the exactly same SQL, but belonging to two different data bases...
I've just been looking at the settings on a server I am looking after. Surprisingly the timeout properties in Tools/Options/Advanced differ from right clicking the server and selecting properties and then looking at the bottom part of the connection tab, on this server it has;
0 in Tools/Options/Advanced
600 in Server Properties/Connection Tab
I'd have a look in right click Server/Properties/Connection Tab and see if that says 30,
first set sp_configure to display advanced options
execute reconfigure with override to force a "LIVE" change
then set remote query timeout to an appropriate value.
run reconfigure with override again.
EXEC sp_configure 'show advanced option', '1'
Here is the message:
Configuration option 'show advanced options' changed from 0 to 1.
Run the RECONFIGURE command to install.
RECONFIGURE with override
--XXXX change to query timeout value (example '800')
sp_configure 'remote Query Timeout', 'XXXX'
I was so tired at the time i wrote this that i lost myself in coloring the code in the message posting area