Set timeout for a stored procedure across a link to another server

Question

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?

---

MLarsB

Answer 1

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 

---

Lalith.

Answer 2

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.

---

MLarsB

Answer 3

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.

Answer 4

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

---

MLarsB

Answer 5

Hi MLarsB,

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.

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.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 6

Mystery solved:

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.

---

MLarsB