Exec sp from Execute Process task

Exec sp from Execute Process task

Post by Mike Donnell » Fri, 30 Mar 2001 12:22:53



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

 
 
 

Exec sp from Execute Process task

Post by Tibor Karasz » Fri, 30 Mar 2001 17:43:53


Mike,

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


 
 
 

Exec sp from Execute Process task

Post by Mike » Sat, 31 Mar 2001 01:42:37


here is the sp
CREATE PROCEDURE  sp_Copy_GlJeDtl
@XferID                 int,
@DebugLevel                     int
AS

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

-----Original Message-----
Mike,

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.

"Mike Donnelly" <donne...@landmarkchevrolet.com> wrote in message
news:usXNq9$tAHA.1904@tkmsftngp03...
> 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

.

 
 
 

Exec sp from Execute Process task

Post by Tibor Karasz » Sat, 31 Mar 2001 15:09:45


I really didn't mean the proc as is, but rather some simplified version easier too
read...
I suggest running the proc though a de* and see where the error is generated (it
is too early in the morning to for me to go though unknown code working against
unknown datamodel :-).

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

-----Original Message-----
Mike,

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.veryComputer.com/
Please reply to the newsgroup only, not by email.



> 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

.

 
 
 

1. Exec sp from Execute Process task

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

2. Connecting to secure Access db with ADO+SHAPE

3. Execute Package Task - Exec Result Status

4. US-Atlanta : INGRES + WIN 4GL : perm/consulting

5. exec SP weekly (as a task)

6. sp_spaceused for DB Vs. sp_spaceused <table_name> disparity

7. Execute SQL task (exec my_sp) pb, DTS stops without error

8. MERGING Workbooks using VB6 - help!

9. CMD EXEC task - process Exit code

10. Exec Process task error

11. EXEC an SP returns DB-Library Process Dead - Connection Broken

12. app role has exec permission but still can't execute sp

13. Execute Process Task gives error