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

[MLarsB]

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?

[ck9663]

Try to see first why it's returning longer than usual. It's probably because of the double-hop you're doing on the second server.

Good Luck!!!

~~ CK

[MLarsB]

Thanks.

The same stored procedure which runs in 2 minutes in SMS will not complete in 30 seconds through VB, with the same parameter values. It would seem the server hops would be the same for the two cases.

If I could set the timeout to 3 minutes the call in VB would most likely work.

[ck9663]

Try to isolate your problem. Remove all portions that links the two server and see how fast it will return. If it still returns more than a minute then it's the problem is somewhere else.

Good Luck!!!


~~ CK

[MLarsB]

Thanks, but the first thing is does is select across the linked server. The rest is selects on the records it had brought over.

I have asked the DBA's to do as suggested on another site:
You can set the time out on a specific linked server if you use the sp_serveroption system procedure with the query timeout option.

I have not heard back from them yet.

[MLarsB]

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 everyone who responded.