SQLServerCentral / Discuss Content Posted by Joseph Sack / Article Discussions / Article Discussions by Author / Capturing The Error Description In A Stored Procedure / Latest Posts

RE: Capturing The Error Description In A Stored Procedure

wildh — Thu, 11 Feb 2010 00:49:42 GMT

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.

RE: Capturing The Error Description In A Stored Procedure

varun_sud1 — Wed, 10 Feb 2010 23:18:48 GMT

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/-

RE: Capturing The Error Description In A Stored Procedure

Willie Bodger — Fri, 13 Mar 2009 10:09:20 GMT

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

RE: Capturing The Error Description In A Stored Procedure

wildh — Fri, 13 Mar 2009 02:56:57 GMT

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

RE: Capturing The Error Description In A Stored Procedure

Willie Bodger — Thu, 12 Mar 2009 14:44:11 GMT

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[quote][b]ZenDada (5/2/2007)[/b][hr]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![/quote]

RE: Capturing The Error Description In A Stored Procedure

sql_er — Mon, 28 Jul 2008 14:00:25 GMT

Zendada, and Bitbucket - Thank you for the information.I used the following (as suggested) to fix the problem:Method 1: Manually delete excess rows and columnsTo reset the last cell by manually deleting excess rows and columns, follow these steps:1. Select all columns to the right of the last column that contains data by selecting the appropriate column headings.Tip One way to do this is to press F5 and type the appropriate reference. For example, type F:IV for columns, or type 5:65536 for rows.2. On the Edit menu, click Clear All.Note If this step does not clear all the excess formatting, you may have to right-click the columns and then click Delete to fully remove the cells. If you delete cells that are referenced by formulas, the reference changes to "#REF!." Therefore, we recommend that you create a backup of the original file and then check your formulas for "#REF!" after you follow these steps.3. Repeat steps 1 and 2 for the rows that are under the last row that contains data.4. Save the file.5. To continue working in the file, close and then reopen the file.It would be great if this approach could be automated though ...Thanks!

RE: Capturing The Error Description In A Stored Procedure

bitbucket-25253 — Fri, 25 Jul 2008 18:26:43 GMT

sql_er this may explain your problemWhen you save a workbook, Microsoft Office Excel stores only the part of each worksheet that contains data or formatting. [b]Empty cells may contain formatting that causes the last cell in a row or column to fall outside of the range of cells that contains data.[/b] This causes the file size of the workbook to be larger than necessary and may result in more printed pages when you print the worksheet or workbook.To avoid these issues, you can locate the last cell that contains data or formatting on a worksheet, and then reset that last cell by clearing all of the formatting that may be applied in empty rows or columns between the data and the last cell.Let's say you enter any text or number in cell A10 and then enter a valid date in cell A20 of a new Worksheet. Now delete the date in cell A20 and save. This is because entering a date in A20 has caused Excel to automatically format the cell from "General" to a Date format.Also refer to:http://support.microsoft.com/?id=244435

RE: Capturing The Error Description In A Stored Procedure

ZenDada — Fri, 25 Jul 2008 13:59:29 GMT

[quote][b]sql_er (7/25/2008)[/b][hr]Guys,I have noticed a few things happening with the excel spreadsheet, as I am using this approach:1. The excel file is increasing in size, even though the data inserted does not grow in size (as if so2. If I inserted 1000 rows yesterday, and today i run the DTS package, which clears everything out and inserts 200 rows, I get 800 empty rows after it, with the size of the Excel spreadsheet either same as 1000 row one or even more3. Sometimes empty rows are just added on and on every time I run this reportHas anyone encountered any of these problems and could point to a solution?Thanks a lot![/quote]I recall that being one of the issues that led me to my approach as I described earlier in this thread. I think the fix though, was to manually name the range with the table name with only the field names in the first row, and ranging rows 1 and 2. Then using a transformation to fill the excel table after the delete statement. Something like that. Try my approach instead. It's much cooler.

RE: Capturing The Error Description In A Stored Procedure

sql_er — Fri, 25 Jul 2008 13:38:51 GMT

Guys,I have noticed a few things happening with the excel spreadsheet, as I am using this approach:1. The excel file is increasing in size, even though the data inserted does not grow in size (as if so2. If I inserted 1000 rows yesterday, and today i run the DTS package, which clears everything out and inserts 200 rows, I get 800 empty rows after it, with the size of the Excel spreadsheet either same as 1000 row one or even more3. Sometimes empty rows are just added on and on every time I run this reportHas anyone encountered any of these problems and could point to a solution?Thanks a lot!

RE: Capturing The Error Description In A Stored Procedure

sql_er — Tue, 22 Jul 2008 12:50:03 GMT

Zendada, thank you for all the suggestions.I think I figured it out.Here is what happened:I had that DTS package running on my local server. Everything ran fine, data was loaded into excel, and sent out properly. I was then asked to implement the same thing on the production server. I did. But when I manually ran the DTS package on the production server, for some reason, the data never changed there [which is why i made the post]. I then tried to delete the data, which also failed, as I specified ... or did it?I looked back at my LOCALfolder and saw that the data was deleted there ... on my LOCAL server. I then went back to the Production server, scheduled the 'delete table' DTS package as a job, and ran the job ... and voila ... the data got deleted on my Production server Excel spreadsheet.So, it seems, that when I ran the PRODUCTION DTS manually (while being on my local machine), the DTS was looking at the excel spreadsheet on my local drive [locations between production and local are identical, which is why i did not catch this problem earlier].So, I'm assuming that if I would login to the production server and run the DTS from there it would work too ... just as scheduling and running the job ...

RE: Capturing The Error Description In A Stored Procedure

ZenDada — Tue, 22 Jul 2008 12:39:40 GMT

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).

RE: Capturing The Error Description In A Stored Procedure

ZenDada — Tue, 22 Jul 2008 12:34:00 GMT

[quote][b]sql_er (7/22/2008)[/b][hr]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![/quote]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.

RE: Capturing The Error Description In A Stored Procedure

sql_er — Tue, 22 Jul 2008 12:15:19 GMT

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!

RE: Capturing The Error Description In A Stored Procedure

ZenDada — Mon, 21 Jul 2008 15:22:27 GMT

[quote][b]sql_er (7/21/2008)[/b][hr]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![/quote]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.

RE: Capturing The Error Description In A Stored Procedure

sql_er — Mon, 21 Jul 2008 15:03:37 GMT

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!

RE: Capturing The Error Description In A Stored Procedure

ZenDada — Mon, 21 Jul 2008 12:10:31 GMT

[quote][b]sql_er (7/21/2008)[/b][hr]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![/quote]Heck yeah, just use the FileSystemObject.

RE: Capturing The Error Description In A Stored Procedure

sql_er — Mon, 21 Jul 2008 12:01:06 GMT

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!

RE: Capturing The Error Description In A Stored Procedure

ZenDada — Wed, 02 May 2007 19:08:00 GMT

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!

RE: Capturing The Error Description In A Stored Procedure

sql_er — Wed, 02 May 2007 14:11:00 GMT

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!

RE: Capturing The Error Description In A Stored Procedure

Archie Neisz — Tue, 24 Apr 2007 06:45:00 GMT

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.

RE: Capturing The Error Description In A Stored Procedure

Frances L — Tue, 26 Sep 2006 10:06:00 GMT

Will you please post your active script task code to call EXCEL macro?

Thanks.

Also I have error to run the DTS. it said syntax error in the drop table or drop index. I also tried to DROP TABLE 'authors'

DROP TABLE 'authors' GOCREATE TABLE `authors` (`au_id` VarChar (11) , `au_lname` VarChar (40) , `au_fname` VarChar (20) , `phone` VarChar (12) , `address` VarChar (40) , `city` VarChar (20) , `state` VarChar (2) , `zip` VarChar (5) , `contract` Bit )

RE: Capturing The Error Description In A Stored Procedure

charles evans — Mon, 25 Sep 2006 09:38:00 GMT

I did this for years at my former job. You can also call an Excel macro which formats the report directly from an active script task before sending.

RE: Capturing The Error Description In A Stored Procedure

Frances L — Mon, 25 Sep 2006 06:04:00 GMT

I am pretty new to DTS. but I have problem to name the active script and sql connection with the authors table.

I have ACCESS database. I am curious how I can get query export to excel monthly. I have timer set up which the end users launch the application. but it is not reliable.

Thanks.

RE: Capturing The Error Description In A Stored Procedure

gex — Wed, 28 Sep 2005 23:26:00 GMT

In response to "The FinalSQL"'s question:Yes it is possible to pump data to multiple Tabs in a excel file. The trick? There is none. Just simply Create Table's for each new tab you require (as you did for the "Author's " tab in the example given. Be sure to specify that the connection you are using is indeed the existing Excel file! Once the table's (or Tabs) have been created, use the Transform Data Task to upload your data into the specified Tab (Indicate which tab you want to uplaod data to by selecting it in the "destination" tab of the Transaform Data Task.Another nice feature of using teh ActiveX Scripts is that you can format your excel files after the Data Pump has been completed. (Or you can simply utilise a template as someone has already discussed).A lot of people seem to have problems with the ActiveX SendMail functionaility. Personnaly I have only used the XP_sendmail function or the "Send Mail Task" option in DTS Designer. This option uses a wizzard of sorts to set-up your send mail task. Please Note that in all instances of sending mail through a DTS Package that you must have a Mail Service running in SQL Server.I hope this helps and doesn't confuse you.

RE: Capturing The Error Description In A Stored Procedure

Joh Louis — Wed, 28 Sep 2005 13:01:00 GMT

Hi all,

I liked the article by Mr. Sack, this is my first interaction with SQL and need help.

See, I have a back-up of two .db files from SQL 2000 that need to get converted onto Excel 2003 then through a citrix connection then transferred into another SQL db...now i only have that back -up file and excel on a standalone Pc any suggestions? THanks

RE: Capturing The Error Description In A Stored Procedure

Yoav S. — Mon, 26 Sep 2005 05:20:00 GMT

Hi user4301,

To use the exchange server you need to install a MAPI client on the sql server machine (an outlook account of the account that runs your sql server service). look a Books online on the topic of SQLMail. Then there is a dts task of sendmail that uses this outlook account and will send Emails.

Also there is a nice XP in http://www.sqldev.net/xp/xpsmtp.htm that sends emails in SMTP.

its a nice wrapper for the commands of SMTP.

yoav.

RE: Capturing The Error Description In A Stored Procedure

Ed Salva — Fri, 23 Sep 2005 12:24:00 GMT

Thanks Joe,

I have serveral variants of the script, but JIC I quick tested the script with the appropriate changes. no luck.

I need to dig a little deeper into our mail server. We are using Novell Groupwise and had a network god who liked to tweak settings for fun. (even to the point that admin only has restricted rights) fortunatly he's no longer with us, but his memory lives on ...

I need to do some more research on the novell side and lower level settings.

RE: Capturing The Error Description In A Stored Procedure

user4301 — Fri, 23 Sep 2005 11:43:00 GMT

Does anybody have any suggestions for those using Exchange Server at the corporate office?

I am informed that we do not therefore use SMTP.

By the way is it possible to get the message number or some thing similar to actually really positively completely tell the message has been sent?

RE: Capturing The Error Description In A Stored Procedure

IsaacGoGo — Fri, 23 Sep 2005 11:32:00 GMT

Hi All,

One common question I get about this article is about the email not working. The article's example assumes you have a valid, open relay SMTP service running from your client or SQL Server instance machine.

If you don't, here is a code example of referencing a remote SMTP service:

Function Main()

Dim iMsg Dim objMail

Const cdoSendUsingPickup = 1Const cdoSendUsingPort = 2

set iMsg = CreateObject("CDO.Message")set iConf = CreateObject("CDO.Configuration")Set Flds = iConf.Fields

With Flds .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort 'Change this parameter below to your valid SMPT Server Name .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="10.10.10.99" .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10 .UpdateEnd With

With iMsg Set .Configuration = iConf.From = "test@test.com".To = "test@test.com".AddAttachment ( "c:\temp\test.xls").Subject="Test Spreadsheet".TextBody = "Spreadsheet".Send End With

Set objMail = nothing

Main = DTSTaskExecResult_SuccessEnd Function

Best Regards,

Joe

RE: Capturing The Error Description In A Stored Procedure

Ed Salva — Fri, 23 Sep 2005 09:08:00 GMT

Very nice, I was working on something similar a while back.

Was able to get all but the email to work properly.

Inhouse network staff were unable to help, and internet searches yeilded limited results.

Haven't had time to revisit this.

Curious if you had any problems with email setup.

RE: Capturing The Error Description In A Stored Procedure

The FinalSQL — Fri, 23 Sep 2005 06:00:00 GMT

Would it be possible to export data to multiple tabs or specify a tab to export the data to?Thanks

RE: Capturing The Error Description In A Stored Procedure

David le Quesne — Fri, 23 Sep 2005 03:00:00 GMT

I also had the drop table problem, I just removed the quotes altogether, which seemed to solve the problem

I have a problem with the ActiveX script, it results in an error

Error Code: 0

Error Source: CDO.Message.1

'The SendUsing configuration value is invalid'

Error Line : 13

David

RE: Capturing The Error Description In A Stored Procedure

~nano — Fri, 23 Sep 2005 02:21:00 GMT

Excellent just the kind of thing I need ....

Now if someone could give me a tip on using a parameterized stored Procedure instead of a view of a table ... and how to invoke that from a .net application it would absolutely make my day!!!!

nano

RE: Capturing The Error Description In A Stored Procedure

wildh — Fri, 23 Sep 2005 02:03:00 GMT

Ahh. I was talking about spreadsheets not CSV, my mistake.

RE: Capturing The Error Description In A Stored Procedure

bond007 — Fri, 23 Sep 2005 01:57:00 GMT

Wildh, i've found the opposite. You don't need to delete and recreate the csv file, the original data is replaced with the new data, at least in my experience.

RE: Capturing The Error Description In A Stored Procedure

wildh — Fri, 23 Sep 2005 01:48:00 GMT

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

RE: Capturing The Error Description In A Stored Procedure

bond007 — Fri, 23 Sep 2005 01:43:00 GMT

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?

RE: Capturing The Error Description In A Stored Procedure

Erich Cervantez — Fri, 16 Sep 2005 16:40:00 GMT

I kept getting a syntax error with my Execute SQL Task:DROP TABLE 'contacts'GOFinally I removed the single quotes and it worked:DROP TABLE contactsGOweird...

RE: Capturing The Error Description In A Stored Procedure

Dunc — Thu, 30 Jun 2005 09:59:00 GMT

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?

Thanks,

Dunc

RE: Capturing The Error Description In A Stored Procedure

Agson Chellakudam — Thu, 05 May 2005 01:04:00 GMT

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