Deploying SQL Changes Containing $(ESCAPE_SQUOTE())

Question

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.

If you directly use the `sqlpackage.exe` command locally will also get the same result. So this issue seems not related to TFS. — PatrickLu-MSFT, Jul 14 '17 at 09:56
No it's an issue directly with the `sqlpackage.exe` command, what I really need is a way to disable parameters when deploying my code. I think sqlpackage calls sqlcmd when deploying but I don't see an option for either of them that allows you to disable variables. — David Rogers, Jul 14 '17 at 14:17

score 2 · Answer 1 · answered Dec 21 '18 at 00:55

2

It seems that sqlcmd looks for the $( as a token, so separating those two characters is good enough. You can do this with a dynamic query that does nothing more than break the query into two strings:

DECLARE @query nvarchar(256) = N'... $' + N'(ESCAPE_SQUOTE(JOBID)) ...';
EXEC sp_executesql @query

answered Dec 21 '18 at 00:55

winter_limelight

79
7

Works like a charm...!!! This is the only solution I've seen so far that solve both problems (passing variables to the sql script and using SQL Server Agent Tokens inside it). Thanks. – Manuel Ferreiro Sep 11 '19 at 20:09

score 0 · Accepted Answer · answered Jul 17 '17 at 14:59

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.

score 0 · Answer 3 · answered Sep 16 '19 at 13:23

I propose another workaround my job has a step running : DTEXEC.exe /SERVER "$(ESCAPE_NONE(SRVR))"

I just have to add a SQLCMD variable before: :setvar SRVR "$(ESCAPE_NONE(SRVR))"

this way the toked is considered as SQLCMD variables $(SRVR) and is replaced by the requested value

Deploying SQL Changes Containing $(ESCAPE_SQUOTE())

Update 1

3 Answers3