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" sqlcmd :create_initial_db do |sql| sql.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", "RunUpdateDatabase.sql" end
All of the settings are optional, except the 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 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.
This task supports configuration via an external YAML file. For more information, see the yamlconfig page.
This task supports additional command line options, including a
.parameters collection for passing in options that are not directly supported. For more information, see the commandline task options documentation.