Azure SQL 数据仓库上的 sqlcmd - SqlState 24000，INSERT 语句后游标状态无效 [英] sqlcmd on Azure SQL Data Warehouse - SqlState 24000, Invalid cursor state after INSERT statement

问题描述

我正在编写一个脚本，在 Linux 上使用 SQLCMD 重新加载表，该脚本连接到 Azure SQL 数据仓库数据库.

I am working on a script to reload a table using SQLCMD on Linux connecting to an Azure SQL Data Warehouse database.

INSERT 语句完成后，下一条语句失败(但不会结束 sqlcmd 执行)并显示警告"

After the INSERT statement completes, the next statement fails (but does not end the sqlcmd execution) with the "warning"

insert into
  schema.table_temp
(
 ...list of columns
)
select
 ...list of columns
from
   schema.table
;

GO(COMMENT--> 在脚本中，但未在日志中回显.)

GO (COMMENT--> in script, but not echoed in log.)

(0 rows affected)   

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = 'schema' AND TABLE_NAME = 'table_nox' )
 DROP TABLE schema.table_nox
;

GO(COMMENT--> 在脚本中，但未在日志中回显.)

GO (COMMENT--> in script, but not echoed in log.)

SqlState 24000, Invalid cursor state

脚本继续运行，每个后续批次都得到相同的结果SqlState 24000，无效的游标状态警告"

The script continues to run with each subsequent batch getting the same SqlState 24000, Invalid cursor state "warning"

如果我注释掉 INSERT 语句，脚本会按预期在没有警告的情况下运行.我推测 INSERT 语句没有关闭游标，然后后续命令收到警告，这应该真正被视为错误并结束执行.(我在 sqlcmd 调用中打开了 -b 标志.)

If I comment out the INSERT statement, the script runs without warning as expected. I speculate the INSERT statement is not closing a cursor and then subsequent commands get the warning which should really be considered an eror and end the execution. (I have the -b flag on in the sqlcmd invocation.)

将脚本分成多个部分是我唯一的解决方案吗?

Is my only solution to break the script into multiple parts?

推荐答案

这似乎是 sqlcmd 在后台使用的 ODBC 驱动程序的问题.您能否确认您是否在 Azure SQL DB 中看到了这个问题?最好能看到您的 odbc.ini 和 odbcinst.ini 文件.可以分享一下吗?

this seems to be an issue with the ODBC Driver that is used by sqlcmd under the hood. Can you confirm if you see this issue with Azure SQL DB? It would also be good to see your odbc.ini and odbcinst.ini files. Can you share those out?

同时，我想推荐几个非常酷的 Node.js 命令行工具，它们更适合 Linux 和 Mac 环境:
SQL-CLI
猎豹

In the meanwhile, I would like to recommend a couple of very cool Node.js command line tools that are more suited for Linux and Mac environments:
SQL-CLI
Cheetah

谢谢，
见面

这篇关于Azure SQL 数据仓库上的 sqlcmd - SqlState 24000，INSERT 语句后游标状态无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！