August 27, 2002 at 6:05 am
I can't find any good DMO example code to create a linked server. I have login information (server name, uid, pwd) for two servers. i have logged into one and now want to use login credentials to create and log into the other so i can run cross server queries from the 1st login.
so my goal for the linked server is to simply run sql statements via ADO that affect both servers. But I can not assume the linked server exists. In fact, I will create it, use it and then delete it after the operation has been completed in the case that the user does not want a linked server.
Please let me know if you have a good code example to create, login to, and attach a linked server.
TIA
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
August 27, 2002 at 6:27 pm
See if this works. Look for a follow up article in the next week or so.
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 "eg\one"
End With
'create an empty linked server object and set
'some properties
Set oLinkedServer = New SQLDMO.LinkedServer
With oLinkedServer
.Catalog = "Pubs"
.Name = "LINKTEST1"
'.ProductName = "SQL Server"
'.ProviderName = "Microsoft OLEDB Provider for SQL Server"
.ProviderName = "SQLOLEDB"
.DataSource = "two"
End With
'add it to the collection
oserver.LinkedServers.Add oLinkedServer
'set options now
oLinkedServer.SetOptions SQLDMOSrvOpt_DataAccess + SQLDMOSrvOpt_RPC + SQLDMOSrvOpt_RPC_out, True
'remove the default login
oLinkedServer.LinkedServerLogins(1).Remove
'then create a new one and add one back
Set oLogin = New SQLDMO.LinkedServerLogin
With oLogin
.RemoteUser = "sa"
.RemotePassword = "sa"
.Impersonate = False
End With
oLinkedServer.LinkedServerLogins.Add oLogin
Set oLogin = Nothing
'see if it works - this should show column names from pubs..titles
Set oResults = oLinkedServer.ExecuteWithResults("Select * from titles")
For J = 1 To oResults.Columns
Debug.Print oResults.ColumnName(J)
Next
'drop the link
oLinkedServer.DropLogins = True
oLinkedServer.Remove
'done
Set oResults = Nothing
Set oLinkedServer = Nothing
oserver.DisConnect
Set oserver = Nothing
Andy
August 28, 2002 at 5:55 pm
great - many thanks. let me try this and let you know how it pans out
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply