Disable identity field on SQL Server

CloverDX Platform
1

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

2

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
3

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.

4

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.

5

Hi,

putting


threadSafeConnection=false
jdbcSpecific=MSSQL

in the dbConfig file solved my problems!

Thanks for your quick solution! Great!
Alex