1

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?

Karasu
  • 15
  • 5
  • Usually this is indicative of the column in error being an Identity column. With a name like, "Value" this seems less likely, but check that out and see if Value is an Identity. Also, double-check to see if the columns are, in fact, identical on both servers. (in name, data type and order.) – DeadZone Jun 23 '15 at 18:28
  • There is no column named "Value" in the table. Also, what would be the quickest way to check that the columns are, in fact, completely identical? (There are 1,000+ columns for the table in question, so that would be very time consuming.) – Karasu Jun 23 '15 at 18:57
  • Check [this](http://www.kepware.com/KEP_KB/?solution=/_ui/selfservice/pkb/PublicKnowledgeSolution/d?&id=50140000000SUKxCsY) out. From reading that, I'm thinking it is related to the fact that there are over 1,000 columns and the way the INSERT INTO...SELECT is working under the hood, you're breaking a maximum character length. I'd propose this as an answer if I had better documentation to support it instead of just a theory. – LDMJoe Jun 23 '15 at 19:24
  • I just made a quick check on my program that generates the data - there are 766 columns it has generated, not 1,000+ as I had previously assumed. – Karasu Jun 23 '15 at 19:47
  • I ran Red Gate comparison tool, and it indicated that the two databases are the exact same. – Karasu Jun 23 '15 at 20:21

1 Answers1

0

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.

LDMJoe
  • 1,591
  • 13
  • 17
  • Pardon my minuscule knowledge of SQL. but how do I set the access mode to "Table or view - fast load"? After a brief search, the MSDN mentioned that the setting can be found in the OLE DB Connection Manager / OLE DB Destination Editor, but I do not know how to access that. – Karasu Jun 23 '15 at 21:10
  • To pull this off it looks like you're going to have to do your work in an [SSIS package](https://msdn.microsoft.com/en-us/library/ms141134.aspx). The key is that under the hood an `INSERT BULK` is being executed, as opposed to `SP_cursor` (which was producing error). According to [this MSDN question](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d9bf8677-7138-457a-afae-948ae749465e/what-is-insert-bulk?forum=transactsql) there is no way to access it directly - it is only accessible via the Bulk Insert API's. – LDMJoe Jun 23 '15 at 23:52
  • How do I execute it in an SSIS package and make use of the INSERT BULK? – Karasu Jun 24 '15 at 14:02
  • By using "Table or view - fast load" in your destination setting for "Data Access Mode" - I found an answer to another question here that includes a [screenshot of how to set it](http://stackoverflow.com/questions/16705449/netezza-incremental-load-from-sql-server-using-ssis/16709877#16709877). – LDMJoe Jun 24 '15 at 14:29
  • How do you access the OLE DB Destination Editor? – Karasu Jun 24 '15 at 15:21
  • In your SSIS Package, go to the Data Flow tab, hit Control+Alt+X to show the Toolbox. In the Toolbox, under Data Flow Destinations (or Other Destinations, depending on version), grab an OLE DB Destination and drop it on your Data Flow tab. Double-click on it to bring up the properties, which will look like the screenshot linked in the previous comment. – LDMJoe Jun 24 '15 at 15:53
  • How do I access the SSIS package? – Karasu Jun 24 '15 at 21:13
  • You are gonna create it, an it sounds like it'll be your first. [This MSDN Tutorial](https://msdn.microsoft.com/en-us/library/ms169917.aspx) is a good place to start. You may want to consider wrapping this question up, as the original question is answered. I expect you'll have more SSIS questions as you're learning, and creating new questions for those specific hurdles will draw the attention of the community at large instead of just me. – LDMJoe Jun 25 '15 at 00:35
  • I made a few discoveries. Turns out that I can insert records from a remote into a local server, such as script execution coming from ServerA that inserts from ServerB to ServerA, or script execution coming from ServerB that inserts from ServerA to ServerB. However, I cannot insert records from a local to a remote server. This seems to indicate that it's a permissions issue, rather than sp_cursor simply being unable to handle 766 columns. – Karasu Jun 26 '15 at 15:15