Capturing The Error Description In A Stored Procedure

  • Willie Bodger

    Newbie

    Points: 6

    So, I set it up the way originally laid out in this article, then the bean counters wanted date specific files. Well, I found you post and it looks great, but I am a little lost. Currently, I create a 'table' for the excel file, dump and re-create daily. Your suggestion is to create the path in an ActiveX object (like I could pre-define most of the path and then just add in the date part?), how, then do I create the file from that template? This is all a bit new to me, so sorry if I seem obtuse, just not sure how to bring this together. It sounds great, looks like it is exactly what I am looking for, jsut not quite sure how to implement it, so any further help you can give me would be great.

    Willie

    ZenDada (5/2/2007)


    If you are using a transformation to fill your spreadsheet:

    Create an Excel template that you always use for the destination. (Just the field names on the worksheet.)

    Upstream of the transform, create an ActiveX. Write some VB code that creates the path on the fly based on date, and assign the path to a local var. Copythe template workbook to that path using theFileSystemObject. Assign the local var to a global string var.

    After that, create Dynamic Properties Task. Assign the global var to the destination of the transformation.

    After that comes the transformation.

    If you have the Excel object model on the server, you can do your copy, move with that in the ActiveX script. But it's bloaty. Use the FSO.

    If you use an Excel template like this, you won't need to clear the 'table'. Just leave the template empty, and dump your transform to the worksheet by name. You may find that you need to cast some of your fields to varchar to make them look pretty in the output.

    Voila!

  • wildh

    SSCarpal Tunnel

    Points: 4260

    Willie,

    See if this helps - http://www.sqlservercentral.com/Forums/Topic93756-9-1.aspx

  • Willie Bodger

    Newbie

    Points: 6

    So, I can get the whole drop, recreate and fill the table thing figured out, but that jsut uses the same table over and over again. The part I can't see how to do is name the table like mmddyysales.xls where (obviously) mmddyy (or yymmdd, it's the data, not the format that I am looking for) is the day for which I am running the report. Thanks for the help!

    Willie

  • varun_sud1

    Grasshopper

    Points: 13

    Hi,

    Very nice article. Thanks.

    But, what if I need to maintain the headers in bold and only delete/insert the rows every time.

    Is there any way to do that? Or is there any way to make table headers bold after creating it?

    Thanks,

    Varun/-

  • wildh

    SSCarpal Tunnel

    Points: 4260

    Ther'll probably be a way to include the headers in SQL but not to make them bold. For that I would suggest a script that does that automatically when the book is opened.

Viewing 5 posts - 46 through 50 (of 50 total)

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