-
Notifications
You must be signed in to change notification settings - Fork 71
SQLCmdTask
The SQLCmdTask allows you to run .sql scripts through SQL Server’s “sqlcmd.exe” tool. Right now it only supports a few basic options that my projects are using. If you need additional feature support, feel free to submit a patch or add it to the issues list.
Here is an example of how to use the SQLCmdTask
desc "Create the initial R1 database"
Rake::SQLCmdTask.new(:create_initial_db) do |sql|
sql.path_to_command = "sqlcmd.exe"
sql.server = "some_server"
sql.database = "some_database"
sql.username = "some_user"
sql.password = "SHH!!! it's a secret!"
sql.variables = {:New_DB_Name => "Albacore_Test"}
sql.scripts << "RunCreateDatabase.sql"
sql.scripts << "RunUpdateDatabase.sql"
end
All of the settings are optional, except the path_to_command.
Specify the location and name of the sqlcmd executable.
Specify the name of the server to connect to
Specify which database to use
Specify the user to log in and run the scripts as
Specify the password for the user to log in as
Specify a set of variables to pass to sqlcmd, via a hash table. These are passed to sqlcmd using the “-v name=value” format. See the “http://msdn.microsoft.com/en-us/library/ms162773.aspx”SQLCmd documentation for more information.
Specify a set of script files for sqlcmd to execute, via an array. These are passed to sqlcmd using the “-i filename.sql” format. See the “http://msdn.microsoft.com/en-us/library/ms162773.aspx”SQLCmd documentation for more information.
The SQLCmdTask supports configuration via YAML files. To do this, call the .configure method and provide the location of the yml file.
desc "Create the initial R1 database"
Rake::SQLCmdTask.new(:create_initial_db) do |sql|
sql.configure("sqlcmd.yml")
sql.variables = {:New_DB_Name => "Albacore_Test"}
sql.command_directory = "SQLScripts\\Database\\Db_R1_scripts\\CreateDB"
sql.scripts << "RunCreateDatabase.sql"
end
The contents of the yml file should be name: value pairs that represent the sqlcmd settings and values for those settings. For example, in the above example, the server, database, username and password can all be specified through this yml file:
sqlcmd.yml
path_to_command: sqlcmd.exe
server: 192.168.35.129
database: master
username: sa
password: PA$$word14
The SQLCmdTask uses the built in logging options to provide some potentially useful information at run-time. By default, no additional information is logged, other than what sqlcmd.exe produces.
When the log_level is set to :verbose, the full command line call for sqlcmd will be logged. This includes the path to the sqlcmd exe as well as the command line parameters that are passed to it.