<SQLIDENTITY> issue in child tables

[sxavier]

After upgrading to Jitterbit 3.0 from 2.x, I am having issue with <SQLIDENTITY> in the target child table.

For example, when a mapping is done to put data into (address -> person) tables, joined by address_id, the person.person_id is not getting auto-incremented.

The following is the error I get:
Error occurred on writing to target database, Wrong value for numeric field. value:"" for column: "address.person$person_id"

-Suresh

[Frank]

In order for us to look into your problem, we have to know the database and ODBC/JDBC driver you are using. Please also tell us the server version number also.

Frank

[sxavier]

Frank,

Jitterbit Server: 3.0.0.64-community-complete-win32
Database: SQL Server 2005
Driver: SQL Server
I have attached the DB SQL file and JitterPak also.

Thanks!
Suresh

[emanuel]

This is a known issue with the released community version. If you use a manual connection string it *may* work if you change

<Subprotocol>sqlserver</Subprotocol>
to
<Subprotocol>sql server</Subprotocol>

in JdbcDrivers.com. In any case it should work in the latest enterprise build

http://www.jitterbit.com/Files/Builds/i ... -win32.exe

http://www.jitterbit.com/Files/Builds/i ... -win32.exe

/Cheers
Emanuel

[sxavier]

Emanuel,

I am getting following error: No suitable driver found for jdbc:sql server://localhost\SQLEXPRESS/JIT3_TEST

I tried your solution by doing the following:
1. I included SQL ServerJ in the JDBCDrivers.conf file.
<Driver>
<Name>SQL ServerJ</Name>
<Class>com.microsoft.sqlserver.jdbc.SQLServerDriver</Class>
<Subprotocol>sql server</Subprotocol>
</Driver>

2. Since I did not have the JDBC drivers for SQL Server, I downloaded sqljdbc4.jar from msdn.
3. I copied the sqljdbc4.jar to C:\Program Files\Jitterbit Integration Server\tomcat\lib and C:\Program Files\Jitterbit Integration Server\tomcat\webapps\axis\WEB-INF\lib
4. Restarted all of the Jitterbit services.
5. In the Jitterbit DB Targets, I am able to select the SQLServerJ driver. But when I test the connection, it gives the error No suitable driver found for jdbc:sql server://localhost\SQLEXPRESS/JIT3_TEST

Thanks,
Suresh

[sxavier]

Emanuel,

I was able to get past the "No suitable driver" error by changing <Subprotocol>sql server</Subprotocol> to <Subprotocol>sqlserver</Subprotocol> in JDBCDrivers.conf

But the operation still fails with the same error I got with SQL Server ODBC drivers.

Error
Failed to write JDBC target. SQLException - INSERT INTO "address_t" ("address_id","addr") VALUES (,'123 ABC Blvd, AB, 11111')
Message - Incorrect syntax near ','.

Thanks,
Suresh

[emanuel]

You need to use a "manual" URL for this to (potentially) work. In the URL you have to say something like jdbc:sqlserver://localhost\SQLEXPRESS/JIT3_TEST (without the space) but in JdbcDrivers.conf you have to include the space. This issue has been fixed in the latest version.

/Emanuel

[sxavier]

The Jitterbit 3.0 enterprise edition (trial version) also has the same issue with identity columns in parent-child relationship.

SQLException - 'INSERT INTO "address_t" ("address_id","addr") VALUES (?,?)' Parameters = [, 123 ABC Blvd, AB, 11111] Message - Cannot insert explicit value for identity column in table 'address_t' when IDENTITY_INSERT is set to OFF.

Environment:
Jitterbit Server: 3.0.0.125
Jitterbit Client: 3.0.0.126
Database: SQL Server 2005
Driver: SQL Server JDBC (sqljdbc4)

Note: I uninstalled the community edition first before installing the enterprise edition.

Thanks,
Suresh

[emanuel]

That bug may be there in that version as well, try this preview of 3.1

http://www.jitterbit.com/Files/Builds/i ... -win32.exe
http://www.jitterbit.com/Files/Builds/i ... -win32.exe

/Cheers
Emanuel