Click here to Skip to main content
Click here to Skip to main content

Add/Drop LinkedServers using SMO

, 31 Jan 2006
Rate this:
Please Sign up or sign in to vote.
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

About the Author

arunpv
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 Pinmembertomytom29-Aug-11 21:37 
QuestionHow to create an SMO LinkedServerLogin for no specific local login [modified] PinmemberBobWaug22-Sep-08 16:47 
QuestionHow does this work with VB.NET 2003 PinmemberThorgalRose6-Apr-07 21:43 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 31 Jan 2006
Article Copyright 2006 by arunpv
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid