locked
sqlcmd instructions in vsdb sql-scripts RRS feed

  • Question

  • I've added some pre-deployment user scripts in VS database project and successfully included one from another using the ":r" directive as it is when using sqlcmd.exe. But any other instruction like ":setvar" or using variables "$(VarName)" doesn't work. For example this code

    :setvar X=Y
    SELECT '$(X)'
    

    results in output below

    :setvar X=Y
    SELECT '$(X)'
    

    May be anybody know, what subset of sqlcmd directives may be used in vsdb sql-scripts and how to include the variables' values in resulting code?

    Friday, August 20, 2010 8:03 PM

Answers

  • :r $(MYLIB)\SomeScript.sql

    I see now - the problem SQLCMD variable is on the :r command rather than in the invoked script.  This issue was reported by Jamie Thompson in connect feedback item https://connect.microsoft.com/VisualStudio/feedback/details/575383/datadude-valid-sqlcmd-syntax-throws-error-at-build-time, but it doesn't look like it will be addressed in the current release.  Jamie used IF/ELSE control of flow as an alternate method. Will that work in your scenario?
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Guderian Wednesday, August 25, 2010 4:10 PM
    Wednesday, August 25, 2010 12:34 PM
  • Dan, thanks for your response, but I'm afraid this doesn't help. At first I have tried your sample and it failed. Then I have looked with reflector inside sources of the SqlSetupDeployTask used by MSBuild and found that sql processing is a simple readers stack, that know only ":r" instruction and nothing more :(
    • Marked as answer by Guderian Saturday, August 21, 2010 4:30 AM
    Saturday, August 21, 2010 4:30 AM

All replies

  • :setvar X=Y

    SELECT '$(X)'

    results in output below

    :setvar X=Y
    
    SELECT '$(X)'
    
    

    May be anybody know, what subset of sqlcmd directives may be used in vsdb sql-scripts and how to include the variables' values in resulting code?

    Are you saying the instructions are simply being echoed? 

    AFAIK, the full set of SQLCMD functionality is available from the SQLCMD.EXE command-line as well as from SSMS and VS.  Note that setvar does not accept a '=' after the variable name.  Should be:

    :setvar x y

    You can optionally enclose the value in quotes:

    :setvar x "y"

    Unline SQLCMD.EXE and SSMS, I don't think VS will display resultsets returned by SELECT commands; only PRINT and RAISERROR statements.  I would expect this to work, though:

    :setvar X "Y"

    PRINT '$(X)'


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, August 21, 2010 3:01 AM
  • Dan, thanks for your response, but I'm afraid this doesn't help. At first I have tried your sample and it failed. Then I have looked with reflector inside sources of the SqlSetupDeployTask used by MSBuild and found that sql processing is a simple readers stack, that know only ":r" instruction and nothing more :(
    • Marked as answer by Guderian Saturday, August 21, 2010 4:30 AM
    Saturday, August 21, 2010 4:30 AM
  • What version of VS are you using?  I see that there is an old connect item for VS 2005 (https://connect.microsoft.com/VisualStudio/feedback/details/276074/sqlcmd-support-in-sql-scripts-besides-post-and-predeployment) on this issue and a work item was created for the next release.  It was apparently addressed in VS 2008+GDR 2 and in VS 2010 since I'm able to use successfully test pre/post scripts with :r and sqlcmd variables in the called scripts.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, August 21, 2010 2:41 PM
  • VS2010. Are the same SqlSetupDeployTask working when project builds in your case? Because it definitely doesn't know about variables or any other commands except :r.
    Tuesday, August 24, 2010 8:30 AM
  • Yes, I can successfully build and deploy using both the IDE and MSBUILD command-line.  As a test, I simply created a new VS 2010 SQL 2008 database project with the "Create a deployment script (.sql) and deploy to the database" deploy action and configured the target connection.  I then added a new post-deploy script script with the code below and added the :r command to Script.PreDeployment.sql.

    :setvar X Y
    PRINT 'begin pre script'
    PRINT '$(X)'
    PRINT 'end pre script'
    

    Below is the build/deploy output:

    ------ Build started: Project: Database2, Configuration: Debug Any CPU ------
     Database2 -> C:\Users\dguzman\documents\visual studio 2010\Projects\Database2\Database2\sql\debug\Database2.dbschema
    ------ Deploy started: Project: Database2, Configuration: Debug Any CPU ------
      Deployment script generated to:
    C:\Users\dguzman\documents\visual studio 2010\Projects\Database2\Database2\sql\debug\Database2.sql
    
      begin pre script
      Y
      end pre script
    ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
    ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

    I repeated the test after adding $(X) to Database.sqlcmdvars with a default value of Z:

    ------ Build started: Project: Database2, Configuration: Debug Any CPU ------
     Database2 -> C:\Users\dguzman\documents\visual studio 2010\Projects\Database2\Database2\sql\debug\Database2.dbschema
    ------ Deploy started: Project: Database2, Configuration: Debug Any CPU ------
      Deployment script generated to:
    C:\Users\dguzman\documents\visual studio 2010\Projects\Database2\Database2\sql\debug\Database2.sql
    
      begin pre script
      Z
      end pre script
    ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
    ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

    Note that deploy action created a single deployment script (Database2.sql) with the pre and post deployment script files included in-line.  Below are excerpts from that script:

    <snip>
    
    :setvar X "Z"
    :setvar DatabaseName "Database2"
    :setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\"
    :setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\"
    
    GO
    <snip>
    
    /*
     Pre-Deployment Script Template							
    --------------------------------------------------------------------------------------
     This file contains SQL statements that will be executed before the build script.	
     Use SQLCMD syntax to include a file in the pre-deployment script.			
     Example:   :r .\myfile.sql								
     Use SQLCMD syntax to reference a variable in the pre-deployment script.		
     Example:   :setvar TableName MyTable							
            SELECT * FROM [$(TableName)]					
    --------------------------------------------------------------------------------------
    */
    :setvar X Y
    PRINT 'begin pre script'
    PRINT '$(X)'
    PRINT 'end pre script'
    
    GO
    /*
    Post-Deployment Script Template							
    --------------------------------------------------------------------------------------
     This file contains SQL statements that will be appended to the build script.		
     Use SQLCMD syntax to include a file in the post-deployment script.			
     Example:   :r .\myfile.sql								
     Use SQLCMD syntax to reference a variable in the post-deployment script.		
     Example:   :setvar TableName MyTable							
            SELECT * FROM [$(TableName)]					
    --------------------------------------------------------------------------------------
    */
    GO
    

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, August 24, 2010 12:41 PM
  • Yes, it produces sqlcmd output well, but there is an issue very significant for me. If you put the code below, that works fine in sqlcmd:

    :r $(MYLIB)\SomeScript.sql
    the VS2010 fails with error "Could not find a part of the path X:\somefolder\$(MYLIB)\SomeScript.sql. So VS with its individual :r interpretation and unavailability to expand variables brokes the code, that works well in sqlcmd. May be there is another way to include external scripts which locations defined using variables?
    Tuesday, August 24, 2010 2:55 PM
  • :r $(MYLIB)\SomeScript.sql

    I see now - the problem SQLCMD variable is on the :r command rather than in the invoked script.  This issue was reported by Jamie Thompson in connect feedback item https://connect.microsoft.com/VisualStudio/feedback/details/575383/datadude-valid-sqlcmd-syntax-throws-error-at-build-time, but it doesn't look like it will be addressed in the current release.  Jamie used IF/ELSE control of flow as an alternate method. Will that work in your scenario?
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Guderian Wednesday, August 25, 2010 4:10 PM
    Wednesday, August 25, 2010 12:34 PM
  • Thanks, this is definetely the same issue. Just didn't find IF/ELSE workaround there. It seems I'm blind and stupid today :)
    Wednesday, August 25, 2010 4:10 PM