SQL Server - set identity_insert on

translated submission in the german forum. you can read the original entry here: SQL Server - identity_insert

hello,

i am trying to migrate an sql database. it is important for me to be able to define a unique key myself, in order that i can associate data in another table using this key. however, a direct import always reports the error message: "an explicit value... cannot be inserted when identity_insert is set to off."

if i generate an sql script and edit it to manually edit it to insert the command "set identity_insert veis_invoice on" prior to the import statements, everything works properly.

is there some way to specify this command should be executed first, to enable me to write directly to the database?

thanks
martin

Hallo,

That is an interesting requirement. You can achieve this with Version 1.3.0 and above, together with a .NET Script Heater and the following short C# Script.

Procedure: Drag and drop a .NET Script Heater onto any existing connection (pipe), the best choice is to use the field for Auto Increment (Identity). Next copy the script above (modified with your own table name – see comment in the script). Now open the Configurator on the WRITE side and in the SQL Identity field uncheck the property "Auto Increment". This simply tells FlowHeater to pass on the field to SQL Server, it does not modify the actual SQL Schema of the SQL Server table.

The .NET script gets executed once, and effectively generates the following command before the actual SQL Insert statements on the WRITE side (=SQL Server Adapter):

set identity_insert [YOUR TABLE] on

Thereafter AutoID (Identity) values per Insert can also be inserted on the specified SQL Server table.
Please note: The above only works in combination with the SQL Server Adapter .