Connect to a server and its databases using SQL-DMO
Microsoft provides an object-oriented library for dealing with SQL objects: SQL-DMO (SQL Distributed Management Objects). By using this library, you can perform a number of cool tasks that are difficult or impossible to do in T-SQL.
As with all object libraries, you have to learn to walk the hierarchy. The highest-level object in the library is the collection of servers. Below that level are the databases, and the tables, views, sprocs, and so on that reside within a given database. This tip will show you the most basic steps on how to connect to a server and its databases using SQL-DMO.
Related resources
* Best practices to enhance your SQL Server applications
* Download: Quick Start: Microsoft SQL Server
The first thing you need to do is obtain the list of servers. The following code will deliver the list of servers to a listbox:
Function listServers(oControl As Object)
Dim oApp As SQLDMO.Application
Dim oNames As SQLDMO.NameList
Set oApp = New SQLDMO.Application
Set oNames = oApp.ListAvailableSQLServers()
For Each oName In oNames
oControl.AddItem oName
Next
End Function
Podobny skrypt w j. Python:
import win32com.client
oApps = win32com.client.Dispatch('SQLDMO.Application')
oNames = oApps.ListAvailableSQLServers()
print "Lista serwerow MS SQL"
for oName in oNames:
print oName
print "Koniec"
The next (lower) element in the hierarchy is the list of databases. This is where you may need to supply a userid and a password (if you aren't using integrated security). Note: Be sure to read the following code before using it, particularly the demarcated lines.
Function listDatabases(vServer As String, oControl As Object)
Dim oSrvr As SQLDMO.SQLServer
Set oSrvr = New SQLDMO.SQLServer
oSrvr.LoginSecure = True
' ----------------------------
' use this line for integrated security
oSrvr.Connect vServer
' or this for sql login
oSrvr.Connect vServer, "sa", "SQLTips" - substitute your sa password
' --------------------------
For Each oDatabase In oSrvr.Databases
oControl.AddItem oDatabase.Name
Next
End Function
I once used such code when I was deploying an application written against MSDE, which doesn't include Enterprise Manager, Query Analyzer, and all those other nice tools. With SQL-DMO, I was able to write a small application that enables users to create a new database, install the tables we shipped (mostly blank but some of them were pre-populated), and then load and go.
I encourage you to investigate SQL-DMO further. By descending into the hierarchy, you can walk the list of tables, queries, and so on, using very simple code like the examples above. Once you dive in, it turns out to be very easy, and it can save you lots of time.
Arthur Fuller has been developing database applications for more than 20 years. He frequently works with Access ADPs, Microsoft SQL 2000, MySQL, and .NET.
Brak komentarzy:
Prześlij komentarz