Is there a way to load new data into Attachment TABLE using the operating system command line.

Article Id: 51198
Status: Published
Updated On: 21-03-2018 06:14
Legacy Id: TEC525712
Products:
CA IT Asset Manager , CA Software Asset Manager (CA SAM) , ASSET PORTFOLIO MGMT- SERVER , SUPPORT AUTOMATION- SERVER , CA Service Desk Manager - Unified Self Service , KNOWLEDGE TOOLS , CA Service Management - Asset Portfolio Management , CA Service Management - Service Desk Manager
Issue/Introduction:

Description:

How do I load data into Attachment TABLE using command line to import all files from a previous USD version?

Solution:

While Trying to insert Rows in Attachment table (attmnt) pdm_load / pdm_user load fails:

For example if the following had been done it would fail in the same way

Step 1: Sample Input File:

TABLE Attachment
description del orig_file_name attmnt_name file_name folder_id folder_path_id
s rel_file_path zip_flag repository status link_only
{"attmnt:400084","0","error.doc","error.doc","413251_error.doc","0","0","rep_
folder_00001","1","doc_rep:400001","INSTALLED","0"}

Step 2: The operating system prompt command to insert the data. 

   pdm_load -f input.txt -i

Step 3: Error message from above command

Error 1 on update, insert or delete done.
Error 16 on inserting row.
The inserted row conflicts with existing data.

The excerpt from Stdlog file:-
*******************

10/06 16:01:38.11 <Servername> sql_agent 5292 ERROR sqlclass.c 977 SQL Execute failed: [Microsoft OLE DB Provider for SQL
Server] [ SQL Code=3621 SQL State=01000] The statement has been terminated.; [Microsoft OLE DB Provider for SQL Server] [ SQL Code=2601 SQL State=23000]
Cannot insert duplicate key row in object 'dbo.attmnt' with unique index 'attmnt_x10'.

Explanation:
*********

Inserting into the Attachment table fails due to addition Column attachment_uuid in R12.1 MDB, This requires an UUID id which was missing from the Input file. However the same is not the case in r11.2 The above functionality works fine on Migrated box from (R11.2 to r12.1).

Workaround:-
**********

Important! Using pdm_load can be destructive so always back up your database before you perform a pdm_load, and use pdm_userload unless instructed to use pdm_load.

  1. Open a command prompt, execute following command from the operating system

    pdm_perl -e "print PDM::GetUUIDValue;"

    which will generate uuid on the command prompt.

    output: 918F43E6EC70744D830FE334A42ECEF9

  2. Copy the generated uuid, and place it in the attmnt_uuid column in the file which you want to load in the data. 
    TABLE Attachment
    description del orig_file_name attmnt_name file_name folder_id folder_path_id
    s rel_file_path zip_flag repository status link_only attmnt_uuid
   
    {"attmnt:400084","0","error.doc","error.doc","413251_error.doc","0","0","rep_
    folder_00001","1","doc_rep:400001","INSTALLED","0","918F43E6EC70744D830FE334A42ECEF9"}

Environment:

Release:
Component: ARGIS