Disable identity field on SQL Server

Hi,

I try to copy a database from Postgres to SQL Server 2005 (not my choice!). So I need to insert values to an identity field.

I found a similar topic in the forum http://forum.cloveretl.org/viewtopic.php?f=4&t=3342.
So I tried to turn IDENTITY_INSERT on with the following nearly minimal grf file but got an error
“Cannot insert explicit value for identity column in table ‘TEST_TABLE’ when IDENTITY_INSERT is set to OFF”:


<?xml version="1.0" encoding="UTF-8"?>
<Graph description="Copy table from one db to another one" name="CopyDB">
	<Global>
	   <Connection dbConfig="${ConfigIn}" id="ConnectionIn" type="JDBC"/>
	   <Connection dbConfig="${ConfigOut}" id="ConnectionOut" type="JDBC" threadSafeConnection="false"/>
	   <Metadata fileURL="${MetadataOutDir}/TEST_TABLE-out-record.fmt" id="MetadataOut_TEST_TABLE"/>
	</Global>
    <Phase number="0">
		<Node id="TEST_TABLE_OUTPUT_IDENTITY_INSERT"
			type="DB_EXECUTE"
			dbConnection="ConnectionOut"
			sqlQuery="set IDENTITY_INSERT TEST_TABLE ON"
			inTransaction="SET"
		/>
    </Phase>
    <Phase number="1">
		<Node 
            id="TEST_TABLE_INPUT" 
            type="DB_INPUT_TABLE"
            dbConnection="ConnectionIn"
            sqlQuery="select DESCRIPTION, ID from TEST_TABLE"
        />
        <Node 
            id="TEST_TABLE_OUTPUT" 
            type="DB_OUTPUT_TABLE"
            dbConnection="ConnectionOut" 
            dbTable="TEST_TABLE" 
            batchMode="${BatchMode}" 
            batchSize="1000"
            commit="10000"
            maxErrors="0"
            errorAction="ROLLBACK"
            dbFields="DESCRIPTION; ID"
        />
        <Edge fromNode="TEST_TABLE_INPUT:0" id="TEST_TABLE_EDGE0" metadata="MetadataOut_TEST_TABLE" toNode="TEST_TABLE_OUTPUT:0"/>
    </Phase>
</Graph>

The postgres database table:


	CREATE TABLE test_table	(
		id bigint NOT NULL,
		description character varying(50),
		CONSTRAINT test_table_pkey PRIMARY KEY (id)
	);

The SQL Server table:


    create table TEST_TABLE (
        ID BIGINT identity not null,
        DESCRIPTION VARCHAR(255) not null,
        primary key (ID)
    );

What is wrong here? Is setting the jdbcSpecific property necessary? In that case: Why do I get the error


WARN  [main] - Optimizing connection failed: The CLOSE_CURSORS_AT_COMMIT option is not currently supported by the setHoldability method.
WARN  [main] - Try to use another jdbc specific

I tried it with cloveretl version 3.0.2.

I hope someone can help! I’m dealing with this problem since hours.

Thanks in advance

Hello,
you probably forgot to uncheck the “Thread-safe connection” check-box. Your connection configuration should look as follows:

#Tue Mar 04 12:56:15 CET 2008
user=my_user
name=MSSqlConnection
password=my_password
threadSafeConnection=false
dbURL=jdbc\:jtds\:sqlserver\://hostaname\:1433;DatabaseName\=TestDemo
passwordEncrypted=false
database=MSSQL
jdbcSpecific=MSSQL

When you look at the ‘Configuration’ part in my example you’ll see

threadSafeConnection="false"

And setting the jdbcSpecific property to MSSQL resulted in the above described error.

What do you have in ConfigOut parameter? If dbConfig attribute is specified, all others attributes are ignored and values from the configuration file are used.

Hi,

putting


threadSafeConnection=false
jdbcSpecific=MSSQL

in the dbConfig file solved my problems!

Thanks for your quick solution! Great!
Alex