Search code examples
sql-servervisual-studiosqlcmdsqlpackage

Deploying SQL Changes Containing $(ESCAPE_SQUOTE())

I have a Database project in Visual Studio that I am attempting to deploy automatically to a test environment nightly. To accomplish this I am using TFS which leverages a PowerShell script to run "SqlPackage.exe" to deploy any changes that have occurred during the day.

Some of my procs contain logic that is run inside of a script that is part of a agent job step and contains the following code(In dynamic SQL):

$(ESCAPE_SQUOTE(JOBID))

When deploying changes that affect this proc, I get the following issue:

SQL Execution error: A fatal error occurred. Incorrect syntax was encountered while $(ESCAPE_SQUOTE( was being parsed.

This is a known issue, it appears as though that is not supported. It appears to be a function of the "SQLCmd" command misinterpreting the $( characters as a variable:

"override the value of a SQL command (sqlcmd) variable used during a publish action."

So how do I get around this? It seems to be a major limitation of "sqlcmd" that you can't disable variables, I don't see that parameter that supports that...

Update 1

Seems as through you can disable variable substitution from "sqlcmd" by feeding it the "-x" argument(Source, Documentation):

-x (disable variable substitution)

Still don't see a way to do this from "SqlPackage.exe" though.

Solution

  • One way to get around this is to refactor the "$(ESCAPE_SQUOTE(JOBID))" string into a scalar function, then setup a PowerShell script to directly invoke the "Sqlcmd" command with the "-x" parameter to "Create/Alter" said function before running "SqlPackage.exe".

    Looks something like this in PowerShell:

    $sql = @"
USE $database
GO

CREATE FUNCTION [dbo].[GetJobID] ()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN '$(ESCAPE_SQUOTE(JOBID))'
END

GO
"@;

Sqlcmd -S $servername -U $username -P $password -Q $sql -x;

    This is a pretty poor workaround, but it does accomplish the task. Really hoping for something better.