CODEWITHSUNDEEP
sqlsql-serversqlcmd
jcreamer898
jcreamer898

Reputation: 8189

sqlcmd with jQuery in the insert statements

I need to do some seeding of an MSSQL database and I have many sql files that need to run in order.

Basically I have a start.sql file...

SET NOCOUNT ON
GO

PRINT 'First File'
:r "c:\sql\firstfile.sql"

PRINT 'Second File'
:r "c:\sql\secondfile.sql"

And I am running this with sqlcmd -S LOCALHOST\SqlExpress -E -i "C:\sql\start.sql" -x

The issue is in one of the files I am inserting HTML data into the DB and it has some script references in it that have jQuery $('#stuff') in them. This is throwing syntax errors because of the SQL variable substitution.

I was hoping the -x option would stop the substitution, but I still got an error. Am I missing something else?

Upvotes: 3

Views: 751

Answers (1)

schellack
schellack

Reputation: 10274

SQL Command Mode is supposed to treat $(something) as a variable.

When in SQLCMD Mode, the result of PRINT '$test' will be $test, but executing PRINT '$(test)' will return the message 'test' scripting variable not defined. Using the -x option (lowercase x) should NOT return that message, though.

I tested that out by running the following in a Windows command prompt:

sqlcmd -S localhost -E -i "c:\failsInSqlCmdMode.sql" -x

Where the .sql script contains the following SQL:

declare @test varchar(10) 
set @test = '$(#stuff)'
select @test
go

The result is:

----------
$(#stuff)

(1 rows affected)

Only when I remove the -x from the end of the command, I get an error:

Sqlcmd: Error: Syntax error at line 2 near command '#' in file 'C:\failsInSqlCmdMode.sql'.

I tested this out on Windows 7 x64 connecting to SQL Server 2008 R2.

Upvotes: 1

Related Questions