SQL Server - set identity_insert on

FlowHeater-Team
Topic Author
Offline
Administrator

More

12 years 1 week ago #2087 by FlowHeater-Team

SQL Server - set identity_insert on was created by FlowHeater-Team

translated submission in the german forum. you can read the original entry here .

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.

Code:

use [test190]

set dateformat 'ymd'

begin tran

set identity_insert veis_invoice on

insert into [veis_invoice] ([pk_veis_invoice], [entitydate], [entitystate], [reqnumber], [companyid], [personalaccountid], [inv_type], [invoicetype], [amountbasecurrency], [currencyid], [referencenumber], [documentdate], [transactiondate], [modeofpayment], [factoring]) values(1500171601151820, '2010-12-28 13:02', 0, 15001716, '115', 1151817, 'tte_customerinvoice', 'ite_invoice', 719.71, 'eur', '00015001716', '2010-09-08', '2010-09-08', 'pte_default', 'fte_no')

commit tran

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

thanks
martin

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in or Create an account to join the conversation.

FlowHeater-Team
Topic Author
Offline
Administrator

More

12 years 1 week ago #2088 by FlowHeater-Team

Replied by FlowHeater-Team on topic Re:SQL Server - set identity_insert on

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.

Code:

bool bFirst = true;

public object DoWork()
{
// only execute first time
if (!AdapterWrite.OnlyTest && bFirst)
{
bFirst = false;

// modify here the SQL table name
AdapterWrite.Execute("set identity_insert [your_table] on");
}

// just returning the first input parameter
return InValues[0].GetValue();
}

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 .

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in or Create an account to join the conversation.