Database Project - Error with INSERT statement

Question

I am trying to seed my database using a Database Project in Visual Studio 2012. As park of my Post Deployment script I have a statement similar to the following:

INSERT INTO SomeTable (SomeTextCol) Values (N'$(function(){});')

The column is defined as NVARCHAR(MAX)

This causes the post build event to fail. However the strange thing is, if I execute the statement using SSMS it succeeds.

Is this caused by the fact the Database Project is using SQLCMD in the background?

How can I fix this error (whilst still being able to insert the jQuery into the table) - changing the column value isnt an option for me, as I do not own the schema.

Answer 1

You are right, this is caused by sqlcmd for which $ is a special character. One solution to fix this would be to concatenate two strings:

INSERT INTO SomeTable (SomeTextCol) Values (N'$'+'(function(){});');

It is a little ugly, but it works. An alternative would be to use a different symbol for jQuery like its complete name:

INSERT INTO SomeTable (SomeTextCol) Values (N'jQuery(function(){});');

Or, if you have a lot of references to jQuery inside the function:

(function(JQ){
  JQ(
     function(){
     //other references to JQ
     }
  );
 }
)(jQuery);

This last solution has the added advantage that anyone defining $ or JQ anywhere else on the page won't break your code, as "your" JQ is defined in a closure visible only to your code.