Problems with sp_addlinkedserver in SQL Server 2000

Question

Hi!

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.

Answer 1

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.