Capturing The Error Description In A Stored Procedure

  • Comments posted to this topic are about the content posted at

  • Great Article Joseph!

    This is even more interesting when you consider the new features in the "soon to be revamped" DTS in Yukon. I read that you will be able to use looping structures so using this types of packages you will be able to put all you data into a spreadsheet in one fell swoop.

    I'm wondering whether Microsoft will release a separated module with the new DTS for download until we can actually start using it when the product is released together SQL Server 2005 like they did with other features (enhanced XML, Reporting Services). But if the change is "that big" maybe they wont be able to release separately.

    Keep writing this great DTS articles... I'm looking forward to see more of them

  • Enjoyed your article Joseph.

    FYI, the page title that appears in the window title bar is:

    "Capturing The Error Description In A Stored Procedure"

  • "There is a limitation in the Excel driver that effects the 'DELETE' keyword. If you attempt to use it, you will receive the error message, "Deleting data in a linked table is not supported by this ISAM". Instead, we remove the Excel table entirely with the DROP TABLE 'authors' command."

    That is so clever!  For years, I have been using an Excel template and the FSO to overcome the delete failure.  Your solution is so simple it's elegant!  Bravo!

    [font="Courier New"]ZenDada[/font]

  • I had som similar problems with Excel-reports a while back, but my solution was to create a template of the excelfile and in the beginning of the DTS, I deleted the previous report and copied my fresh template.

    exec master.dbo.xp_cmdshell 'del excelfile.xls'

    exec master.dbo.xp_cmdshell 'copy excelfile_template.xls excelfile.xls'

    The great thing with this option is that you can create some basic formatting on the template if your report require that.

  • Very much akin to discussion

    however, this version is much more visually comprehensive.

    This article that should help a lot of people as it appears to have been a very common question over the years.

  • First - great article!

    Minor issue with implementation - maybe somebody has experienced this too? When the data is posted from my query into the excel sheet, it doesn't start posting the data on Row 2 (Row 1 has column headers). Instead it starts posting the data down on row 458.

    Any ideas? This is screwing up my pivot table that is using that data sheet.

    Thanks for your help!

  • Very nice Article!

    I got an error on implementation. The first task of dropping the table, did not work for me. It says error occurred in drop table or drop index. I removed the first task and tried with the remaining tasks. the transformation task works fine but the execute ActiveX control task gives me an error saying that the 'SendUsing' has a configuration value that is invalid. I dont konw what it means. Can anybody help me???





  • Regarding the dropping the table task: Make sure that you use the slanted quotes around the table name, not just the regular single quotes. They seem to be necessary. You can copy them from the CREATE TABLE statement. See if that helps.

  • Two thinsg to note:


    1) I too had trouble with the DROP Table statement until I used the slanted quotes.  I accidentally discovered that I could also use Brackets to get it to work like this:

    DROP TABLE [Authors]


    2) I can;t get the ActiveX code to work with the objMail.AddAttachment line.  If I remove that line then the code works but when that line is there I get the error that the object doesn't support this property or method.  ANy ideas?



    Kindest Regards,

    Just say No to Facebook!
  • Hi Joseph

    U r article is excellent!!!...

    A small problem i noticed it, though it is working successfully, data getting appended with old data, so in effect SQL Excecute task is not dropping the table..

    can anybody tell me why and how I can solve it?

    Thanks in advance

    Agson C A


  • My post has nothing to do with capturing the error description, but after ten minutes of searching for a 'new thread' button I give up.

    What I am looking for is more leeway in formatting the email. In sqlmail I am able to insert cr's and lf's, but the same syntax doesn't work in the activex mail.

    Can someone point me in the right direction to research this further?





  • I kept getting a syntax error with my Execute SQL Task:

    DROP TABLE 'contacts'


    Finally I removed the single quotes and it worked:

    DROP TABLE contacts



  • Excellent article, thanks.  I've discovered that exporting to a .csv you don't need to delete the file/contents of the file first, a .csv file retains the structure from the first time you run the package and just gets new data on each subsequent run. Does that make sense?

    Growing old is mandatory, growing up is optional

  • Yes, it does append the data unless you delete and recreate the CSV file.

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply