mssql 2005 sqlcmd with rails migrations

“Connection is busy with results for another command” bugger

The constant struggle with db scripts in Rails migrations is side-stepping the “Connection is busy with results for another command” error when attempting to run a typical script. Here’s how I’m dealing with it using mssql 2005 and sqlcmd.

First, create config/mssql_migrate.yml as shown:


  server: myserver\sqlexpress
  database: customers

Now, simply use Ruby's Kernel.system method to call sqlcmd (installed with mssql express edition) to run a sql script:

  
  require 'yaml'

  class CleanupUglyTriggers < ActiveRecord::Migration
    def self.up
      
      say 'Running scripts...'
      server = YAML.load(ERB.new(IO.read("#{RAILS_ROOT}/config/mssql_migrate.yml")).result)['server']
      db = YAML.load(ERB.new(IO.read("#{RAILS_ROOT}/config/mssql_migrate.yml")).result)['database']

      system %{sqlcmd -S #{server} -d #{db} -E -i ".\\db\\migrate\\scripts\\update_trigger.sql"}
      system %{sqlcmd -S #{server} -d #{db} -E -i ".\\db\\migrate\\scripts\\insert_trigger.sql"}
      system %{sqlcmd -S #{server} -d #{db} -E -i ".\\db\\migrate\\scripts\\delete_trigger.sql"}

    end
  end

Posted by Luke on Thursday, January 03, 2008

Comments (1)


Acie on Thursday, January 03, 2008

Thanks!