Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005

    Unanswered: Capture messages from extended procedure

    Hi all,

    I am using an extended stored procedure that sends email using SMTP. The procedure can be downloaded here. If I execute the procedure in QA and it fails for some reason, the error message is printed in the Messages tab. Is there any way to capture this message/messages when I use the XP in my stored procedures?

    Your help is much appreciated.

  2. #2
    Join Date
    Nov 2005

    Forgive me for bumping, I promise never to do it again, but it's important for me to find out if this is possible.

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Yes, it is possible to capture the "print vector" output of an extended stored procedure within a standard stored procedure, but it isn't simple.

    The easiest way is to use xp_cmdshell to execute OSQL.EXE and capture its output into a table. This is untested, but you'll get the idea:
    CREATE TABLE #foo (
      x VARCHAR(1024)
    INSERT INTO #foo (x)
       EXECUTE master.dbo.xp_cmdshell 'OSQL -E -Q"EXECUTE sp_who"'
    SELECT * FROM #foo
    My next choice would be to create a DTS package that uses Active-X script to do roughly the same thing. That's more complicated than I want to type right now, but it isn't too hard.

    There are other ways that break even more "best practices", but lets not go there unless we really have to!


  4. #4
    Join Date
    Nov 2005
    Thanks for your reply.

    It actually works !!! Perhaps it's just me, but don't like the idea to use xp_cmdshell.

    Anyway, thank you for you time and expertice.

Posting Permissions

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