sp_addserver for remote servers is discontinued

16/12/2012

אין תגובות

Microsoft SQL Server, had a provision to add remote servers until SQL Server 2008 R2. From Microsoft SQL Server 2012, this functionality is now deprecated, and we now need to add "linked” servers instead. If a user does attempt to use the sp_addserver syntax to add a remote server, system will result in the following error:

Msg 15663, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 60
Feature "sp_addserver" is no longer supported. Replace remote servers by using linked servers.

The solution is to use the sp_addlinkedserver system stored procedure, using which the script would require changes as under:

01.--Check the instances registered with this instance of Microsoft SQL Server

02.--An id=0 indicates that this is a local instance

03.sp_helpserver

04.GO

05.

06.--Syntax: sp_addlinkedserver (http://msdn.microsoft.com/en-us/library/ms190479.aspx)

07.--Add a remote server

08.sp_addlinkedserver 'WIN2K8\SQL2K8','SQL Server'

09.GO

10.

11.--Check the instances registered with this instance of Microsoft SQL Server

12.--An id=0 indicates that this is a local instance

13.sp_helpserver

14.GO

Using sp_addlinkedserver instead of sp_addserver to register a remote SQL Server instance in SQL Server 2012

Please note that after one uses sp_addserver to rename a local instance of Microsoft SQL Server, a service restart is required for the changes to take effect.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *