locked
Pass parameters to a SQL script using Database Deployer - Execute Script from Release Management Tool RRS feed

  • Question

  • Hello,

    I need to pass 2 parameters to a SQL stored procedure from my Release Management template.

    The stored procedure I am trying to use requires 2 input parameters, a source database and a destination database.

    I am planning on creating a component that would use the "Database Deployer -Execute Script" as the tool to deploy the changes to my target database.

    I need to pass these 2 variables to sqlcmd using -v as far as I understand from the documentation of "sqlcmd".

    These two variables are declared in the stored procedure as @source, @destination.

    How do I configure the new component so that Release sqlcmd identifies these as parameters which I can configure in my release template for each stage?

    Is the syntax mentioned below correct?

    -S __ServerName__ -i "__ScriptName__" -v "source=__SourceDB__" -v  "destination=__DestinationDB__" -b

    Tuesday, August 5, 2014 2:34 PM

Answers

  • Hi Ballari,

    You can create a custom action like below to execute sql with parameters 

    Use it in release template to pass any parameter


    In params above you can pass SourceDB="YourSourceDBName" destination="YourDestinationDBName"

    Then you can use syntax like below to change to the required db in the script

    Use $(SourceDB)


    or even

    select * from $(SourceDB).dbo.TableName

    You can refer to my blog post here http://chamindac.blogspot.com/2014/08/custom-action-to-run-sql-script-with.html to learn more about creating and testing the tool.

    Cheers!

    Chaminda


    Tuesday, August 5, 2014 7:17 PM

All replies

  • Hi Ballari,

    You can create a custom action like below to execute sql with parameters 

    Use it in release template to pass any parameter


    In params above you can pass SourceDB="YourSourceDBName" destination="YourDestinationDBName"

    Then you can use syntax like below to change to the required db in the script

    Use $(SourceDB)


    or even

    select * from $(SourceDB).dbo.TableName

    You can refer to my blog post here http://chamindac.blogspot.com/2014/08/custom-action-to-run-sql-script-with.html to learn more about creating and testing the tool.

    Cheers!

    Chaminda


    Tuesday, August 5, 2014 7:17 PM
  • Hi Chaminda,  

    Thank you for sharing the nice reply and blog here, +1. It will be very beneficial for other community members having the similar questions.

    All your participation and support are very important to build such harmonious/ pleasant / learning environment for MSDN community.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 6, 2014 2:56 AM
    Moderator
  • How do you change this same scenario to Drop ad replication instead of a database? I can create the Drop Publication and Create Publication SQL scripts- how do I in cooperate it to Release management and parameters?

    Priyantha Perera

    Monday, November 9, 2015 10:36 PM