sqlcmd运行脚本但脚本不影响数据库
因此，我们有一个运行 SQL 脚本的 .bat 文件，例如，
@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
该脚本运行并且没有错误，但该脚本实际上并不影响数据库。在上面的示例中，正在运行的内容如下：
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
该脚本非常简单，您可能希望它添加一个名为 MandatoryInList 的列，但事实并非如此。奇怪的是脚本中没有语法错误，当我从 SQL Server Management Studio 运行它时，它运行良好。权限或哪个用户正在运行该脚本不存在问题，因为批处理文件中还有其他脚本可以正常运行。
我还应该提到，它通过创建一个新进程并运行它来从 GUI 运行，但不显示命令窗口。但无论如何，我已经像这样运行这个工具几个月了，没有出现任何问题。
有什么想法吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
因此它可以在 Management Studio 中运行，但不能在调用 sqlcmd 的批处理文件中运行。奇怪...
我不知道什么可能导致此问题，但这里有一些想法：
如果您从命令提示符而不是从批处理文件手动运行相同的“sqlcmd”命令，它会起作用吗？< /p>
如果您从命令提示符运行批处理文件，而不是从 GUI 应用程序调用它，它会起作用吗？
它正在向您的 dbupt.log 文件写入什么内容？它是否有标题“--- 03_Case6395_Publication.sql --- ”，并且之后是空的？
你可以在脚本中添加一些 PRINT 语句，这样你就可以看到它是在执行 IF 还是 ELSE 部分吗？
你可以
您能否尝试使用“2>&1”运算符将错误流也重定向到日志文件？例如：
sqlcmd -U %1 -P %2 -S %3 -d %4 -i 03_Case6395_Publication.sql -k -b >>> dbupt.log 2>&1
您可以尝试将“IF ERRORLEVEL 1”更改为“IF NOT ERRORLEVEL 0”吗？
您是否尝试过使用 SQL Profiler 来查看 SQL 实际执行的内容？有什么显示吗？
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?