Disable identity field on SQL Server

axetheaxe

• May 12, 2011 00:00

• Answered

Follow

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

• 

  avackova

  • May 12, 2011 09:41

  0

  Comment actions Permalink

  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

  • DBConnection.png
• 

  axetheaxe

  • May 12, 2011 09:51

  0

  Comment actions Permalink

  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.
• 

  avackova

  • May 12, 2011 12:22

  0

  Comment actions Permalink

  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.
• 

  axetheaxe

  • May 12, 2011 13:01

  0

  Comment actions Permalink

  Hi,
  
  putting
  

  
  threadSafeConnection=false
  jdbcSpecific=MSSQL
  

  
  in the dbConfig file solved my problems!
  
  Thanks for your quick solution! Great!
  Alex

Please sign in to leave a comment.