Using SP with references to remote server

Question

Post reply

Using SP with references to remote server

Richard Rice

SSC Eights!

Points: 879
More actions
May 28, 2010 at 10:39 am

#147243

I have a SP that will conditionally update local tables with data from remote master server. This SP is called from an application which has the SQL2005 Desktop Engine.
A typical line in the SP is of the type:
insert dbo.branchinfo
select *
from speedy.poller.dbo.branchinfo
The script will synchronize several tables from the master server to the local database.
When the SP is called on the local machine, I get an error stating that the connection to the remote server failed.
Typically I would add the line : sp_addlinkedserver 'servername' to a TSQL in order to get the server hooked up, but this line will generate an validation error if added to the SP.
Is there a way to ensure that the remote server is always linked or available from the local desktop SQL instance?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Richard maybe something like this:

the proc sp_tables_ex in a try...catch, if it fails to find the linked server?

i tested it with both a valid and invalid linked server name, and it seems to work...you could use this idea as a pre-test...if it fails, you cannot insert, but if it returns 1, then you can continue?

BEGIN TRY

Begin Transaction

exec sp_tables_ex fakeserver

SELECT 1 As results

Commit transaction

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

SELECT 0 As results

END CATCH

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Richard Rice SSC Eights! Points: 879 More actions · Answer 2

Lowell, thank you for the quick reply, but the issue is in trying to get the remote server linked either through the SP or permanently. I have 4 remote locations that will be using this SP, all contacting and synchronizing with a single master SQL 2005 server.

Optimally, I would like each remote server to be able to contact the master server at all times. This may be a problem if the WAN drops though, which is why we are synchronizing the tables to begin with. So, to get around this, having the SP create the link would be the best method.

Lowell SSC Guru Points: 323518 More actions · Answer 3

ahh ok, then i think you can use dynamic sql to add the linked server.

you might want to drop and recreate each time, or use a if not exists(select * from sys.servers where servername=mylinkedserver),

but i think that would work.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Richard Rice SSC Eights! Points: 879 More actions · Answer 4

Ok, I got it. I needed to do 2 calls to the server, but also had to set the first call of the form: master.sys.sp_addlinkedServer 'speedy' since the odbc connection is setting the database to the working database.

This works, so thanks for the push in the right direction.