Sunday, September 30, 2007

Using Ruby & SQL-DMO to Automate SQL Server Tasks

If you work with Microsoft SQL Server, you might like to know that you can automate many of your administrative tasks by leveraging Distributed Management Objects (SQL-DMO). SQL Server's Enterprise Manager is based on DMO, so most (if not all) of what you do through the Enterprise Manager interface can be automated through SQL-DMO -- and Ruby. Create, manage, and backup databases, tables, views, jobs, stored procedures, and more. Let's take a look at some of the available objects and Ruby code that uses them.

We start by creating an instance of the SQLServer object:


require 'win32ole'
server = WIN32OLE.new('SQLDMO.SQLServer')

To connect to your server using SQL Authentication, call the Connect method providing the server IP address or name, the login, and password:

server.Connect('127.0.0.1', 'login', 'password')

To connect using Windows Authentication, set the LoginSecure property to true, then call the Connect method with just the server IP address or name:

server.LoginSecure = true
server.Connect('127.0.0.1')

When you're done, be sure to close your connection by calling the Disconnect method:

server.Disconnect

The Databases method returns a collection of Database objects. You can access a specific Database object by passing the database name to the Databases method:

database = server.Databases('my database')

The Database object includes dozens of methods and several collections, including Tables, Views, and StoredProcedures. This code, for example, prints the names of all tables:

for table in database.Tables
puts table.Name
end

Furthermore, many database objects expose a Script method that returns the SQL code for creating that object. This little block of code saves the SQL for creating all your Table objects:

for table in database.Tables
File.open("#{table.Name}.sql", "w") do |f|
f.puts table.Script
end
end

The following block of code writes the text of each stored procedure to a text file:

database.StoredProcedures.each do |sp|
File.open("#{sp.Name}.txt", "w") do |f|
f.puts sp.Text
end
end

The SQL Server Agent service, which runs and manages jobs, is accessed via the JobServer object. The following code starts and stops this service:

server.JobServer.Start
server.JobServer.Stop

The JobServer object's Jobs method returns a collection of all SQLServer Agent jobs:

for job in server.JobServer.Jobs
puts "Name: #{job.Name}\nDescription: #{job.Description}\n\n"
end

I have a script that iterates over the Jobs collection and creates a "jobs inventory", an Excel workbook listing each job's name, description, frequency (daily, weekly, etc.) and start time.

Further details on SQL-DMO can be found in this MSDN article, and in this SQL Team article.

That's all for today, but if you use SQL Server, perhaps this has given you some ideas on how to use your Ruby skills to make SQL Server admin tasks a little easier.

UPDATE: You may also be interested in this earlier article about using Ruby and ADO for your SQL Server queries.

Feel free to post a comment here or send me an email with questions, comments, or suggestions.

Thanks for stopping by!

Digg my article

7 comments:

| r a g u | said...

I am working on database migration. This is going to be very useful to me. Thanks a bunch!

thomasl said...

I can connect to my SQL 2005 Express database using SQLDMO but not using ADO. My database uses Windows Authentication only. Using ADO, I would expect that instead of specifying User ID and password, I could instead use "Trusted_Connection=Yes;". The fullconnection string I use in ADO is:
connection_string = "Provider=SQLNCLI;"
connection_string << "Server=.\SQLEXPRESS;"
connection_string << "Database=tw14;"
connection_string << "Trusted_Connection=Yes;"

Can you please explain what I might be missing?

David Mullet said...

@thomasl:

Your syntax looks correct, but I cannot test it at this time.

Is it possible that you do not have the SQL Native Client (SQLNCLI) installed?

Further details here:

Connection strings for SQL Server 2005:

http://tinyurl.com/p98fbs

Feature Pack for Microsoft SQL Server 2005:

http://tinyurl.com/r2pw82

Sam_I_AM said...

I am unable to create a server object. Here is the error I have recieved.

WIN32OLERuntimeError: unknown OLE server: `SQLDMO.SQLServer'
HRESULT error code:0x800401f3
Invalid class string
from (irb):3:in `initialize'
from (irb):3:in `new'
from (irb):3

Secondly I really enjoy your posts. Please keep them coming

David Mullet said...

@Sam_I_Am:

This error can occur if you do not have SQLDMO installed on the PC where your code is running.

Specifically, the SQLDMO object model resides in the file SQLDMO.DLL.

You can install SQLDMO as part of the SQL Server 2005 Feature Pack:

http://tinyurl.com/r2pw82

David

hugo said...

David, thanks so much for this code. I have blogged about, in the hopes that it will raise your ranking on google. This was the best solution for working on MS SQL in ruby. :)

Anonymous said...

I was getting the following error:
Invalid object name 'master..spt_values'.

The solution was to run the following script against the master db...
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Install\u_tables.sql