SQL INSERT sp_cursor 错误 [英] SQL INSERT sp_cursor Error

问题描述

我有一对链接的 SQL 服务器:ServerA 和 ServerB.我想编写一个简单的 INSERT INTO SELECT 语句，它将一行从 ServerA 的数据库复制到 ServerB 的数据库.ServerB 的数据库是直接从 ServerA 复制的，因此它们应该具有完全相同的基本结构(相同的列名等)

I have a pair of linked SQL servers: ServerA and ServerB. I want to write a simple INSERT INTO SELECT statement which will copy a row from ServerA's database to ServerB's database. ServerB's database was copied directly from ServerA's, and so they should have the exact same basic structure (same column names, etc.)

问题是当我尝试执行以下语句时:

The problem is that when I try to execute the following statement:

INSERT INTO [ServerB].[data_collection].[dbo].[table1]SELECT * FROM [ServerA].[data_collection].[dbo].[table1]

我收到以下错误:

Msg 16902, Level 16, State 48, Line 1sp_cursor: 参数value"的值无效.

另一方面，如果我尝试执行以下语句:

On the other hand, if I try to execute the following statement:

INSERT INTO [ServerB].[data_collection].[dbo].[table1](时间)SELECT Time FROM [ServerA].[data_collection].[dbo].[table1]

语句工作正常，代码按预期执行.上面的语句执行得很好，不管我指定插入哪个表或多少表.

The statement works just fine, and the code is executed as expected. The above statement executes just fine, regardless of which or how many tables I specify to insert.

所以我的问题是，当我明确指定要复制的列时，为什么我的 INSERT INTO SELECT 语句可以正常运行，但当我告诉它使用*"复制所有内容时却不能正常运行?我的第二个问题是:我该如何解决这个问题?

So my question here is why would my INSERT INTO SELECT statement function properly when I explicitly specify which columns to copy, but not when I tell it to copy everything using "*"? My second question would then be: how do I fix the problem?

推荐答案

通过谷歌搜索来跟进我最初的预感，我找到了一个我认为足够可靠的来源，可以在答案中引用.

Googling around to follow up on my initial hunch, I found a source I consider reliable enough to cite in an answer.

指定的值"参数不是您的列之一，它是 sp_cursor 通过您的 INSERT INTO...SELECT 隐式调用.

The 'value' parameter specified isn't one of your columns, it is the optional argument to sp_cursor that is called implicitly via your INSERT INTO...SELECT.

来自 SQL Server Central...

我有一个 ssis 包，需要用数据填充 sql 表来自一个以竖线分隔的文本文件，每条记录包含 992 (!) 列....最初我将包设置为包含要使用的数据流任务访问模式设置为表的 ole db 目标控件或查看模式.但出于某种原因，当 运行包时会崩溃，并出现错误，指出参数值"无效在 sp_cursor 过程中.在探查器中设置跟踪以查看这个控件实际上做了什么，它似乎试图插入使用 sp_cursor 过程记录.在 SQL 中运行相同的查询Server Management Studio 给出了相同的结果.经过大量的测试和拔出头发，我发现通过更换 sp_cursor带有插入语句的语句记录填充良好表明 sp_cursor 在超过一定数量时无法应对参数的尝试.不清楚图.

I have an ssis package that needs to populate a sql table with data from a pipe-delimited text file containing 992 (!) columns per record. ...Initially I'd set up the package to contain a data flow task to use an ole db destination control where the access mode was set to Table or view mode. For some reason though, when running the package it would crash, with an error stating the parameter 'value' was not valid in the sp_cursor procedure. On setting up a trace in profiler to see what this control actually does it appears it tries to insert the records using the sp_cursor procedure. Running the same query in SQL Server Management Studio gives the same result. After much testing and pulling of hair out, I've found that by replacing the sp_cursor statement with an insert statement the record populated fine which suggests that sp_cursor cannot cope when more than a certain number of parameters are attempted. Not sure of the figure.

请注意您的情况和所引用的情况之间的共同主题 - 无数列.

Note the common theme here between your situation and the one cited - a bazillion columns.

同样的来源也提供了一种解决方法.

That same source offers a workaround as well.

我已经设法解决了这个问题，但是通过设置访问模式为表或视图 - 快速加载".再次查看跟踪确认 SSIS 通过插入批量"语句尝试此操作加载正常.

I've managed to get round this problem however by setting the access mode to be "Table or view - fast load". Viewing the trace again confirms that SSIS attempts this via a "insert bulk" statement which loads fine.

这篇关于SQL INSERT sp_cursor 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！