Possible bug in pre- and post-deployment scripting?

Question

Hi folks, I came a cross a problem recently with our post-deployment scripting and was wondering if anyone else had encountered it or even if it was a known bug in VSDB?

I'm writing our post-deployment scripts such that we insert our static data for certain tables into temp tables, then insert each line if it doesn't exist, like so:

DECLARE @RowVariable int;
DECLARE @MaxRowVariable int;

CREATE TABLE #TempTable(
RowNumber int NOT NULL,
PK_Value int NOT NULL,
Identifier nvarchar(75) NOT NULL,
Text_Value nvarchar(2000) NOT NULL
);

WITH TableContent (
PK_Value,
Identifier,
Text_Value
)
AS(
SELECT 1, N'Identifier_1', N'I am some text'
UNION ALL
SELECT 2, N'Identifier_2', N'I am some more text'
)
INSERT INTO #TempTable
SELECT
ROW_NUMBER() OVER(ORDER BY PK_Value)
PK_Value,
Identifier,
Text_Value
FROM TableContent;

SET @RowVariable = 1;

SET @MaxRowVariable = (SELECT MAX(RowNumber) FROM #TempTable);

WHILE @RowVariable <=@MaxRowVariable
BEGIN
	IF NOT EXISTS(
			SELECT [Row].RowNumber
			FROM dbo.RealTable
				CROSS APPLY(
					SELECT RowNumber
					FROM #TempTable
					WHERE #TempTable.RowNumber = @RowVariable
					AND #TempTable.PK_Value = dbo.RealTable.PK_Value) AS [Row])
		BEGIN;
			INSERT INTO dbo.RealTable (
				PK_Value,
				Identifier,
				Text_Value)
			SELECT
				PK_Value,
				Identifier,
				Text_Value
			FROM #TempTable
			WHERE RowNumber = @RowVariable;
		END;
	SET @RowVariable = @RowVariable + 1;
END;

DROP TABLE #TempTable;

However I came across another "Fatal Parser Error" when one of the Text_Values contained a string like "The following characters are not permitted in this field: a-z A-Z 0-9 !£$()[]{};'@~,.`¬".

It appears that the substring "$()" is being interpreted as a SQLCMD variable, even though it is being declared as a nvarchar string like so:

N'The following characters are not permitted in this field: a-z A-Z 0-9 !£$()[]{};''@~,.`¬'

I worked around it by splitting the $ and ( like so:

N'The following characters are permitted in this field: a-z A-Z 0-9 !£$' + N'()[]{};''@~,.`¬'

But surely the SQLCMD parsing should be bright enough to ignore the $() stuff if it's been placed inside a string? The Visual Studio UI changes the string's colour-coding to reflect that it is a string, so why doesn't the parser understand the difference??

Thanks, Rob

Answer 1

The problem is that you can have a SQLCMD variable wrapped inside a string such as '$(DatabaseName)'.  I think you work around is the best possible solution right now.

Answer 2

So it's valid for SQLCMD variables to be parsed within stings that are held in a script that is added to the main pre- or post-deployment script via the :r .\script.sql syntax?

I thought that SQLCMD variables were only usable within the main pre-or post-deployment scripts themselves?

Answer 3

All the :r does is "#include" the file; the variables are parsed after that.

Answer 4

Ah-ha, thanks for clarifying that for me, Larry.

So, if this behaviour would be expected if the same code snippet existed within the main pre- or post-deployment script, I guess my question is: why?

The variable syntax is clearly inside an apostrophe-delimited string, so I don't understand why it isn't excluded from the parser. Is this by design? Surely, if I wanted to include a SQLCMD variable in my string I should be writing something like N'Start of string, ' + $(StringVariable) + N' and end of string.'? Expecting a parser to see a variable inside a string seems somewhat wrong to me.

Answer 5

Duke, I'm sorry for unmarking JAdams2006's response as an answer, but I'm failing to see why the SQLCMD values are being parsed when they are clearly inside a correctly delimited text string.

In any other programming language, this would clearly be an error, so why is it not in T-SQL's case?

There's a parser bug here as far as I can see, as I'm having to use a hack to get around it, which means one more thing for my dev team to be aware of when writing their own additions to our DB code.

I'd like to know if the VSTSDB dev team are aware of this issue and, if so, what sort of timescales are in place for the issue. If they aren't aware of it, how can I send a bug report to them?

Answer 6

This is the way SQLCMD behaves. A variable within single quotes will be bound to the value of the variable. The following example is from the SQLCMD utility documentation: http://msdn.microsoft.com/en-us/library/ms188714.aspx. Note the WHERE clause. I agree that it is not the way C, C++, C# or VB behave.

C. Using command prompt environment variables within sqlcmd

In the following example, four environment variables are set and then called from sqlcmd.

C:\>SET tablename=Person.Contact

C:\>SET col1=FirstName

C:\>SET col2=LastName

C:\>SET title=Ms.

C:\>sqlcmd -d AdventureWorks

1> SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name

2> FROM $(tablename)

3> WHERE Title ='$(title)'

4> GO

---

Duke Kamstra - Program Manager - VSTS Database Edition (Data Dude, DBPro)