Testing a connection to a linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Testing a connection to a linked server

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-12 : 11:51:38

I succesfully setup a linked server between sql2005 and sql2000 in .net using:
EXEC sp_addlinkedserver
EXEC sp_addlinkedsrvlogin

If I set the correct login name/password and ip address then the linked server is created and I can access remote database as expected.

If I set incorrect login name/password and IP address although the linked server is created I cannot access the remote database as expected. However, when I try to access the database it times out after around 20 seconds with a port error.

Q. Is it possible to test the connection after I've made the linked server?
Q. If not can I reduce the timeout to say 5 seconds?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-12 : 11:58:02

1)Yes with sp_testlinkedserver
2)See connection timeout setting in Linked server properties.

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-12 : 12:11:24

quote:
Originally posted by sodeep

1)Yes with sp_testlinkedserver
2)See connection timeout setting in Linked server properties.

Thanks for the quick reply.
1. I used

exec sp_testlinkedserver @server = "ServerAliasName"

but it takes the same time with same error as a normal sql query so testing it still takes like 10 seconds

2. I looked at all the properties of the following none of which has a connection timeout property:

sp_testlinkedserver,

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-12 : 12:13:09

http://sqlblogcasts.com/blogs/markallison/archive/2008/01/29/linked-server-timeouts.aspx

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-12 : 12:31:21

quote:
Originally posted by sodeep

http://sqlblogcasts.com/blogs/markallison/archive/2008/01/29/linked-server-timeouts.aspx

I gather so far then that I can't programmatically change the connection timeout property. However I can't find the Linked Server Properties dialogue window in sqlSMS2005.

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-13 : 03:08:11

Can anyone else help me with this problem please?

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-13 : 11:14:42

quote:
Originally posted by insanepaul

Can anyone else help me with this problem please?

I need to do this programmatically as some queries take longer than others. If the experts don't know how to do this then I'm sure I don't. What do I do?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-13 : 11:20:31

If you are checking link before executing query you could do a simple query before proceeding:

e.g.
DECLARE @iSourceRows as integer;
DECLARE @iDestRows as integer;

BEGIN TRAN
--get count of rows in Source
select @iSourceRows= a.iRows
FROM (select count(*) as iRows FROM [lnkedServer].[DB].[dbo].[Table]) AS a

--check rows returned before ......
If @iSourceRows < 1
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
..............

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-13 : 12:37:04

quote:
Originally posted by darkdusky

If you are checking link before executing query you could do a simple query before proceeding:

e.g.
DECLARE @iSourceRows as integer;
DECLARE @iDestRows as integer;

BEGIN TRAN
--get count of rows in Source
select @iSourceRows= a.iRows
FROM (select count(*) as iRows FROM [lnkedServer].[DB].[dbo].[Table]) AS a

--check rows returned before ......
If @iSourceRows < 1
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
..............

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-13 : 12:38:31

quote:
Originally posted by darkdusky

If you are checking link before executing query you could do a simple query before proceeding:

e.g.
DECLARE @iSourceRows as integer;
DECLARE @iDestRows as integer;

BEGIN TRAN
--get count of rows in Source
select @iSourceRows= a.iRows
FROM (select count(*) as iRows FROM [lnkedServer].[DB].[dbo].[Table]) AS a

--check rows returned before ......
If @iSourceRows < 1
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
..............

Can you show me this in an SQL statement...I tried count (*) but still it takes 20 seconds if the linked server was incorrectly setup

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-14 : 04:42:15

If you just execute "select count(*) as iRows FROM [lnkedServer].[DB].[dbo].[Table] with (nolock)"
How long does this query take?

Do you have remote access to the linked server through remote desktop or MSTSC etc?
If you run the select (*) directly on the server itself how long does the query take? I'm trying to rule out if the delay is actually on the database processing query rather than on the linkage.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 05:10:20

Why bother with a "simple" query? If the linked server is not there to answer, the timeout is still set to 20 seconds or so.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 05:11:50

In SSMS2005 you go to "Server Objects -> Linked Servers" and choose properties for the linked server in question.
Change page to "Server Options" and set new conncetion timeout.

Done.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 05:14:23

And if you want to do it programmatically, see Books Online for the use of

sp_serveroption

E 12°55'05.63"
N 56°04'39.26"

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-14 : 07:02:17

quote:
Originally posted by Peso

And if you want to do it programmatically, see Books Online for the use of

sp_serveroption

E 12°55'05.63"
N 56°04'39.26"

Where is Server Objects in SMS2005?
I tried:
sp_serveroption
@server = "ServerAliasName",
@optname = "query timeout",
@optvalue = 3

sp_serveroption
@server = "ServerAliasName",
@optname = "connect timeout",
@optvalue = 3
or:
EXEC sp_serveroption 'ServerAliasName', 'query timeout', 3;
EXEC sp_serveroption 'ServerAliasName', 'connect timeout', 3;

but it still takes 20 secs to get the error when testing a sql command when i was expecting it to take just 3 seconds.

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-16 : 03:17:32

quote:
Originally posted by Peso

And if you want to do it programmatically, see Books Online for the use of

sp_serveroption

E 12°55'05.63"
N 56°04'39.26"

Can you help with the above reply?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources