SQLServerCentral Article

Adding Linked Servers Using SQL-DMO

,

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating