Very Computer

Thanks

I'd need to see more about that "solution" to really comment on it. But if you execute
a proc which tries to violate entity integrity (insert duplicate), then it doesn't
matter how you try to execute that proc. You need to get the prow to not try to insert
duplicates.

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

Quote:> I have discovered that some of my stored procedures are failing on an insert
> operation due to a PK violation in SQL7.  I  think I saw a solution from
> Microsoft that said to execute the so from a Execute Process task instead of
> the execute sql task.  But I have not been able to figure out how to exec a
> sp form the execute Process task.  Any help would be appreciated

> Thanks

SET NOCOUNT ON

Declare @XferRecord             int,
        @XferDate               smallDateTime,
        @ItemID         varchar(38),
        @DealerCode             varchar(6),
        @CompanyNumber  varchar(3),
        @CompanyName    varchar(6),
        @Source         varchar(3),
        @Reference              varchar(10),
        @PostedDate             varchar(8),
        @Account                varchar(7),
        @Amount                 varchar(13),
        @ControlType            varchar(2),
        @Control                varchar(17),
        @Control2               varchar(17),
        @Seq                    varchar(8),
        @PostingDesc            varchar(132),
        @ins_error              int,
        @ReturnCode             int,
        @RecordsProcessed       int,
        @DuplicateRecords       int,
        @Severity               int,
        @Message                varchar(500),
        @HostName               varchar(30)

Select @ReturnCode = 0
Select @Severity = 0
Select @RecordsProcessed = 0
Select @DuplicateRecords = 0
Select @HostName = Host_Name()
Select TOP 1 @DealerCode = DealerCode from Temp_GlJeDtl where XferID = @XferID

EXEC sp_LogMessage 'sp_Copy_GlJeDtl', @Hostname, 'sp_Copy_GlJeDtl starting' , 0,
@XferID

-- Ignore Delete records

-- Update existing records
UPDATE v
SET v.XferRecord = t.XferRecord,
        v.XferID = @XferID,
        v.XferDate = t.XferDate,
        v.CompanyNumber = t.CompanyNumber,
        v.CompanyName = c.CompanyName,
        v.Source = t.Source,
        v.Reference = t.Reference,
        v.PostedDate = t.PostedDate,
        v.Account = t.Account,
        v.Amount = convert(money, t.Amount),
        v.ControlType = t.ControlType,
        v.Control = t.Control,
        v.Control2 = t.Control2,
        v.Seq = t.Seq

From GlJeDtl v, temp_GlJeDtl t, Companies c

Where v.DealerCode = t.DealerCode
        AND v.ItemID = t.ItemID
        AND t.XferID = @XferID
        AND t.ErrorCode NOT IN (SELECT Code FROM ErrorCodes WHERE Severity = 1)
        AND t.CrcStatus <> 'D'
        AND t.CompanyNumber = c.CompanyNumber

-- insert new records
DECLARE xfer_cursor  CURSOR
         FOR
         SELECT XferRecord, XferDate, ItemID, a.DealerCode, a.CompanyNumber,
b.CompanyName,
                Source, Reference, PostedDate,  Account, Amount, ControlType,
Control, Control2, Seq, PostingDesc
         From   Temp_GlJeDtl a, Companies b
         Where  XferID = @XferID
                AND CrcStatus <> 'D'
                AND ErrorCode NOT IN (SELECT Code FROM ErrorCodes WHERE Severity
= 1)
                AND a.CompanyNumber = b.CompanyNumber

Open xfer_cursor
Fetch xfer_cursor into  @XferRecord, @XferDate, @ItemID, @DealerCode,
@CompanyNumber, @CompanyName,
                @Source, @Reference, @PostedDate, @Account, @Amount,
@ControlType, @Control, @Control2, @Seq, @PostingDesc

While (@@fetch_status = 0)
    Begin

        Insert GlJeDtl
        ( XferID, XferRecord, XferDate, ItemID, DealerCode, CompanyNumber,
CompanyName,
                Source, Reference, PostedDate,  Account, Amount, ControlType,
Control, Control2, Seq, PostingDesc )
        Values
        ( @XferID, @XferRecord, @XferDate, @ItemID, @DealerCode, @CompanyNumber,
@CompanyName,
                @Source, @Reference, @PostedDate, @Account, convert
(money,@Amount), @ControlType, @Control, @Control2, @Seq, @PostingDesc )

        SELECT @ins_error = @@ERROR
        Select @RecordsProcessed = @RecordsProcessed + 1

       IF @ins_error <> 0
        BEGIN
           IF  @ins_error = 2627
                Begin
                    Select @DuplicateRecords = @DuplicateRecords + 1
                End
           Else
               BEGIN
                   Print 'Unknown Error has Occurred  -  ' + cast(@ins_error as
varchar(6))
                   Select @ReturnCode = 99
               END
        END

        Fetch xfer_cursor into  @XferRecord, @XferDate, @ItemID, @DealerCode,
@CompanyNumber, @CompanyName,
                @Source, @Reference, @PostedDate, @Account, @Amount,
@ControlType, @Control, @Control2, @Seq, @PostingDesc
    End

DEALLOCATE Xfer_cursor

-- Check the error for No Error or PK Violation

       IF @ins_error <> 0
        BEGIN
           IF  @ins_error <> 2627
               BEGIN
                   Print 'Unknown Error has Occurred  -  ' + cast(@ins_error as
varchar(6))
                   Select @ReturnCode = 99
  END
        END
        Select @Message = 'ReturnCode=' + Cast(@ReturnCode as varchar(7))
        Select @Message = @Message + '  Processed=' + Cast(@RecordsProcessed as
varchar(7))
        Select @Message = @Message + '  Updated=' + Cast(@DuplicateRecords as
varchar(7))
        Select @Message = @Message + '  Inserted=' + Cast(@RecordsProcessed -
@DuplicateRecords as varchar(7))
        Select @Message = @Message + '  XferID=' + Cast(@XferID as varchar(7))
        Select @Message = @Message + '  Dealer=' + Cast(@DealerCode as varchar(6))

        Exec sp_LogMessage 'sp_Copy_GlJeDtl', @HostName, @Message , @Severity,
@XferID
        If @DebugLevel > 0
        Begin
        Select @Message = 'sp_Copy_GlJeDtl  ' + @Message            
        Print @Message
        End

Return (@ReturnCode)

It has to do with the insert GlJeDtl line
the table I am getting the information from (Temp_GlJeDtl) can have as many as
20,000 items and I am attempting to insert the new items into a table that may
already have 600,000 items
the Primary key is the itemID

Thanks
Mike

Btw, do you really need a cursor? Looks like you can do an insert WHERE NOT EXISTS
instead? Perhaps it is the SELECT statement that SELECTs duplicated which you then try
to INSERT inside the cursor?

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.veryComputer.com/
Please reply to the newsgroup only, not by email.

here is the sp

AS

SET NOCOUNT ON

-- Ignore Delete records

-- Update existing records
UPDATE v
SET v.XferRecord = t.XferRecord,

v.XferDate = t.XferDate,
v.CompanyNumber = t.CompanyNumber,
v.CompanyName = c.CompanyName,
v.Source = t.Source,
v.Reference = t.Reference,
v.PostedDate = t.PostedDate,
v.Account = t.Account,
v.Amount = convert(money, t.Amount),
v.ControlType = t.ControlType,
v.Control = t.Control,
v.Control2 = t.Control2,
v.Seq = t.Seq

From GlJeDtl v, temp_GlJeDtl t, Companies c

Where v.DealerCode = t.DealerCode
AND v.ItemID = t.ItemID

AND t.ErrorCode NOT IN (SELECT Code FROM ErrorCodes WHERE Severity = 1)
AND t.CrcStatus <> 'D'
AND t.CompanyNumber = c.CompanyNumber

-- insert new records
DECLARE xfer_cursor  CURSOR
FOR
SELECT XferRecord, XferDate, ItemID, a.DealerCode, a.CompanyNumber,
b.CompanyName,
Source, Reference, PostedDate, Account, Amount, ControlType,
Control, Control2, Seq, PostingDesc
From Temp_GlJeDtl a, Companies b

AND CrcStatus <> 'D'
AND ErrorCode NOT IN (SELECT Code FROM ErrorCodes WHERE Severity
= 1)
AND a.CompanyNumber = b.CompanyNumber

Open xfer_cursor

    Begin

        Insert GlJeDtl
        ( XferID, XferRecord, XferDate, ItemID, DealerCode, CompanyNumber,
CompanyName,
Source, Reference, PostedDate, Account, Amount, ControlType,
Control, Control2, Seq, PostingDesc )
        Values

        BEGIN

                Begin

                End
           Else
               BEGIN

varchar(6))

               END
        END

    End

DEALLOCATE Xfer_cursor

-- Check the error for No Error or PK Violation

        BEGIN

               BEGIN

varchar(6))

  END
        END


varchar(7))

varchar(7))

        Begin


        End

It has to do with the insert GlJeDtl line
the table I am getting the information from (Temp_GlJeDtl) can have as many as
20,000 items and I am attempting to insert the new items into a table that may
already have 600,000 items
the Primary key is the itemID

Thanks
Mike