Originally posted by: Kepa_Screen

Hi:

I'm making a map using WSTX 8.1.0.3 that reads data from an Oracle 10g database and puts data at a Sql Server 2008 database.

I was able to connect to the database using the Database Interface Designer, and able to create a .mtt for my target table dbo.COM_INBOX, at EROBOX database

<7140-6524>: Validating the adapter command...
<7140-6524>: Database type is MS SQL Server 7
<7140-6916>: Connecting...
<7140-6916>: Datalink: 10.56.255.100\\EROBOX
<7140-6916>: UserId : eroski
<7140-6916>: Password: *****
<7140-6916>: Context: Output Card, Transaction scope: Map
<7140-6916>: On Failure: Rollback, On Success: Create
<7140-6916>: AUDIT command specified, file: c:\audit.txt
<7140-6916>: TRACE command specified, file: c:\trace.txt
<7140-6916>: Table name: dbo.COM_INBOX
<7140-6916>: Local transaction usage: Transaction ID 0x05766228
<7140-6916>: Transaction started - ISOLATIONLEVEL_READCOMMITTED
<7140-6916>: Connection to SQL Server 10.56.255.100 has been established.
<7140-6916>: Retrieving 1 rows per fetch.
<7140-6916>: Returned status: (0) Success
<7140-6916>: Interface library version 8.1(38)
<7140-6916>: Map: C:\Documents and Settings\S8991\Mis documentos\Oracle_TO_SqlServer.mmc, Timestamp: 10/07/11 16:08:07.
<7140-6916>: Loading data for output card 1.
<7140-6916>: Database adapter: OLE DB / SQL7 Version 8.1(38)
<7140-6916>: Starting database load to table dbo.COM_INBOX...
<7140-6916>: Datalink: 10.56.255.100\\EROBOX
<7140-6916>: UserId : eroski
<7140-6916>: Password: *****
<7140-6916>: Context: Output Card, Transaction scope: Map
<7140-6916>: On Failure: Rollback, On Success: Create
<7140-6916>: AUDIT command specified, file: c:\audit.txt
<7140-6916>: TRACE command specified, file: c:\trace.txt
<7140-6916>: Table name: dbo.COM_INBOX
<7140-6916>: Update mode is off.
<7140-6916>: Retrieving 1 rows per fetch.
<7140-6916>: The columns are of the following types:
<7140-6916>: Column 1 (SECUENCIA) type is int DBTYPE_I4.
<7140-6916>: Column 2 (COD_INT) type is smallint DBTYPE_I2.
<7140-6916>: Column 3 (CONTENIDO) type is ntext DBTYPE_WSTR.
<7140-6916>: Column 4 (FLG_ESTADO) type is varchar(1) DBTYPE_STR.
<7140-6916>: Column 5 (COD_ERROR) type is smallint DBTYPE_I2.
<7140-6916>: Column 6 (MSG_ERROR) type is varchar(2000) DBTYPE_STR.
<7140-6916>: Column 7 (FEC_EMISION) type is datetime DBTYPE_DBTIMESTAMP.
<7140-6916>: Column 8 (FEC_TRATO) type is datetime DBTYPE_DBTIMESTAMP.
<7140-6916>: Column 9 (MSGID) type is varchar(32) DBTYPE_STR.
<7140-6916>: Column 10 (ORIGEN) type is varchar(100) DBTYPE_STR.
<7140-6916>: Column 11 (COD_APLICACION) type is int DBTYPE_I4.
<7140-6916>: Column 12 (USUARIO) type is varchar(200) DBTYPE_STR.
<7140-6916>: Column 13 (PROCESO) type is varchar(200) DBTYPE_STR.
<7140-6916>: Column 14 (ZIP_CONTENIDO) type is image DBTYPE_BYTES.
<7140-6916>: The insert statement to be executed is:
<7140-6916>: INSERT INTO dbo.COM_INBOX VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
<7140-6916>: OLE DB Error code: 0x80040e2f
<7140-6916>: Se terminó la instrucción.
<7140-6916>: The following values were being inserted:
<7140-6916>: Column 1 SECUENCIA : 12002
<7140-6916>: Column 2 COD_INT : 1000
<7140-6916>: Column 3 CONTENIDO : NULL
<7140-6916>: Column 4 FLG_ESTADO : P
<7140-6916>: Column 5 COD_ERROR : NULL
<7140-6916>: Column 6 MSG_ERROR : NULL
<7140-6916>: Column 7 FEC_EMISION : 2011-10-07
<7140-6916>: Column 8 FEC_TRATO : NULL
<7140-6916>: Column 9 MSGID : 12351
<7140-6916>: Column 10 ORIGEN : DLOGAPL
<7140-6916>: Column 11 COD_APLICACION : NULL
<7140-6916>: Column 12 USUARIO : NULL
<7140-6916>: Column 13 PROCESO : NULL
<7140-6916>: Column 14 ZIP_CONTENIDO : binary data length 94
<7140-6916>: Failed to insert a row (rc = -6).
<7140-6916>: Failed after 0 rows inserted.
<7140-6916>: Database load complete.
<7140-6916>: Returned status: (-6) No error text found
<7140-6916>: Cleaning up and closing the transaction...
<7140-6916>: Returned status: (0) Success
Could anyone give some light ¿?

I'm using PUT command :

-SOURCE 10.56.255.100\\EROBOX -DBTYPE SQLSVR7 -USER eroski -PASSWORD xxxxxx -TABLE dbo.COM_INBOX -AUDIT c:\audit.txt -TRACE c:\trace.txt

Also tried using a .mdq file, but it gives me database error

<TargetReport card="1" adapter="DB" bytes="13488" adapterreturn="-6">
<Message>No error text found</Message>
</TargetReport>

User has grants insert, update, select, and delete at SQLServer table....

Thanks in advance
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: TarunB

Check that you're not inserting a NULL into a DB field which is set to not be nullable.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: Kepa_Screen

Hi, i've found the problem, it was a duplicate value for the primary key.
The error log is not as good as it could be. Really not saying too much to me with that codes.

Now we have another problem. The map is working ok at winXP in development, but not working at aix machine, failed to load a shared library. ( SqlServer client one ).

Do you know what files do i need to install to <install_dir>/libs ? ( working with sqlServer 10.50.1600.1 )
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: Kepa_Screen

Solved using ODBC . Thanks !!
/home/entornos/integracion/itf/exec/mercator/mmc -> more traza.txt
<59844-001>: Validating the adapter command...
<59844-001>: Database type is ODBC
<59844-515>: Connecting...
<59844-515>: Datasource : CEBI_SQLSERVER
<59844-515>: UserID : eroski
<59844-515>: Password : *****
<59844-515>: Context: Output Card, Transaction scope: Map
<59844-515>: On Failure: Rollback, On Success: Create
<59844-515>: AUDIT command specified, file: audit.txt
<59844-515>: TRACE command specified, file: /home/entornos/integracion/itf/exec/mercator/mmc/traza.txt
<59844-515>: Table name: EROBOX.dbo.COM_INBOX
<59844-515>: SQLConnect succeeded with info:
<59844-515>: Connection to datasource CEBI_SQLSERVER has been established.
<59844-515>: Servername/alias:->
<59844-515>: Microsoft SQL Server
<59844-515>: Database Version/Type:->
<59844-515>: 10.50.1600
<59844-515>: Client Library/environment(ODBC DriverName):->
<59844-515>: VMmsss23.so
<59844-515>: Client Library version (ODBC DriverVersion):->
<59844-515>: 05.30.0161 (b0115, U0082)
<59844-515>: Returned status: (0) Success
<59844-515>: Interface library version 8.1(37)
<59844-515>: Map: /home/entornos/integracion/itf/exec/mercator/mmc/pruebas.mmc, Timestamp: Tue Oct 18 17:12:04 2011.
<59844-515>: Loading data for output card 1.
<59844-515>: Database adapter: ODBC Version 8.1(37)
<59844-515>: Starting database load to table EROBOX.dbo.COM_INBOX...
<59844-515>: Datasource : CEBI_SQLSERVER
<59844-515>: UserID : eroski
<59844-515>: Password : *****
<59844-515>: Context: Output Card, Transaction scope: Map
<59844-515>: On Failure: Rollback, On Success: Create
<59844-515>: AUDIT command specified, file: audit.txt
<59844-515>: TRACE command specified, file: /home/entornos/integracion/itf/exec/mercator/mmc/traza.txt
<59844-515>: Table name: EROBOX.dbo.COM_INBOX
<59844-515>: Update mode is off.
<59844-515>: The columns are of the following types:
<59844-515>: Column 1 (SECUENCIA) type is INTEGER, precision is 10, auto-increment, non-updateable.
<59844-515>: Column 2 (COD_INT) type is SMALLINT, precision is 5.
<59844-515>: Column 3 (CONTENIDO) type is UNICODE_LONGVARCHAR, precision is 1073741823.
<59844-515>: Column 4 (FLG_ESTADO) type is VARCHAR, precision is 1.
<59844-515>: Column 5 (COD_ERROR) type is SMALLINT, precision is 5.
<59844-515>: Column 6 (MSG_ERROR) type is VARCHAR, precision is 2000.
<59844-515>: Column 7 (FEC_EMISION) type is TIMESTAMP, precision is 23.
<59844-515>: Column 8 (FEC_TRATO) type is TIMESTAMP, precision is 23.
<59844-515>: Column 9 (MSGID) type is VARCHAR, precision is 32.
<59844-515>: Column 10 (ORIGEN) type is VARCHAR, precision is 100.
<59844-515>: Column 11 (COD_APLICACION) type is INTEGER, precision is 10.
<59844-515>: Column 12 (USUARIO) type is VARCHAR, precision is 200.
<59844-515>: Column 13 (PROCESO) type is VARCHAR, precision is 200.
<59844-515>: Column 14 (ZIP_CONTENIDO) type is LONGVARBINARY, precision is 2147483647.
<59844-515>: The insert statement to be executed is:
<59844-515>: INSERT INTO EROBOX.dbo.COM_INBOX (COD_INT,CONTENIDO,FLG_ESTADO,COD_ERROR,MSG_ERROR,FEC_EMISION,FEC_TRATO,MSGI
D,ORIGEN,COD_APLICACION,USUARIO,PROCESO,ZIP_CONTENIDO) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
<59844-515>: 319 rows inserted.
<59844-515>: Database load complete.
<59844-515>: Returned status: (0) Success
<59844-515>: Cleaning up and closing the transaction...
<59844-515>: Transaction rollback succeeded.
<59844-515>: Returned status: (0) Success
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender