January 15, 2008 at 1:48 pm
Create a linked server to another box.
exec sp_serveroption 'RemoteServer', 'connect timeout', '1'
exec sp_serveroption 'RemoteServer', 'query timeout', '1'
Pull the network cable from the remote box.
SELECT * from RemoteServer.Database.dbo.Table1
Why does it take almost exactly 21 seconds, every time, before it fails?
How can I get it to fail immediately?
January 17, 2008 at 11:52 am
January 17, 2008 at 12:07 pm
to be honest I don't think you can, it's probably something to do with oledb. It's something I'm aware of but not thought much about. You could try setting a timeout - or try raiserror with nowait ( you'll have to check as I can't find my notes just now )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 21, 2008 at 7:06 pm
It is probably because of the network. Maybe you should talk to your network administrator to see why it takes so long.
Anyway I use OPENQUERY to get the dataset of the linked server database table and put the recordset in a temp table. Then I select data from the temp table.
In one stored procedure, the SELECT statement used 4 linked server tables, the stored procedure ran 3 hours. I changed to use OPENQUERY and it ran half an hour. My boss's jaw almost dropped to the floor!!!
January 22, 2008 at 7:39 am
The long timeout is more likely to due with the NetServerEnum API, on which OLEDB depends to scan the network for SQL Server instances. One of the issues that need to be addressed when connecting to computers in your network is dealing with computers that don't respond. When connecting to a remote computer you don't have control over the timeout. I suspect your timeout is closer to 30 seconds than 21 seconds, which may indicate the default timeout for the NetServerEnum API.
Thanks,
Phillip Cox
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply