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:
Linked Servers
Linked Servers' Meta Data
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.
