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?
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.