sqlcmd runs script but script does not affect database

Question

Hey all,

So we have a .bat file that runs SQL scripts, e.g.

@ECHO --- 03_Case6395_Publication.sql ---  >> dbupt.log
sqlcmd -U %1 -P %2 -S %3 -d %4 -i 03_Case6395_Publication.sql -k -b >> dbupt.log
IF ERRORLEVEL 1 GOTO ErrorTag

The script runs and gives no errors, but the script doesn't actually affect the database. In the example above, here is what is being run:

IF NOT EXISTS (SELECT 1 FROM [dbo].[syscolumns] WHERE [NAME] = N'MandatoryInList' AND [ID] = object_id(N'Pub_Type'))
BEGIN
    ALTER TABLE [dbo].[Pub_Type] ADD [MandatoryInList] bit NULL CONSTRAINT [DF_PubType_MandatoryInList] DEFAULT (0)
END
ELSE
BEGIN
    ALTER TABLE [dbo].[Pub_Type] ALTER COLUMN [MandatoryInList] bit NULL 
END
GO

The script is pretty simple and you would expect it to add a column called MandatoryInList, however it doesn't. The odd thing is there are no syntax errors in the script and when I run it from SQL Server Management Studio, it runs fine. There's no issues with permissions or which user is running the script because there are other scripts that run fine in the batch file.

I should also mention that this runs from a GUI by creating a new process and running it but not showing the command window. But regardless, I've been running the tool like this for months without any issues.

Any ideas?

Answer 1

So it works from Management Studio but not from your batch file that calls sqlcmd. Strange...

I don't know what could cause this, but here are some ideas:

• If you run the same "sqlcmd" command manually from the command prompt, rather than from your batch file, does it work?

• If you run the batch file from the command prompt, rather than invoking it from the GUI app, does it work?

• What's it writing to your dbupt.log file? Does it have the header "--- 03_Case6395_Publication.sql --- ", and it's empty after that?

• Can you add some PRINT statements to the script, so you can see whether it's executing the IF or the ELSE part?

• Can you try redirecting the error stream to your log file as well, using the "2>&1" operator? E.g.:

  sqlcmd -U %1 -P %2 -S %3 -d %4 -i 03_Case6395_Publication.sql -k -b >> dbupt.log 2>&1

• Can you try changing "IF ERRORLEVEL 1" to "IF NOT ERRORLEVEL 0"?

• Have you tried using SQL Profiler to see what SQL is actually executing? Does anything show up?