Error in DTS Package

  • Hi All,

    Good Morning.

    I am getting following error in one of the DTS package.

    *****************************

    DTSRun OnError:  DTSStep_DTSExecuteSQLTask_1, Error = -2147217873 (80040E2F)

       Error string:  Cannot insert duplicate key row in object 'sysindexes' with unique index 'sysindexes'.

       Error source:  Microsoft OLE DB Provider for SQL Server

       Help file: 

       Help context:  0

     

    Error Detail Records:

     

    Error:  -2147217873 (80040E2F); Provider Error:  2601 (A29)

       Error string:  Cannot insert duplicate key row in object 'sysindexes' with unique index 'sysindexes'.

       Error source:  Microsoft OLE DB Provider for SQL Server

       Help file: 

       Help context:  0

     

    DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1

    *****************************

     

    I am calling the package in a Job. Package is updating on of the tables and in the next SQL task.. it is checking for the table existense and Droping and recreating the table.

    I am not able to reproduce the same. When i executed the same next time it is successful. Please help me if u have any soulution for this.

    /***Code inside the SQL task : Job failing at this step and table is not created***/

    IF OBJECT_ID('[dbo].[TableName_OneTime ]') > 0

       DROP TABLE [dbo].[TableName_OneTime ]

    GO

    CREATE TABLE [dbo].[TableName_OneTime ] (

    )

    GO

  • Hi Ramesh,

    This sounds like a timing issue - I seem to remember having the same problem once.

    Sometimes it works sometimes it doesn't - dependant on wether the entry in sysindexes has been updated / deleted before trying to re-create the table.

    Try explicitly wrapping transactions around the "IF OBJECT_ID('[dbo].[TableName_OneTime ]') > 0

       DROP TABLE [dbo].[TableName_OneTime ]" bit of your code. (BEGIN AND COMMIT TRANSACTION)

    Have fun

    Steve

    We need men who can dream of things that never were.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply