Problems with sp_addlinkedserver in SQL Server 2000

Question

I'm having a bit of a problem with moving specific data from one server running SQL Server 2000 (A) and another running SQL Server 2008 (B). I'm writing a script according to customer specifications which is to be executed on A, populating tables in B with data. However, I can't seem to get the server link to work.

-- Bunch of declarations here

EXEC sp_addlinkedserver @server = @ServerName,
            @srvproduct = @ServerProduct,
            @provider = @ProviderString,
            @datasrc = @RemoteHost

-- Data migration stuff here

EXEC sp_dropserver @ServerName

Now if I run the script in its entirety I get an error saying:

Msg 7202, Level 11, State 2, Line 55 Could not find server 'remoteServer' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

However, if I highlight only the sp_addlinkedserver part and execute that, there is no error and I can highlight the rest of the script and run it. What am I missing here?

Please help!

PS. If backup-restore was an option, I would have done that already.

Tom H · Accepted Answer · 2010-06-17 14:32:21Z

2

The problem is that SQL Server is trying to validate the code before it runs it. To show that, try putting one of your commands in EXEC(). Try putting a GO after the sp_addlinkedserver command.

answered Jun 17, 2010 at 14:32

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problems with sp_addlinkedserver in SQL Server 2000

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related