Sunday, September 30, 2007

Using Ruby & SQL-DMO to Automate SQL Server Tasks


We start by creating an instance of the SQLServer object:

require 'win32ole'
server ='SQLDMO.SQLServer')




server.LoginSecure = true




database = server.Databases('my database')


for table in database.Tables
puts table.Name


f.puts table.Script


f.puts sp.Text




for job in server.JobServer.Jobs






Thanks for stopping by!

Digg my article


Unknown 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...


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:

Feature Pack for Microsoft SQL Server 2005:

Unknown 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...


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:


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