禁用 SQLCMD 语法检查

发布于 2024-08-07 22:33:59 字数 631 浏览 7 评论 0原文

我们有时会编写数据端口脚本来重命名表或列并具有其他复杂的逻辑。我们还在 IF 语句中使用 SQL 引用那些旧的列或表。我们有一个使用 IF 语句的标准，使我们的 SQL 脚本可以多次运行。

然而，有时，即使 if 语句的计算结果为 false，块中包含的代码也会出错，我猜是因为语法检查。

if 1 = 0
begin

   select * from mydatabase.dbo.mytable; -- doesn't execute, but still errors because doesn't exist

end

另一个奇怪的事情是它并不总是一致的，有时它工作得很好。

有谁知道我是否可以通过一种方法禁用逐个脚本的此类检查。

谢谢

注意

我知道人们会说他们尝试了类似的情况并且没有错误。起初对我来说，它在 SSMS 中没有错误，但在 SQLCMD 中出错。

sqlcmd MyTestScript.sql -r 1 -U user -P password

然后我把SSMS置于SQLCMD模式查询-> CMDMODE 仍然没有给出错误。然后重新运行几次后，两者都开始出错。

原文

We sometimes write dataport scripts that rename tables or columns and have other complex logic. We also have SQL in IF statements referencing those old columns or tables. We have a standard to use IF statements to make our SQL Scripts multi run friendly.

However occasionally, even though the if statement evaluates to false, the code wrapped in the block errors out, I'm guessing because of a syntax check.

if 1 = 0
begin

   select * from mydatabase.dbo.mytable; -- doesn't execute, but still errors because doesn't exist

end

Another weird thing is it isn't always consistent, sometimes it works fine.

Does anybody know if a way I can disable these sort of checks on a script by script basis.

Thanks

NOTE

I know people are going to say they tried a similar situation and it doesn't error. At first for me it didn't error in SSMS but it errored in SQLCMD.

sqlcmd MyTestScript.sql -r 1 -U user -P password

Then I put SSMS in SQLCMD mode Query -> CMDMODE and it still didn't give the error.
Then after rerunning a few times it started to error in both.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

紫竹語嫣☆ 2024-08-14 22:33:59

尝试使用TRY-CATCH块，简单易用，优雅。

如果 1 = 0
开始尝试
从 mydatabase.dbo.mytable 选择 *；
结束尝试
开始捕捉
--错误处理代码
选择 ERROR_MESSAGE()
结束接球

回复收藏 0 原文

风轻花落早 2024-08-14 22:33:59

我认为内置存储过程的execute_sql可以解决这个问题。

回复收藏 0 原文

嘿看小鸭子会跑 2024-08-14 22:33:59

在您的开发环境中运行以下代码

DROP Procedure dbo.#TestProc
GO
CREATE Procedure dbo.#TestProc
AS

IF 1=0
BEGIN
 SELECT 1 FROM XXXXX
END
ELSE
BEGIN
 SELECT * 
 FROM Information_Schema.Tables 
 WHERE Table_Name = 'XXXXX'
END

GO
EXEC #TestProc

它会编译。它执行。无论是编译还是执行过程中都不会出错。

我使用的是 SQL 2005。

从结果中可以看到，没有名为 XXXXX 的表。

我认为你的条件实际上已经得到满足并且代码正在执行。

Edt

我比我输入的内容更进一步：

创建存储过程并为以下每一项运行

SELECT * FROM XXXXX.XXXXX /* owner XXXXX */
SELECT * FROM XXXXX.XXXXX.XXXXX /* database XXXXX */

当我完全限定名称时，代码爆炸了

SELECT * FROM XXXXX.XXXXX.XXXXX.XXXXX /* linked server XXXXX */

现在它查找链接服务器，但没有找到1 并抛出错误。

Msg 7202, Level 11, State 2, Procedure #TestProc, Line 6
Could not find server 'XXXXX' in sys.servers. 
Verify that the correct server name was specified. 
If necessary, execute the stored procedure sp_addlinkedserver 
to add the server to sys.servers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '#TestProc'.

Run the following code in your dev environment

DROP Procedure dbo.#TestProc
GO
CREATE Procedure dbo.#TestProc
AS

IF 1=0
BEGIN
 SELECT 1 FROM XXXXX
END
ELSE
BEGIN
 SELECT * 
 FROM Information_Schema.Tables 
 WHERE Table_Name = 'XXXXX'
END

GO
EXEC #TestProc

It compiles. It executes. It does not error out during either compilation or execution.

I am using SQL 2005.

As you can see from the results, there is no table called XXXXX.

I think your conditions are actually being met and the code is executing.

Edt

I went further than what I typed:

The stored proc gets created and runs for each one of the following

SELECT * FROM XXXXX.XXXXX /* owner XXXXX */
SELECT * FROM XXXXX.XXXXX.XXXXX /* database XXXXX */

The code bombs out when I fully qualify the name

SELECT * FROM XXXXX.XXXXX.XXXXX.XXXXX /* linked server XXXXX */

Now it LOOKS for the linked server and does not find one and throws an error.

Msg 7202, Level 11, State 2, Procedure #TestProc, Line 6
Could not find server 'XXXXX' in sys.servers. 
Verify that the correct server name was specified. 
If necessary, execute the stored procedure sp_addlinkedserver 
to add the server to sys.servers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '#TestProc'.

回复收藏 0 原文