Working with sp_addlinkedserver and sp_dropserver

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Working with sp_addlinkedserver and sp_dropserver

Author

Topic

bmahf
Starting Member

14 Posts

Posted - 2012-04-25 : 16:06:31

Hi all,

I'm trying to connect programmatically to a remote server. I want to write a script that will add a linked server name in order to do this, and then after doing the work, will remove the linked server name from the database.

I wrote the following:


IF (SELECT COUNT(*) FROM master.dbo.sysservers WHERE srvname = 'S1_instance1') = 0
BEGIN
	EXEC sp_addlinkedserver @server=N'S1_instance1', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'<remote_server_name>\<remote_instance>';
END

SELECT * FROM S1_instance1.<databasename>.dbo.mytable

EXEC sp_dropserver 'S1_instance1'

Of course anything inside of '<' and '>' are to be changed to the correct strings.

When I run this the first time, it works great. Then I go to check another table, say mytable2. I change mytable to mytable2 and hit F5 and get the following:

quote:
Could not find server 'S1_instance1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

But if I select the full if block and run that, it runs well, happily printing out 'Command(s) completed successfully.' Then if I hit F5 I'm golden. But then if I change the tablename again, say to mytable3, I get the same error message, and then selecting the if block and running that seems to clear it so the next run of F5 gives me the content of mytable3.

And yes, we can assume that mytable, mytable2, and mytable3 all exist, since I do get data from them, but not after I have changed the table name.

Oh, and I continue to get the error after changing the table name and while hitting F5 over and over. Not matter how many times. I only get out of this state when I select the if block, hit F5, and then unselect so that I get everything and then hit F5 again. While I'm getting the issue, if I go to my instance in the Object Explorer and look at Server Objects > Linked Servers, I don't see the server name. Once I select just the if block and hit F5, I do see the server name.

I'm running on SQL Server 2008 R2 on Windows 7, and I'm trying to connect to another physical box running the same configuration.

I'm not sure how a script running on one box would work with this continually happening. I have no idea right now why the if statement would not always just work correctly.

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-25 : 16:14:28

try this and see if you're still getting intermittent error



IF (SELECT COUNT(*) FROM master.dbo.sysservers WHERE srvname = 'S1_instance1') = 0
BEGIN
	EXEC sp_addlinkedserver @server=N'S1_instance1', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'<remote_server_name>\<remote_instance>';
END

SELECT * FROM S1_instance1.<databasename>.dbo.mytable
GO
EXEC sp_dropserver 'S1_instance1'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bmahf
Starting Member

14 Posts

Posted - 2012-04-25 : 17:43:11

Thanks, but that only made it so I got two errors instead of one. The way it was, the crash at the select statement ended the run, but now it makes it two different things, so the exec sp_dropserver now gets run even though the select bombed. My new error messages after putting go right after the select statement are:
---
Msg 7202, Level 11, State 2, Line 10
Could not find server 'S1_instance1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Msg 15015, Level 16, State 1, Procedure sp_dropserver, Line 42
The server 'S1_instance1' does not exist. Use sp_helpserver to show available servers.
---

bmahf
Starting Member

14 Posts

Posted - 2012-04-25 : 17:49:00

Oh, and I should mention that selecting the if block and F5 causes the next F5 after unselecting to work properly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources