Add/Drop LinkedServers using 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;

}

}

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