Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Adding Linked Servers Using SQL-DMO

By Andy Warren,

A few weeks ago we had a question about how to work with linked servers using DMO (posted by Brian Lockwood of LockwoodTech Software) that turned out to be more work than I thought it would be, though for reasons having more to do with linked servers in general than anything to do with DMO!

Before we dig in to the code there's a tip that might save you some time if you use DMO frequently - create a template in VB. Start by creating a new exe type project, then add a reference to SQL-DMO. Then add the following code to form_load (for simplicity, not necessarily a best practice):

Dim oserver As SQLDMO.SQLServer

'create standard server object first
Set oserver = New SQLDMO.SQLServer
With oserver
.LoginSecure = True
.Connect "eg\one"
End With

oserver.DisConnect
Set oserver = Nothing

Then click File, Save As, navigate to the MS Visual Studio\VB98\Template\Projects folder. Save the form as frmDMO.frm, save the project as SQLDMO. Now when you open VB to create a new project you can select SQLDMO and save some time typing that stuff in to a new project each time. Not earthshaking, but every little bit helps!

If you're new to linked servers they are a way to access other OLEDB data sources from within SQL - whether it be another SQL Server, an Access mdb, anything that has has an OLEDB driver. We've got a few articles here on the site worth looking at:

Uncovering Linked Servers
Viewing Linked Servers' Meta Data
Linked Servers: Creation, setting options and querying

You'll probably want to spend some time building and testing linked servers using Enterprise Manager and Query Analyzer as well before you try building one though code. Now let's start looking at some code! We'll start by dimming all the variables and getting a server connection:

Dim oserver As SQLDMO.SQLServer
Dim oLinkedServer As SQLDMO.LinkedServer
Dim oResults As SQLDMO.QueryResults
Dim oLogin As SQLDMO.LinkedServerLogin
Dim J As Integer

'create standard server object first
Set oserver = New SQLDMO.SQLServer
With oserver
.LoginSecure = True
.Connect "your server"
End With

Next we start getting to the good stuff. Remember in DMO if you want to add anything you first create the object, assign values to it's properties, then add the object to a collection. That is sooooo important. Create the object, set the properties, add to the collection!

If you'll glance through the next block of code you'll see that I'm specifying SQLOLEDB as the provider name. Originally I set the product name to 'SQL Server' and the ProviderName to 'Microsoft OLEDB Provider for SQL Server'. The problem with that technique is that it forces the name of the linked server to be the datasource as well. It's a handy shortcut for setting up a link to another SQL Server as long as you don't already have a linked server with the same name or you're not trying to reference it by the IP address. If you need to have the name of the link different than the datasource, the code below works. Actually it works even if you want the name and datasource to be the same!

'create an empty linked server object and set
'some properties
Set oLinkedServer = New SQLDMO.LinkedServer
With oLinkedServer
.Catalog = "Pubs"
.Name = "LINKTEST1"
.ProviderName = "SQLOLEDB"
.DataSource = "name of your linked server"
End With

'add it to the collection
oserver.LinkedServers.Add oLinkedServer

For some reason you can't set the options when you're setting other properties - the options property is a read only bitmap that you can decode to set which options have been set. Instead you use the setoptions method, like this:
 

'set options now
oLinkedServer.SetOptions SQLDMOSrvOpt_DataAccess + SQLDMOSrvOpt_RPC + SQLDMOSrvOpt_RPC_out, True

These are the options you'll generally use, you may need more/different depending on what you're doing, replication being one example.

Next we take a small detour. When you add a linked server it automatically adds a null login. Books Online has this to say about it: "When a linked server is created, SQL Server creates a default linked server login specifying a NULL local login name and authentication impersonation. This special purpose login mapping record provides authentication data mapping for those logins not mapped explicitly." As far as I can tell this is the same as checking the "Be Made Without Using a Security Context" option in the linked server dialog. Useful for things that don't require permissions, not so useful for connecting to another server. Anyway, we don't need it, so let's get rid of it!

'remove the default login
oLinkedServer.LinkedServerLogins(1).Remove

Now repeat the mantra - create the object, set the properties, add to the collection - this time for a Login object. In this example I'm using a sql login which means you have to know a login/password that is already valid on the server you're trying to link to OR you have to have permissions to create a new login/password. If you've opted for the latter you might want to generate the password at run time. If you put the password in your code here you should at least (very least!) obfuscate it so that no one can find it by opening your exe in Notepad and browsing for clear text items.

'then create a new one and add one back
Set oLogin = New SQLDMO.LinkedServerLogin
With oLogin
.RemoteUser = "sa"
.RemotePassword = "supersecretpassword"
.Impersonate = False
End With
oLinkedServer.LinkedServerLogins.Add oLogin
Set oLogin = Nothing

I've added this in case you're just putting the linked server in temporarily and want to remove it afterward. Note, if you're just running one or two queries it might be less work to use OpenRowset in your code rather than creating the linked server at all. Here the trick is you have to remove all the logins for the link before you can drop it. Rather than iterating through the logins collection (though in this example we know there is only one) you can use the drop logins method to get them all in one pass.

'drop the link
oLinkedServer.DropLogins = True
oLinkedServer.Remove

The last part is pretty standard, clean up all our object references:

'done
Set oResults = Nothing
Set oLinkedServer = Nothing
oserver.DisConnect
Set oserver = Nothing

I've built this without error handling, you'll definitely want to add some if you're using for production. The initial server connection might fail, the user might not have rights to add a linked server, the user might not have rights to access the remote server.

Got a comment or question about the article? Post in the attached discussion forum, usually I'll reply the same day. Reader comments add a terrific dimension to any article, often better than the article itself.

Total article views: 12118 | Views in the last 30 days: 5
 
Related Articles
FORUM

To create login in many servers.

To create login in many servers.

FORUM

Creating Logins

Creating Logins with limited permission

FORUM

SSRS Logins & Object Access Report

Please Help!! Creating a report to detail SSRS logins & Objects

FORUM

Create new Login

Create new Login for SQL Server 2005 using SMO

FORUM

how to create user group logins in SQL server 2005

how to create user group logins in SQL server 2005

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones