Tuesday, March 13, 2012 8:17 PM
SQL Server 2000, VB.Net 2010
I am executing a stored procedure from VB.Net in a database on one server, linking
to a database on another server. There are about 380 million rows in the table on
the other server. I select on the primary key, an int, to start with a value just
before the most recent three months. I also select on a datetime index for a date
range of one month.
When I run the stored procedure in SMS (2008), it gets results in about two minutes,
returning about 6.7 million records. When I run it with the VB program, it times
out in about 30 seconds. Setting the timeout longer on my connection string to
the database I am running in has no effect.
Is there a way to increase the time before timing out?
Tuesday, March 13, 2012 9:01 PM
Use the code below to Set the timeout.
30 in the code below is 30 seconds. 0 will be Infinite wait.
sp_configure 'remote login timeout', 30 go reconfigure with override go
- Proposed As Answer by Lalith Sarika Tuesday, March 13, 2012 10:29 PM
Tuesday, March 13, 2012 9:12 PM
Thanks. That's a good idea.
I tried it in our DEV database and did not have the permissions to execute it even there.
If no one comes up with anything else, I will ask our DBA's about doing it.
Tuesday, March 13, 2012 10:33 PM
Lalith's suggestion will affect all the linked servers on your server. Also, since it is big results, not login problems, that are causing your timeouts, you would probably want to use the 'remote query timeout' option, *if* you use sp_configure.
However, you can set the time out on a specific linked server if you use the sp_serveroption system procedure with the query timeout option.
Wednesday, March 14, 2012 6:21 PM
That was a good thing to check. The DBA's tell me the timeout is already set to 10 minutes. I think 5 minutes would be sufficient for my stored procedure. I have asked them whether there are other database parameters would could affect this.
Does anyone know of other parameters?On all linked servers, the default remote query timeout(s) setting is 10 min (600s).
remote query timeout (s) 0 2147483647 600 600
Thursday, March 15, 2012 6:48 AMModerator
By default, the remote query timeout is 600 seconds. In case of other resource issues, such as a lack of memory, the query can be queued and waited for the available resource. You can try to set both values of remote query timeout and query wait to 0 to avoid the timeout issue for long time queries. For more information: Linked Server Properties.TechNet Subscriber Support
In addition, please pay attention to the CommandTimeout property of SqlCommand, the default value is 30 seconds. You may need to increase this value in your scenario.
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.
TechNet Community Support
- Marked As Answer by Stephanie LvModerator Friday, March 16, 2012 1:14 AM
Thursday, March 15, 2012 6:05 PM
As suggested in a reply to my question, I set cmd.CommandTimeout = 600,
where cmd is Dim'd as a SQLCommand.
I would have found this before if VB itellisense had offered that property. The next time I typed in cmd., in another part of the program, this property and others were listed by intellisense which had not been listed before.
Thanks to Stephanie and everyone else who responded.
- Marked As Answer by Stephanie LvModerator Friday, March 16, 2012 1:19 AM