Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
How to pass parameters to SQL script running from VS 2013 Release Management? Let's see step by step.
Below is a very simple SQL Script written in SQLCMD mode, to test the tool.
INSERT INTO [dbo].[Customer]
([Id]
,[Name])
VALUES
($(Id)
,'$(Name)')
GO
This script is inserting a record to a table with two columns. The script cannot be directly executed in SQL Management Studio since the $(Id) and $(Name) variables are not set. If tried will get below error.
http://lh5.ggpht.com/-tlvv1ubhj_g/U-ErBnTl9nI/AAAAAAAACxQ/-dzL64xGYsE/a2_thumb%25255B1%25255D.png?imgmax=800
To test the script run the below command in command prompt.
sqlcmd -S "POC-DOLPHINQA" -d "TestDB" -i "C:\Temp\TestSQL.sql" -v Id=3 Name="Chandrasekara" –b
http://lh6.ggpht.com/-X50vk4ajt5s/U-ErDaS_7XI/AAAAAAAACxg/9A_zqftiAhw/a3_thumb%25255B3%25255D.png?imgmax=800
This adds a row to the table successfully.
http://lh3.ggpht.com/-DJLFgSvblBU/U-ErFF49tfI/AAAAAAAACxw/X8hfBlYQzQ4/a4_thumb%25255B2%25255D.png?imgmax=800
To create a new Action in the VS 2013 Release Management –> Log on to Client and go to Inventory tab and select Actions –> Click New
Create an action as shown below.
http://lh3.ggpht.com/-8j-6JrcuIGo/U-ErG-SmjiI/AAAAAAAACyA/XY6-ptQmAaM/a1_thumb%25255B7%25255D.png?imgmax=800
Arguments should be
-S "__ServerName__" -d "__DatabaseName__" -i "__ScriptName__" -v __Params__ -b
This will add below parameters to the action
ServerName – SQL Server Instance Name
DatabaseName – Database Name the script should run
ScriptName – Script to execute with full path (In deployment machine. Copying script to deployment machine can be done using XCOPY Deployer)
Params – Parameters for the SQL Script
Now this action can be used in Release template.
http://lh3.ggpht.com/-J7D7i71hxi4/U-ErIgKLyCI/AAAAAAAACyQ/Gxi25QRNHLk/a2_thumb%25255B3%25255D.png?imgmax=800
Let’s try a release.
http://lh3.ggpht.com/-6GOSzp8OC-I/U-ErKYLY3VI/AAAAAAAACyg/s1x1FqUUAFI/a3_thumb%25255B6%25255D.png?imgmax=800
The new action execution succeeded.
http://lh6.ggpht.com/-6Oan7lzn6tE/U-ErL6Qt5ZI/AAAAAAAACyw/f1tm-ofGuNI/a2_thumb%25255B5%25255D.png?imgmax=800
Table is added with new record.
http://lh6.ggpht.com/-Aopv8JXoUoM/U-ErNjLOZhI/AAAAAAAACzA/9wbdeIxN6P4/a4_thumb%25255B5%25255D.png?imgmax=800
This custom action can be used even to execute script while dynamically changing target DB, tables, columns etc. using SQLCMD syntax.
For example
Use $(MyDatabaseName)
SELECT x.$(ColumnName)
FROM Person.Person x