SQLCMD

Run SQL scripts & commands using SQL Server's sqlcmd.exe application. This task only supports a subset of commonly used commands, please contribute more!

desc "Create the initial database"
sqlcmd :create do |cmd|
  cmd.server = "myserver"
  cmd.database = "somedatabase"
  cmd.username = "username"
  cmd.password = "password"
  cmd.variables = {:db_name => "Albacore"}
  cmd.scripts = ["create.sql", "update.sql"]
end

Required Parameters

Optional Parameters

Server & Database

The name of the server and database to connect to and use. By default, sqlcmd uses the default instance on localhost.

server = "myserver"
database = "somedatabase"

If you have to pass an instance name, it requires a backslash. So, it must be escaped, by using a double-backslash.

server = "myserver\\myinstance"

Username & Password OR Trusted Connection

The username and password to connect as.

username = "Someone"
password = "FooBar"

Or, you can login with Windows integrated security. Without any authentication parameters, sqlcmd uses a trusted connection implicitly, so this is often not required at all.

trusted_connection

Variables

A set of "command substitution" variables to pass to sqlcmd.

variables = {:foo => "bar", :boo => "baz"}

Scripts

The script files for sqlcmd to execute.

scripts = ["script1.sql", "script2.sql"]

Ignore Variables

Sometimes you want to disable variable substitution, especially useful when executing commands with built-in variables (may SQL Server Agent queries require this switch).

ignore_variables

Batch Abort & Severity

Often used together, this causes sqlcmd to exit with an error level. It must meet or exceed the severity threshold if one is provided.

batch_abort
severity = 1