Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Capturing The Error Description In A Stored Procedure


Capturing The Error Description In A Stored Procedure

Author
Message
Archie Neisz
Archie Neisz
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

subj: Using DTS to Generate and Email Excel Reports

Very useful information Joe - thanks for sharing, this technique will save a lot of time for me.


sql_er
sql_er
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 562

This is very useful indeed.

I have a question though. I need to change the excel spreadsheet name every day (i.e. I want to dynamically incorporate a time stamp in its name [e.g. Report_05022007]). Is that possible?

Thanks guys!


ZenDada
ZenDada
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 313

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. Copy the template workbook to that path using the FileSystemObject. 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!



ZenDada
sql_er
sql_er
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 562
Guys,

If we do not have the Microsoft Excel installed on the server where the template (created on my local machine with MS Excel installed) resides, will this approach still work?


Thanks a lot!
ZenDada
ZenDada
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 313
sql_er (7/21/2008)
Guys,

If we do not have the Microsoft Excel installed on the server where the template (created on my local machine with MS Excel installed) resides, will this approach still work?


Thanks a lot!


Heck yeah, just use the FileSystemObject.

ZenDada
sql_er
sql_er
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 562
Zendada,

Thank you for the reply - I am still a bit confused though.

My question was referring to the approach used by the original article. If I follow that approach as is, with no modifications, and the excel template will reside on the server with no MS Excel installed, will this approach work?


Thanks a lot!
ZenDada
ZenDada
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 313
sql_er (7/21/2008)
Zendada,

Thank you for the reply - I am still a bit confused though.

My question was referring to the approach used by the original article. If I follow that approach as is, with no modifications, and the excel template will reside on the server with no MS Excel installed, will this approach work?


Thanks a lot!


Oh sorry, I thought you were referring to my post. I just re-read the article. I don't see anything in the article that requires the Excel object model on the server, so you don't need the excel app on it. The delete and create table statements don't require it. Make sure you use ticks and not apostraphes just as in the article. Neither does the SMTP code require Excel (instructions for that are in the article). Hope that helps.

ZenDada
sql_er
sql_er
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 562
Zendada,

I don't think it is working. I tried it. Even though the DTS package executes with no errors, nothing happens - the original data remains in the excel spreadsheet.

The puzzling part is that it executes successfully. I tried just having the DROP TABLE component to see if the contents of the Excel spreadsheet would be deleted ... although the package executed successfully, the contents were not deleted.

Any suggestions?


Thank you!
ZenDada
ZenDada
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 313
sql_er (7/22/2008)
Zendada,

I don't think it is working. I tried it. Even though the DTS package executes with no errors, nothing happens - the original data remains in the excel spreadsheet.

The puzzling part is that it executes successfully. I tried just having the DROP TABLE component to see if the contents of the Excel spreadsheet would be deleted ... although the package executed successfully, the contents were not deleted.

Any suggestions?


Thank you!


Hmm.... I am not a DTS programmer any more... so I am working from memory here... let's see...
try these little debugging thingies...

start with a fresh workbook. Make sure it has only one page in it. run the create table statement pointing at that one page. close the connection. go back and run the delete table statement.

i think the connection object can point to either a page or a range, and that the "table" is really a contiguous range. so play with that idea. if the create table didn't work before, try creating a range now, and change the connection to this range now instead of the page.

as i recall, the default setting for excel is not to close the connection which leaves the workbook in a read only state. so don't forget to change that setting.

while you are messing around with this, between runs, try opening the workbook, deleting every freaking row (rightclick delete - you know what i mean) - that clears any remembered range. And if the create range thing worked for you before, create your range again with only the column headings and no, or one, row in it.

ZenDada
ZenDada
ZenDada
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 313
Oh and first thing - the data you have in there right now... how did it get there? was that with your create table statement and insert? Or did you simply export into the page? If the latter, your connection is pointing at the page, not the table (range).

ZenDada
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search