Click here to Skip to main content
15,867,911 members
Articles / Database Development / SQL Server
Article

Add/Drop LinkedServers using SMO

Rate me:
Please Sign up or sign in to vote.
2.00/5 (5 votes)
31 Jan 20061 min read 33.2K   23   3
Add/drop Linked Servers using SQL Server 2005 SMO

Introduction

I have used SQLDMO for SQL 2000 before to get basic information. But with SQL Server 2005 we can't use SQLDMO. I needed this for my project and we are moving to SQL Server 2005. I googled for How-To Linked Server using Microsofts SMO (Server Management Objects). The next generation of SQLDMO. I couldn't find any samples for adding, dropping or loading Linked Servers, so I did some research goggling and msdning... and finally got it working and thought someone else will be looking for the same in the future. Hope this will help them.

Here's the code which Adds, Drops and Load Linked Servers for SQL 2000 and SQL 2005.

You need to Add reference to Microsoft.sqlServer.ConnectionInfo, Microsoft.SqlServer.SMO 

public bool AddLinkServer(string SourceServer, string DestinationServer,string UID,string PWD)

{

try

{

LinkedServer DestSrv;

ServerConnection SrvConn = new ServerConnection();


SrvConn.ServerInstance = SourceServer;

if ((UID == "") && (PWD == ""))

{

SrvConn.LoginSecure = true;

}

else

{

SrvConn.LoginSecure = false;

SrvConn.Login = UID;

SrvConn.Password = PWD;

}

Server SQLServer = new Server(SrvConn);

DestSrv = new LinkedServer(SQLServer, DestinationServer);

// Add Remote user/password for linking.

LinkedServerLogin LnkLogin = new LinkedServerLogin(DestSrv, UID);

if (!SrvConn.LoginSecure)

{

LnkLogin.RemoteUser = UID;

LnkLogin.SetRemotePassword(PWD);

LnkLogin.Create();

}

DestSrv.Create();

SrvConn.Disconnect();

return true;

}

catch

{

return false;

}


}

// drop link serverss...

public bool DropLinkServer(string SourceServer,string DropLinkServerName)

{

try

{

ServerConnection SrvConn = new ServerConnection();

SrvConn.ServerInstance = SourceServer;

SrvConn.LoginSecure = true;

Server SQLServer = new Server(SrvConn);

LinkedServerCollection LnkServerList = SQLServer.LinkedServers;

foreach (LinkedServer Lnk in LnkServerList)

{

if (Lnk.Name == DropLinkServerName)

{

Lnk.Drop(true);

break;

}

}

SrvConn.Disconnect();

return true;

}

catch

{

return false;

}

}

// Load all linked servers...

public bool LoadLinkServers(string SourceServer, ArrayList LinkServerList)

{

try

{

ServerConnection SrvConn = new ServerConnection();

SrvConn.ServerInstance = SourceServer;

SrvConn.LoginSecure = true;

Server SQLServer = new Server(SrvConn);

LinkedServerCollection LnkSrvList = SQLServer.LinkedServers;


foreach (LinkedServer Lnk in LnkSrvList)

{

LinkServerList.Add(Lnk.Name);

}

SrvConn.Disconnect();

return true;

}

catch

{

return false;

}

}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
I am a Delphi/C++ developer. Now, putting my foot on to .Net stuff. Starting with C# .Net and SQL Server 2005.

Comments and Discussions

 
QuestionAdd/Drop LinkedServers using SMO Pin
tomytom29-Aug-11 21:37
tomytom29-Aug-11 21:37 
QuestionHow to create an SMO LinkedServerLogin for no specific local login [modified] Pin
BobWaug22-Sep-08 16:47
BobWaug22-Sep-08 16:47 
Hello arunpv, Thank you very much for your post. However, I wanted to know if you could provide an additional code sample of how to successfully create an SMO LinkedServerLogin where you are using a trusted connection and you need to set a remote server SQL Login and password for the link server connection. For example, if you use the sp_addlinkedsrvlogin system stored procedure to do this you would set the “locallogin” parameter to a value of null. This scenario is typical of the need to create a linked server connection between different environments (e.g. dev, itg, prod) while connected to the local server using windows authentication. However, you need to use a remote SQL Login to connect to the remote server to perform a read/only operation because Kerberos delegation is not supported and you don’t want to reference any specific local SQL Login. I tried to create the LinkedServerLogin by instantiating the object with an empty string value and/or a string value of null (e.g. RemoteLnkSrv = New LinkedServerLogin(lsrv, ""); RemoteLnkSrv = New LinkedServerLogin(lsrv, "NULL")), but to no avail. I finally gave up and just decided to execute the sp_addlinkedsrvlogin system stored procedure using the SMO server connection’s ExecuteNonQuery method. For example:

Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class ScriptMain

Public Sub Main()
Dim connProperty As DtsProperty
Dim SrcSrvName As String
Dim dDbName As String
Dim DestSrvName As String
Dim SqlStr As String
Dim sLnkUser As String = Dts.Variables("LnkUser").Value.ToString
Dim sLnkUsrPwd As String = Dts.Variables("LnkUsrPwd").Value.ToString
Try
connProperty = Dts.Connections("Source.Connection").Properties("ServerName")
SrcSrvName = connProperty.GetValue(Dts.Connections("Source.Connection")).ToString
connProperty = Dts.Connections("Destination.Connection").Properties("ServerName")
DestSrvName = connProperty.GetValue(Dts.Connections("Destination.Connection")).ToString
Dim srv As Server
Dim SrvConn As ServerConnection
SrvConn = New ServerConnection()
SrvConn.ServerInstance = SrcSrvName
SrvConn.LoginSecure = True
' LocalLogin = SrvConn.TrueLogin.ToString
srv = New Server(SrvConn)
'*************************
' Create a linked server
'*************************
Dim lsrv As LinkedServer
lsrv = New LinkedServer(srv, "DESRCSRV")
' Set the linked server properties
lsrv.ProviderName = "SQLNCLI"
lsrv.ProductName = "MSSQLSERVER"
lsrv.DataSource = DestSrvName
lsrv.Rpc = True
lsrv.RpcOut = True
lsrv.Create()
' Now create a Linked Server Login for the Linked Server and test the connection
SqlStr = "EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'" & lsrv.Name.ToString & _
"',@useself=N'False',@locallogin=NULL,@rmtuser=N'" & sLnkUser "',@rmtpassword='" & sLnkUsrPwd & "'"
SrvConn.ExecuteNonQuery(SqlStr)
lsrv.TestConnection()
SrvConn.Disconnect()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(0, "Linked Server Error:", ex.Message, "", 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub

End Class

I think Microsoft could do a far better job of providing solid code samples in their SMO reference.

Thanks for your input. Take care.

Robert W.

modified on Monday, September 22, 2008 10:55 PM

QuestionHow does this work with VB.NET 2003 Pin
ThorgalRose6-Apr-07 21:43
ThorgalRose6-Apr-07 21:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.