SQL INSERT sp_cursor Error

Question

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]

I get the following error:

Msg 16902, Level 16, State 48, Line 1 sp_cursor: The value of the parameter 'value' is invalid.

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

INSERT INTO [ServerB].[data_collection].[dbo].[table1] (Time) 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.

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?

Answer 1

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

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.

From SQL Server Central...

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.

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.