Search code examples
sqlsql-server-2008bcpalter

Incorrect syntax near ALTER NOCHECK CONSTRAINT - SQL Server 2008

I am trying to disable a table's constraint with an ALTER statement using BCP commands. My code is as follows:

SET @disableConstraints = 'SQLCMD -E -S server-Name -d '+@databaseName+' -Q "EXEC sp_MSforeachtable @command1 = ALTER TABLE '+@schemaName+'.'+@tableName+' NOCHECK CONSTRAINT ALL;" >> Z:\Log\ErrorLog.txt'

However when I execute the xp_cmdshell command, the ErrorLog.txt will print an error saying:

Msg 156, Level 15, State 1, Server server-Name, Line 1
Incorrect syntax near the keyword 'ALTER'.

I've been fiddling around with single quotes and double quotes, but I am not getting anywhere.

Can someone help spot the syntax error?

Solution

  • Needed two single quotes at the start of ALTER and the end of ALL

    SET @disableConstraints = 'SQLCMD -E -S server-Name -d '+@databaseName+' -Q "EXEC sp_MSforeachtable @command1 = ''ALTER TABLE '+@schemaName+'.'+@tableName+' NOCHECK CONSTRAINT ALL'';" >> Z:\Log\ErrorLog.txt'