Target not available, Error when map is build

Formally known as "Mercator Inside Integrator 6.7", DataStage TX enables high-volume, complex transactions without the need for additional coding.

Moderators: chulett, rschirm

Post Reply
maddy342
Participant
Posts: 1
Joined: Mon Apr 12, 2010 10:09 pm

Target not available, Error when map is build

Post by maddy342 »

Hi
I am new to DSTX and I am trying to run a map that takes data from a text file and writes it to a database table. I created the output card type tree from a table in the .mdq file. I am picking SQL Server Database as the target, I have used -T command for trace, I have tried several options w/o success. I have filled out the DatabaseQueryFile section with .mdq file, database name and table name. When i build the map it says target not available. And below is the trace ,please help.And let me know what is the Error ..Thanks

Validating the adapter command...
<5120-4380>: Database type is MS SQL Server 7
<5120-3920>: Connecting...
<5120-3920>: Datalink: SQL004\\DEV1
<5120-3920>: UserId : sa
<5120-3920>: Password: *****
<5120-3920>: Context: Output Card, Transaction scope: Map
<5120-3920>: On Failure: Rollback, On Success: Create
<5120-3920>: TRACE command specified, file: C:\Documents and Settings\paul01\Desktop\29yyy\M_S_DB.dbl
<5120-3920>: Table name: TABLE_THREE
<5120-3920>: Local transaction usage: Transaction ID 0x01546248
<5120-3920>: Transaction started - ISOLATIONLEVEL_READCOMMITTED
<5120-3920>: Connection to SQL Server SQL004 has been established.
<5120-3920>: Retrieving 1 rows per fetch.
<5120-3920>: Returned status: (0) Success
<5120-3920>: Interface library version 8.1(38)
<5120-3920>: Map: C:\Documents and Settings\paul01\Desktop\29yyy\M_S_DB.dbl.mmc, Timestamp: 04/09/09 10:42:12.
<5120-3920>: Loading data for output card 1.
<5120-3920>: Database adapter: OLE DB / SQL7 Version 8.1(38)
<5120-3920>: Starting database load to table TABLE_THREE...
<5120-3920>: Datalink: SQL004\\DEV1
<5120-3920>: UserId : sa
<5120-3920>: Password: *****
<5120-3920>: Context: Output Card, Transaction scope: Map
<5120-3920>: On Failure: Rollback, On Success: Create
<5120-3920>: TRACE command specified, file: C:\Documents and Settings\paul01\Desktop\29yyy\M_S_DB.dbl
<5120-3920>: Table name: TABLE_THREE
<5120-3920>: Update mode is off.
<5120-3920>: Retrieving 1 rows per fetch.
<5120-3920>: The columns are of the following types:
<5120-3920>: Column 1 (IN_ID) type is int [DBTYPE_I4].
<5120-3920>: Column 2 (UN_DT) type is datetime [DBTYPE_DBTIMESTAMP].
<5120-3920>: Column 3 (TN_ID) type is varchar(6) [DBTYPE_STR].
<5120-3920>: Column 4 (A_B) type is int [DBTYPE_I4].
<5120-3920>: Column 5 (C_D) type is int [DBTYPE_I4].
<5120-3920>: Column 6 (E_X) type is int [DBTYPE_I4].
<5120-3920>: Column 7 (B_T) type is int [DBTYPE_I4].
<5120-3920>: Column 8 (P_F) type is int [DBTYPE_I4].
<5120-3920>: Column 9 (A_T) type is int [DBTYPE_I4].
<5120-3920>: Column 10 (R_O) type is int [DBTYPE_I4].
<5120-3920>: Column 11 (L_P) type is int [DBTYPE_I4].
<5120-3920>: Column 12 (R_I) type is int [DBTYPE_I4].
<5120-3920>: Column 13 (T_I) type is int [DBTYPE_I4].
<5120-3920>: Column 14 (CNT) type is int [DBTYPE_I4].
<5120-3920>: Column 15 (CV_DT) type is datetime [DBTYPE_DBTIMESTAMP].
<5120-3920>: The insert statement to be executed is:
<5120-3920>: INSERT INTO TABLE_THREE VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
<5120-3920>: OLE DB Error code: 0x80040e2f
<5120-3920>: The statement has been terminated.
<5120-3920>: The following values were being inserted:
<5120-3920>: Column 1 IN_ID : 1
<5120-3920>: Column 2 UN_DT : 2009-2-21 00:00:00.000
<5120-3920>: Column 3 TN_ID : ABCDS
<5120-3920>: Column 4 (A_B) : NULL
<5120-3920>: Column 5 (C_D) : NULL
<5120-3920>: Column 6 (E_X) : NULL
<5120-3920>: Column 7 (B_T) : NULL
<5120-3920>: Column 8 (P_F) : NULL
<5120-3920>: Column 9 (A_T) : NULL
<5120-3920>: Column 10 (R_O) : NULL
<5120-3920>: Column 11 (L_P) : NULL
<5120-3920>: Column 12 (R_I) : NULL
<5120-3920>: Column 13 (T_I) : NULL
<5120-3920>: Column 14 (CNT) : NULL
<5120-3920>: Column 15 (CV_DT) : 2009-12-20
<5120-3920>: Failed to insert a row (rc = -6).
<5120-3920>: Failed after 1 rows inserted.
<5120-3920>: Database load complete.
<5120-3920>: Returned status: (-6) No error text found
<5120-3920>: Cleaning up and closing the transaction...
<5120-3920>: Returned status: (0) Success
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Not a TX nor SQL Server person, but if you google for that OLE DB error it looks to be a unique index / primary key duplicate value issue.
-craig

"You can never have too many knives" -- Logan Nine Fingers
rep
Participant
Posts: 82
Joined: Tue Jun 19, 2007 8:04 am
Location: New York City

Post by rep »

<5120-3920>: INSERT INTO TABLE_THREE VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
<5120-3920>: OLE DB Error code: 0x80040e2f
<5120-3920>: The statement has been terminated.
<5120-3920>: The following values were being inserted:



It has been a very long time since I've connected to a DB. Years, litteraly.

From what I recall, you obviously have to have access to the table. You know you have valid access while in the DB interface designer, when you click on a table an it allows you to build the type tree (the window comes up for "generate type tree from table").

Once you select the table and can access it, you must "set update keys". Include all the fields that are your keys in the top right window, and all the fields you want to update in the bottom window. In the event you have did NOT select a field you want to update, maybe that's causing the error. Once you set those keys and fields PROPERLY, you build the type tree. If you made a mistake, delete the type tree, make sure you set the fields properly, then re-build it.

My suggestion to test is to not use the text file from the get go; just have one output card with the DB, and try to insert a record by values you set in the map ( the rule for field1 is ="test1"). You have to put " -UPDATE" with the "-T" on the DB command line.

This looks wierd to me, but I really do not know exactly what wrong, but from what I recall (and really, I may be wrong), it will show the data you're trying to put in the fields. I see only see question marks.

<5120-3920>: INSERT INTO TABLE_THREE VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
<5120-3920>: OLE DB Error code: 0x80040e2f
<5120-3920>: The statement has been terminated.
<5120-3920>: The following values were being inserted:
Post Reply