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!

Ruby, ADO, and SQLServer

Ruby and ActiveX Data Objects (ADO) make working with Microsoft SQL Server databases simple. Here's a simple example of a class that manages the SQL Server database connection and queries:

require 'win32ole'

class SqlServer
    # This class manages database connection and queries
    attr_accessor :connection, :data, :fields

    def initialize
        @connection = nil
        @data = nil
    end

    def open
        # Open ADO connection to the SQL Server database
        connection_string =  "Provider=SQLOLEDB.1;"
        connection_string << "Persist Security Info=False;"
        connection_string << "User ID=USER_ID;"
        connection_string << "password=PASSWORD;"
        connection_string << "Initial Catalog=DATABASE;"
        connection_string << "Data Source=IP_ADDRESS;"
        connection_string << "Network Library=dbmssocn"
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        # Create an instance of an ADO Recordset
        recordset = WIN32OLE.new('ADODB.Recordset')
        # Open the recordset, using an SQL statement and the
        # existing ADO connection
        recordset.Open(sql, @connection)
        # Create and populate an array of field names
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            # Move to the first record/row, if any exist
            recordset.MoveFirst
            # Grab all records
            @data = recordset.GetRows
        rescue
            @data = []
        end
        recordset.Close
        # An ADO Recordset's GetRows method returns an array 
        # of columns, so we'll use the transpose method to 
        # convert it to an array of rows
        @data = @data.transpose
    end

    def close
        @connection.Close
    end
end

You can then use this class as follows:

db = SqlServer.new
db.open
db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")
field_names = db.fields
players = db.data
db.close

The above code is, of course, incomplete and can certainly be improved and extended (error handling, etc.). But, hopefully, it provides you with a solid foundation on which to build.

UPDATE: You might like to know that you can automate many of your SQL Server administrative tasks by leveraging Distributed Management Objects (SQL-DMO). I've explained this in a later article here.

Thanks for stopping by!