Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Otain error details from a DTS package through T-SQL. Was(xp_sendmail on script error

    SQL Server 2000

    Good day all,

    I've been asked to create a DTS package that will execute 2 other DTS packages (yeah, I know... personally I want to write a sp for it *shrug*) and send an e-mail out reporting a failure and failure details.

    So I have a couple of questions for you;
    1. How do I pick up the error number (and description?) from a failed DTS and pass it to my sendmail task?
    2. Is it possible to send anything other than plain text e-mails?
    3. Can I change the e-mail priority to "high"?

    I appreciate any help you can offer me
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    USE master
    GO
    
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'S' AND name = 'sp_dtsFailureEmail') BEGIN
    	DROP PROCEDURE sp_dtsFailureEmail
    END
    GO
    
    CREATE PROCEDURE sp_dtsFailureEmail
        @dtsName     varchar(64)
      , @errorNumber int = 0
    AS
    
      IF @errorNumber <> 0 BEGIN
        DECLARE @message    varchar(2000)
              , @subject    varchar(255)
              , @errorDesc  nvarchar(255)
    
        SET @errorDesc = (SELECT description FROM sysmessages WHERE error = @errorNumber)
        SET @subject = '***DTS FAILURE*** ' + @dtsname + ' ***DTS FAILURE***'
        SET @message = 'DTS Package:'  + Char(9) + Char(9) + @dtsName
          + Char(13) + 'Failure Date:' + Char(9) + Char(9) + Convert(varchar, GetDate(), 0)
          + Char(13) + 'Error Number:' + Char(9) + Char(9) + Convert(varchar, @errorNumber)
          + Char(13) + 'Error Description:'      + Char(9) + @errorDesc
    
        PRINT ''
        PRINT @subject
        PRINT ''
        PRINT @message
        PRINT ''
    
        EXEC xp_sendmail
             @recipients = '<enter your e-mail address>'
           , @subject    = @subject
           , @message    = @message
      END
      ELSE BEGIN
        PRINT 'No Error'
      END
    GO
    
    SELECT 1/0
    
    EXEC sp_dtsFailureEmail '<enter dtsName>', @@Error
    
    DROP PROCEDURE sp_dtsFailureEmail
    I'm getting close, but I still don't know how to pass the error number from a DTS to... anywhere!!
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    EXEC xp_cmdshell 'DTSRun /S "SQL-XXXX-XXXXX" /N "<dtsname>" /G "{5A14F80F-8EE2-477D-BE95-DC90A343D2A3}" /W "0" /E '
    SELECT @@Error
    Returns no error number when the DTS fails... So this suggests that this cannot be done!
    Any ideas?
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As you know I know stuff all about DTS but you are not getting much help here.

    1) Should you change the title of the thread? The problem does not really have anything to do with xp_sendmail.
    2) Have you tried:
    Code:
    Declare @error_return AS INT
    
    EXEC @error_return = xp_cmdshell 'DTSRun /S "SQL-XXXX-XXXXX" /N "<dtsname>" /G "{5A14F80F-8EE2-477D-BE95-DC90A343D2A3}" /W "0" /E ' SELECT @error_return
    ??
    3) Have you considered setting up a global variable, trapping the error in the DTS script and setting the global. Then test the global when the DTS script has finished.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    1) Wilco... my q2&3 were sp_sendmail though..

    2) I'm just working on that! It returns a bit value declaring whether there was an error or not.

    3) Never used global variables in DTS before - will look up if I run into a dead end with point 2.

    Here's my current working code.
    Code:
    USE master
    GO
    
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'S' AND name = 'sp_dtsFailureEmail') BEGIN
    	DROP PROCEDURE sp_dtsFailureEmail
    END
    GO
    
    CREATE PROCEDURE sp_dtsFailureEmail
        @dtsName     varchar(64)
    AS
    
        DECLARE @message   varchar(8000)
              , @subject   varchar(255)
              , @errorDesc nvarchar(255)
    
    --    SET @errorDesc = (SELECT description FROM sysmessages WHERE error = @errorNumber)
        SET @subject = '***DTS FAILURE*** ' + @dtsname + ' ***DTS FAILURE***'
        SET @message = 'DTS Package:'  + Char(9) + Char(9) + @dtsName
          + Char(13) + 'Failure Date:' + Char(9) + Char(9) + Convert(varchar, GetDate(), 0)
    --      + Char(13) + 'Error Number:' + Char(9) + Char(9) + Convert(varchar, @errorNumber)
    --      + Char(13) + 'Error Description:'      + Char(9) + @errorDesc
    
        PRINT ''
        PRINT @subject
        PRINT ''
        PRINT @message
        PRINT ''
    
    --    EXEC xp_sendmail
    --         @recipients = '<enter your email>'
    --       , @subject    = @subject
    --       , @message    = @message
     -- END
    GO
    
    DECLARE @err bit
    DECLARE @dts varchar(64)
    DECLARE @cmd varchar(1024)
    
    SET @dts = 'test'
    SET @cmd = 'DTSRun /S "SQL-LIVE-IT038" /N "' + @dts + '" /W "0" /E'
    
    EXEC @err = xp_cmdshell @cmd
    	IF @err <> 0 BEGIN
    		EXEC sp_dtsFailureEmail @dts
    	END
    
    DROP PROCEDURE sp_dtsFailureEmail
    Returns:
    DTSRun: Loading...
    DTSRun: Executing...
    DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
    DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14)
    Error string: Divide by zero error encountered.
    Error source: Microsoft OLE DB Provider for SQL Server
    Help file:
    Help context: 0

    Error Detail Records:

    Error: -2147217900 (80040E14); Provider Error: 8134 (1FC6)
    Error string: Divide by zero error encountered.
    Error source: Microsoft OLE DB Provider for SQL Server
    Help file:
    Help context: 0

    DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
    DTSRun: Package execution complete.
    NULL
    ***DTS FAILURE*** test ***DTS FAILURE***

    DTS Package: test
    Failure Date: Sep 5 2007 12:16PM
    This raises another question: Is it possible to retrieve the output information from xp_cmdshell?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    This raises another question: Is it possible to retrieve the output information from xp_cmdshell?
    You mean stuff printed to the window? No.

    How come? Are you hoping to get the details?

    As said I don't know DTS but can you get the details within the package? If so then you can write all this to a table and check that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Poots
    You mean stuff printed to the window?
    Yep.
    Quote Originally Posted by Poots
    How come? Are you hoping to get the details?
    Yep.

    Basically the aim is to e-mail a group of users with the error details of a DTS package. But I'm startign to think it's more trouble than it's worth
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    GeorgeV on DTS: Basically the aim is to e-mail a group of users with the error details of a DTS package. But I'm startign to think it's more trouble than it's worth

    Strong candidate for my new sig

    That, for me, is DTS through and through.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeeeeappp...
    I'd love to rewrite the whole script in T-SQL; but can't...
    1. I don't know how to do absolutely everything the DTS does
    2. It's classed as a "major change" and I really cba going through all that just to do it.

    A colleague of mine wrote a couple of hefty SSIS packages a while back which took a few CSV's from a 3rd party product and transformed them into our databases... All was fine until the column order changed on the CSV file. So even SSIS does the simplest of jobs badly.
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    A colleague of mine wrote a couple of hefty SSIS packages a while back which took a few CSV's from a 3rd party product and transformed them into our databases... All was fine until the column order changed on the CSV file. So even SSIS does the simplest of jobs badly.
    To be fair unless you write bullet proof dynamic sql or have some sort of config file T-SQL will fair no better. I imagine Pat would start muttering something about "contracts" upon reading this. You change the contract between two systems and you can't really expect things to run smoothly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually, to resolve this an interim DTS package was created which produces a "clean" file. DTS packages remember the mapping.

    The 3rd party product cost a total of $99 - we got what we paid for
    George
    Home | Blog

Posting Permissions

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