none
Is there a way to override system defined SQLCMD parameters (DefaultFilePrefix) when deploying via SqlPackage.exe

    Question

  • I might be missing something obvious but I do not see an easy way to override system defined SQLCMD variables when deploying via SqlPackage.exe.

    Specifically I am trying to override the DefaultFilePrefix parameter.  If I pass this as a parameter in the following way it seems to be ignored in both the Publish and Script modes.

    /Variables:DefaultFilePrefix="My_DB_File_Prefix"

    Any tips on how to accomplish this?

    Friday, June 08, 2012 8:10 PM

Answers

  • Hello Mike

    The implicit variables are not directly settable. I have filed an internal bug because you should have received and error message that you cannot set the implicitly defined variables.

    For context, the $(DatabaseName) and $(DefaultFilePrefix) are derived from the TargetDatabaseName argument and the $(DefaultLogPath) and $(DefaultDataPath) are queried from the target server. The intended use of the implicit SQL Command variables is inside your scripts so you do not have to hard code these values.  If you want to create scripts where you can customize the paths externally, you will need to model the elements directly in your project and create custom SQL Command variables.

    If, for example, you want to be able to control file names and file paths of the log file and data file of the database, you can define your own SQLCMD variable and use that in the file definition:

    1. In the SSDT project settings add a new SQL Command variable called $(MyCustomFilePrefix)
    2. In your project add the following File definition

    ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [File1],

    FILENAME = '$(DefaultDataPath)$(MyCustomFilePrefix)_SqlFile1.mdf')

    TO FILEGROUP [PRIMARY]

    When you publish you can then provide a value for $(MyCustomFilePrefix). To see the generated script use the Script action as illustrated below:

    SqlPackage /a:Script /sf:”c:\db.dacpac” /tsn:someServer /tdn:YourDBName /v:MyCustomeFilePrefix=”MyCustomValue” /op:"C:\mydb.sql"

    In the generated script you will see the variable set:

    :setvar MyCustomFilePrefix "MyCustomValue"

    You will also see your database’s primary file use the value:

    PRINT N'Creating $(DatabaseName)...'GO

    CREATE DATABASE [$(DatabaseName)]

    ON

    PRIMARY(NAME = [File1], FILENAME = '$(DefaultDataPath)$(MyCustomFilePrefix)_SqlFile1.mdf') COLLATE SQL_Latin1_General_CP1_CI_AS

    Thanks

    Lonny


    Monday, June 11, 2012 6:24 PM

All replies

  • Hello Mike

    The implicit variables are not directly settable. I have filed an internal bug because you should have received and error message that you cannot set the implicitly defined variables.

    For context, the $(DatabaseName) and $(DefaultFilePrefix) are derived from the TargetDatabaseName argument and the $(DefaultLogPath) and $(DefaultDataPath) are queried from the target server. The intended use of the implicit SQL Command variables is inside your scripts so you do not have to hard code these values.  If you want to create scripts where you can customize the paths externally, you will need to model the elements directly in your project and create custom SQL Command variables.

    If, for example, you want to be able to control file names and file paths of the log file and data file of the database, you can define your own SQLCMD variable and use that in the file definition:

    1. In the SSDT project settings add a new SQL Command variable called $(MyCustomFilePrefix)
    2. In your project add the following File definition

    ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [File1],

    FILENAME = '$(DefaultDataPath)$(MyCustomFilePrefix)_SqlFile1.mdf')

    TO FILEGROUP [PRIMARY]

    When you publish you can then provide a value for $(MyCustomFilePrefix). To see the generated script use the Script action as illustrated below:

    SqlPackage /a:Script /sf:”c:\db.dacpac” /tsn:someServer /tdn:YourDBName /v:MyCustomeFilePrefix=”MyCustomValue” /op:"C:\mydb.sql"

    In the generated script you will see the variable set:

    :setvar MyCustomFilePrefix "MyCustomValue"

    You will also see your database’s primary file use the value:

    PRINT N'Creating $(DatabaseName)...'GO

    CREATE DATABASE [$(DatabaseName)]

    ON

    PRIMARY(NAME = [File1], FILENAME = '$(DefaultDataPath)$(MyCustomFilePrefix)_SqlFile1.mdf') COLLATE SQL_Latin1_General_CP1_CI_AS

    Thanks

    Lonny


    Monday, June 11, 2012 6:24 PM
  • Hi Lonny - thanks for the response and for filing the bug on the silent failure.  This works just fine. 

    I have one follow-up question.  For scripts that are included in the build SQLCMD variables can only be embedded in literal strings.  One thing I would like is to be able to use a SQLCMD variable as a parameter for file attributes that are not defined in literal strings.  It would be really nice to be able to use a variable for things like FILEGROWTH / SIZE /MAXSIZE.

    For example,

    ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME = [File1],FILENAME = '$(DefaultDataPath)$(MyCustomFilePrefix)_SqlFile1.mdf', FILEGROWTH=$(DefaultFileGrowth)) TO FILEGROUP [PRIMARY]

    I find myself "fixing up" a lot of this in pre/post-deployment scripts.  So far I haven't found a better way to handle this but if you have suggestions let me know.
    Tuesday, June 12, 2012 3:29 PM
  • Hello Mike

    Thanks for the feedback on this feature. I am not aware of a way to achieve what you are trying to do with SQLCommand variables. I would suggest putting this feature request on Microsoft connect and have users vote on it.

    Lonny

    Sunday, June 17, 2012 3:46 AM
  • Mike,

    Lonnie is correct, there is currently no way to express interger values inside the object definition using SQLCMD variables, they have to be within strings, otherwise they throw of the parser.

    As you noted the only option right now is to leverage the SQLCMD variable in a post-deployment script.


    -GertD @ www.sqlproj.com

    Sunday, June 17, 2012 4:59 PM
  • Thank you,

    been looking everywhere for this info

    Wednesday, December 12, 2012 6:58 PM
  • you can override the SQLCMD variables by specifying /p:CommentOutSetVarDeclarations when generating the update script. Then when executing the script say with Invoke-Sqlcmd you supply whatever values you need.

    Cheers,

    Graeme.

    Thursday, July 18, 2013 9:26 PM