April 12, 2011 at 2:30 am
I get a timeout exception when running a query that takes almost 33 seconds on management studio. Even if I set command timeout to zero or a larger number. Tried executiontimeout on the config file. Set Connect timeout on the connection string to a larger number. I used to get results from my development machine but it fails when deployed. Now it fails on my development machine as well.
It looks like the commandtimeout settings have no effect and are overpowered by the 30 seconds default.
Please help!!!:-)
April 12, 2011 at 7:37 am
This .net application, is it making a Web Service call or is it a direct programmed connection string?
Is there a firewall between the PC this app runs on and the server?
Have you put a network sniffer on the app / PC to see what the problem is?
Have you run PING and TraceRT from the PC the app runs on to see if it can connect to the SQL Server?
EDIT: Have you checked the SQL Server remote query connection timeout setting?
April 12, 2011 at 7:43 am
I have tried to optimize the underlying tables of my view as my stored proc is reading from the vie. One part of the sproc that retrieves few columns does respond correctly now. The other part does not.
The connection is fine, just that the sproc takes about 32 seconds. I thought I would change the commandtimeout to be just more than this. The commandtimeout seems not to be working.
Thanks for the prompt response.
April 12, 2011 at 7:52 am
You probably didn't see my edited comment.
Check the Server / Instance's "remote query timeout" property. This can be found by right-clicking the server\instance name, navigating to Properties, the clicking on the Connections page. About 2/3rds of the way down is the "Allow remote connections" check box, and right underneath it is the query timeout value.
This setting could be set very low and you not notice it in SSMS, but only in the .Net application. Make sure it's set properly (0 is not a good value to have, even though it means "no timeout", because it allows infinite loop-type queries endless connection to the server). Adjust it in increments to see if it helps your application.
April 12, 2011 at 8:01 am
Thanks,
It's 600, big enough for my query.
April 12, 2011 at 8:52 am
don't you have to set both the connection timeout and your command object's command timeout?
this works fo rme for example, waitng 45 seconds to make sure it lasts longer thna the default 30 second timeout:
'form a command that waits longer than 30 seconds for testing
Dim sqlcmd As String = "WaitFor Delay '00:00:45';SELECT name FROM sys.tables;"
Dim mySqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Connect Timeout=600;Application Name=GhostInTheMachine;"
Dim MyConn As New SqlConnection
MyConn.ConnectionString = String.Format(mySqlConnectionFormat, ".", "SandBox", "Noobie", "NotARealPassword")
MyConn.Open()
'now lets get a commadn object
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = MyConn
mySqlCommand.CommandTimeout = 600
mySqlCommand.CommandType = CommandType.Text
mySqlCommand.CommandText = sqlcmd
Dim myDataReader As SqlDataReader
myDataReader = mySqlCommand.ExecuteReader
Dim MyDataTable As New DataTable
MyDataTable.Load(myDataReader)
Lowell
April 12, 2011 at 6:41 pm
Thanks again for prompt responses.
To Brandie: I think the remote connection timeout is used by the SSMS when connecting to other servers not the application. Please verify.
I had all the settings correct, connect timeout, commandtimeout, and executiontimeout and debug set to false. I have it working now. The only thing I didn't mention to you all is that I was not using SQL Client. I was using Corelab Unidirect. This might be a bug or I'm using an old version.
Tried SQL client and it's working like a charm.
Thanks again for your contribution as it gave me direction. I will throw the Unidirect Library far away.:-P
April 13, 2011 at 4:08 am
thulani.moyana 79207 (4/12/2011)
To Brandie: I think the remote connection timeout is used by the SSMS when connecting to other servers not the application. Please verify.
So far as I know, a remote connection is a remote connection, no matter if it's coming from a server or an application.
I'm glad you resolved your problem.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy