CA Service Management

 View Only
  • 1.  17.2 archiving SLCM Requests does not work

    Posted Jun 13, 2019 10:09 AM
    Hi,
    when we try to archive closed Requests in our dev-env we get an error.
    Any idea what to look for?
    SQL-Statement:

    USE mdb

    GO

    DECLARE             @return_value int

    EXEC      @return_value = [dbo].[usm_sp_archive_data]

                                   @p_object_type = N'Request',

                                   @p_date = N'2019-06-11',

                                   @p_bu = N'xxxx'

    SELECT  'Return Value' = @return_value

    GO

    Following error occurs:

    INFO:started archiving request object

    ERROR:Error while archiving Der Spaltenname oder die Anzahl der bereitgestellten Werte entspricht nicht der Tabellendefinition.

    Meldung 3903, Ebene 16, Status 1, Prozedur usm_sp_archive_data, Zeile 42

    Die ROLLBACK TRANSACTION-Anforderung hat keine entsprechende BEGIN TRANSACTION-Anweisung.

     (1 Zeile(n) betroffen)

    ERROR:Error while archiving The column name or the number of values provided does not correspond to the table definition.
    Message 3903, Level 16, Status 1, Procedure usm_sp_archive_data, Line 42
    The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION statement.
    (1 line(s) affected)


    What can we check?

    Regards,
    Peter



    ------------------------------
    Senior Technical Consultant
    Fujitsu Services
    ------------------------------


  • 2.  RE: 17.2 archiving SLCM Requests does not work

    Posted Jun 17, 2019 03:27 AM
    Take a look if these Knowledge Articles helps

    https://ca-broadcom.wolkenservicedesk.com/kb/not-able-to-run-the-usm-sp-archive-data/KB000108935
    https://ca-broadcom.wolkenservicedesk.com/kb/usm-sp-archive-data-does-not-archive-any-data/KB000108937



  • 3.  RE: 17.2 archiving SLCM Requests does not work

    Posted Jun 17, 2019 04:24 AM
    Hi,
    in articel KB000108935 the solution has been to recreat usm2request table.
    Do you know the HowTo for recreating usm2request table?
    Is there any sql-script to be started?

    Thanks and Regards,
    Peter

    ------------------------------
    Senior Technical Consultant
    Fujitsu Services
    ------------------------------



  • 4.  RE: 17.2 archiving SLCM Requests does not work

    Broadcom Employee
    Posted Jun 18, 2019 07:49 AM

    Hi Peter,

    Any missing columns can be added individually. The following knowledge article may help:  https://ca-broadcom.wolkenservicedesk.com/kb/archive-and-purge-script-failing-to-execute/kb000102740

    If it also may help for checking on the expected column names, in my 17.1 environment, MS SQL Server Management Studio gives the following results when selecting "Script Table as" > CREATE To > New Query Editor Window :

    CREATE TABLE [dbo].[usm2request](

    [request_id] [int] NOT NULL,

    [name] [nvarchar](128) NOT NULL,

    [status] [int] NOT NULL,

    [created_date] [datetime] NOT NULL,

    [modified_date] [datetime] NOT NULL,

    [completion_date] [datetime] NULL,

    [desired_date] [datetime] NULL,

    [comments] [ntext] NULL,

    [priority] [int] NULL,

    [req_for_account_id] [varchar](50) NULL,

    [req_by_account_id] [varchar](50) NULL,

    [req_for_user_id] [nvarchar](100) NULL,

    [req_by_user_id] [nvarchar](100) NULL,

    [code] [nvarchar](64) NULL,

    [domain] [varchar](50) NULL,

    [location_uuid] [binary](16) NULL,

    [context_type] [int] NOT NULL,

    [source] [tinyint] NULL,

    [locale] [varchar](50) NULL,

    [req_for_uuid] [binary](16) NULL,

    [req_by_uuid] [binary](16) NULL, 

    CONSTRAINT [XPKusm2request] PRIMARY KEY CLUSTERED ( [request_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Regards,
    Karen



    ------------------------------
    Broadcom
    ------------------------------



  • 5.  RE: 17.2 archiving SLCM Requests does not work
    Best Answer

    Broadcom Employee
    Posted Jun 18, 2019 07:50 AM

    Hi Peter,

    Any missing columns can be added individually. The following knowledge article may help:  https://ca-broadcom.wolkenservicedesk.com/kb/archive-and-purge-script-failing-to-execute/kb000102740

    If it also may help for checking on the expected column names, in my 17.1 environment, MS SQL Server Management Studio gives the following results when selecting "Script Table as" > CREATE To > New Query Editor Window :

    CREATE TABLE [dbo].[usm2request](

    [request_id] [int] NOT NULL,

    [name] [nvarchar](128) NOT NULL,

    [status] [int] NOT NULL,

    [created_date] [datetime] NOT NULL,

    [modified_date] [datetime] NOT NULL,

    [completion_date] [datetime] NULL,

    [desired_date] [datetime] NULL,

    [comments] [ntext] NULL,

    [priority] [int] NULL,

    [req_for_account_id] [varchar](50) NULL,

    [req_by_account_id] [varchar](50) NULL,

    [req_for_user_id] [nvarchar](100) NULL,

    [req_by_user_id] [nvarchar](100) NULL,

    [code] [nvarchar](64) NULL,

    [domain] [varchar](50) NULL,

    [location_uuid] [binary](16) NULL,

    [context_type] [int] NOT NULL,

    [source] [tinyint] NULL,

    [locale] [varchar](50) NULL,

    [req_for_uuid] [binary](16) NULL,

    [req_by_uuid] [binary](16) NULL, 

    CONSTRAINT [XPKusm2request] PRIMARY KEY CLUSTERED ( [request_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Regards,
    Karen



    ------------------------------
    Broadcom
    ------------------------------



  • 6.  RE: 17.2 archiving SLCM Requests does not work

    Broadcom Employee
    Posted Jun 18, 2019 07:50 AM
    https://ca-broadcom.wolkenservicedesk.com/kb/archive-and-purge-script-failing-to-execute/kb000102740

    ------------------------------
    Broadcom
    ------------------------------



  • 7.  RE: 17.2 archiving SLCM Requests does not work

    Posted Jun 18, 2019 08:23 AM
    Hi Karen,
    many thanks. I'll check it being back on customers site at 1st of July.

    Regards,
    Peter

    ------------------------------
    Senior Technical Consultant
    Fujitsu Services
    ------------------------------



  • 8.  RE: 17.2 archiving SLCM Requests does not work

    Posted Jul 01, 2019 11:10 AM
    Hi Karen,

    with the help of the knowledge article you mentioned we were able to fix the issue.

    The following two entries were missing at the database:
    ALTER TABLE usm2request_pending_action ADD sequence_id int;
    ALTER TABLE usm2request_item_form ADD is_visible int;

    After adding these the archiving works fine.

    Many thanks,
    Thomas


  • 9.  RE: 17.2 archiving SLCM Requests does not work

    Broadcom Employee
    Posted Jul 02, 2019 03:32 AM
    Hi Thomas,  
    Thank you for the good news!
    Regards,
    Karen