Running a Distributed Query with Visual Basic Using ADO - Probing the Linked Server
(Page 4 of 4 )
Collected in the click event of the Command3 button are four of the stored procedures that can be run on the SQL Server 2000. They show some of the details about the Linked Server, its table, columns, Primary Keys, and so forth. This is not a complete list, but please refer to the SQL 2000 Server Book-on-line for other related stored procedures.
Private Sub Command3_Click()
'Choose the option and send the strsql to the sqlst Procedure
If Option1.Value = True Then strsql = "Exec sp_helplinkedsrvlogin"
Call sqlst (strsql) ElseIf Option2.Value = True Then strsql =
"Exec sp_tables_ex 'GoodLink'" Call sqlst (strsql) ElseIf Option3.Value =
True Then strsql = "Exec sp_Columns_ex GoodLink, 'Employees' " Call sqlst
(strsql) ElseIf Option4.Value = True Then strsql = "Exec sp_primarykeys
GoodLink, 'Order Details' " Call sqlst (strsql) End If
End Sub
Sub sqlst (ByVal strg As String)
'sqlst passed the string to the recordset open() method as
'an argument. Otherwise it is similar to the Command2 code
'described earlier.
Dim connstr As String
Dim strsql As String
connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Persist Security Info=False; Initial Catalog=Northwind;" _
& "Data Source=XPHTEK"
If adors.State = 1 Then adors.Close
End If adors.CursorLocation = adUseClient
adocon.Open connstr
adors.Open strg, connstr, adOpenStatic, adLockReadOnly
Set DataGrid1.DataSource = adors
adocon.Close
End Sub
In order to find the information choose one of the radio buttons. For the screen shot shown here, the "Examine the Linked Server" button was clicked after choosing "Find all tables in the linked server." The grid is filled with the retrieved information as shown in the screen shot.
Summary
ADO objects provide a convenient way to add and manage a linked server on a SQL Server, and allow you to run distributed queries. Only some of the stored procedures were examined, but you may find several more in the Book On Line. While the Openquery() method returned the columns in their natural order, the four-part syntax returned the columns in alphabetical order. In the tutorial presented, Windows authentication for the SQL Server and remote authentication for an unsecured database were used. If these are different in your situation, changes must be made for the code.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
