Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: Failing a Stored Proc

    I have an SP that calls a DTS package. The DTS package checks for a certain condition and fails if that condition is not met. The DTS package properly reports the failure. However, the SP, when finished, reports success regardless of the outcome of the DTS package.

    How can I get the SP to report the correct outcome of the DTS package?

    The SP syntax is:

    Code:
      execute master.dbo.xp_cmdshell 'DTSRun /S MyServer /U MyUser /P MyPassword /N "My DTS Package" '
    Thanks in advance,

    Hugh Scott

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    consider a change to your code:

    Code:
    declare @rc int
    execute @rc = master.dbo.xp_cmdshell 'DTSRun /S MyServer /U MyUser /P MyPassword /N "My DTS Package" '
    if @rc != 0
       return -1
    this will either cause your sp to end if the DTS command fails OR you could carry the value of @rc to the end of your sp.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •