2

I'm trying to troubleshoot an error that I'm getting when I try to insert/append into an Azure SQL database.

The error I'm getting:

Error: Write Data In-DB (353): Error running PreSQL on "NoTable": Microsoft OLE DB Provider for SQL Server: An explicit value for the identity column in table 'CHART' can only be specified when a column list is used and IDENTITY_INSERT is ON.\42000 = 8101

The tool that I'm using to perform the insert is Alteryx 10.0, a great data blending and advanced analytics tool, specifically the Write Data In-Database tool.

I've been trying to figure this out since Saturday morning, with no luck. This evening I decided to review the OLE connection string to see if it provided me any clues. I came across the Use Procedure To Prepare parameter in the connection string. Documentation indicates that this parameter dictates how a temporary stored procedure is created and released. There are two possible setting:

  1. A temporary stored procedure is created when a command is prepared. All temporary stored procedures are dropped when the session is released.

  2. A temporary stored procedure is created when a command is prepared. The procedure is dropped when the command is unprepared, when a new command is specified, or when all application references to the command are released.

Question 1: do temporary stored procedures rely upon global temporary table? If so, could this be the cause of my error, because Azure SQL database does not support global temporary variables?

Question 2: is the Use Procedure To Prepare parameter optional? If this is in fact the problem I'm running into, the hope would be I could remove this parameter from my connection string and avoid creating a global temporary table.

EDIT: Despite the error message indicating I'm trying to insert into the identity column, I am not. I believe the beginning of the error message that specifies "No Table" is the tip off that the system is looking for a global temporary table that is not supported by Azure SQL

Thanks in advance for you input.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mutuelinvestor
  • 3,384
  • 10
  • 44
  • 75
  • So what is the Use Procedure For Prepare set to, 1 or 2? With older OLE's it was also a option 0, which "A temporary stored procedure is not created when a command is prepared" see https://support.microsoft.com/en-us/kb/193135 – Dijkgraaf Sep 15 '15 at 01:13
  • It was set to 1 originally, I believe that is the default value, then I removed it from the connection string, but it still generated the same error message. Perhaps when its removed from the string it defaults to the default setting 1. I will try 0 and see what that yields. – Mutuelinvestor Sep 15 '15 at 01:18
  • @Dijkgraaf I tried setting the parameter to zero, but still got the error message. So, now I have gotten the error message for 1 and 0. I'm going to also try 2 just to pursue all options. – Mutuelinvestor Sep 15 '15 at 01:25

1 Answers1

1

Answer

An explicit value for the identity column in table 'CHART' can only be specified when a column list is used and IDENTITY_INSERT is ON.

That error message implies that you are trying to INSERT data into an IDENTITY column in the CHART table. It also suggests a solution. Include a column list and turn on identity insert. You can do that like this: 

SET IDENTITY_INSERT CHART ON;

INSERT CHART(column1, column2)
VALUES (value1, value2);

SET IDENTITY_INSERT CHART OFF;

Alternatively, you can just not try to insert the identity value and leave that up to SQL Server. An IDENTITY column is a value that SQL Server calculates automatically. If we want to set it manually, we need to be explicit about that, if we do not want to set it manually, then we just do not include a value in the VALUES list.

See also

IDENTITY (Property)

SET IDENTITY_INSERT

Shaun Luttin
  • 133,272
  • 81
  • 405
  • 467
  • 1
    I suspect the part of error message you reference is a red herring, as I am not inserting to the Identity column. Thanks for the inpupt. – Mutuelinvestor Sep 14 '15 at 23:36
  • How do you know that you are not inserting into the identity column? – Shaun Luttin Sep 14 '15 at 23:39
  • 1
    Perhaps I don't, but I created the record to insert and it does not include the identity column, as you suggested in your answer I'm leaving it up to SQL to do it for me. – Mutuelinvestor Sep 14 '15 at 23:46
  • If you are both 1. using a column list and 2. ensuring that there are no IDENTITY columns in that list, then you're right, it seems like a red herring. That said, it's odd that that error message would come out of nowhere. – Shaun Luttin Sep 14 '15 at 23:51
  • what do you make of the "No Table" part of the error message. Thanks again. – Mutuelinvestor Sep 14 '15 at 23:57
  • I make head scratches, because I have never seen an error message like that. My guess is that that part of the error comes from the Write In-DB tool, whereas the latter part of the error comes from the OLE DB provider. That is what the colons `:` in the error message indicate. – Shaun Luttin Sep 15 '15 at 00:00