Running a Distributed Query with Visual Basic Using ADO - Adding a linked server to the SQL 2000 server
(Page 2 of 4 )
The code shown in the next paragraph allows you to make the connection to the SQL 2000 Server and run two stored procedures in the SQL Server. In the present tutorial, the author being the owner (admin) of the computer and the dbo of the database, things will work without a problem. The author is also authenticated by the Windows authentication for the SQL 2000 Server. If your situation is different, you may need to adjust the code.
Insert the code shown in the click event of the Command1 button. Also add the two declarations at the top of the form's code in the (Declarations) area of the code page. Code completion is aided by intellisense; just insert the object name and a period and you should see a pick list drop giving you all the relevant methods and properties.
Once you have the connection string you can open a connection. With the connection open you can execute command texts against the database. In this case they are the two stored procedures, sp_addlinkedserver which adds a linked server called "GoodLink" whose data will be in the external source, which is a Microsoft Access database. The Microsoft Access database directory location is known. The "GoodLink" server will be added to the Link Servers on the "XPHTEK" SQL Server 2000. Further you will be accessing this server (=this MS Access database) with the username: admin and an empty string for password. The Product Name, which is the second argument after the linked server name, appears to be mandatory, but can be anything ("trash" has been used to show that it does not matter).
Dim adocon as New ADODB.CONNECTION
Dim adors as New ADODB.Recordset
Private Sub Command1_Click()
Dim connstr As String
connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Security Info=False; Initial Catalog=Northwind;" _
& "Data Source=XPHTEK"
adocon.Open connstr
adocon.Execute "Exec sp_addlinkedserver" _
& "'GoodLink', 'trash', 'Microsoft.Jet.OLEDB.4.0'," _
& "'C:Documents and Settingscomputer userMy DocumentsNorthwind.mdb'"
adocon.Execute "Exec sp_addlinkedsrvlogin" _
& "'GoodLink', false, NULL, 'Admin',''"
MsgBox ("GoodLink Server added")
adocon.Close
End Sub
After running this code you may verify in the SQL Server 2000 Enterprise Manager that the "GoodLink" server is added to the Linked Servers folder as shown.
The security tab has taken the values in your code as shown in the next picture. The product name is a required item, but can be anything, it appears.
Next: Running a query against the Linked Server >>
More Visual Basic.NET Articles
More By Jayaram Krishnaswamy
