Capturing The Error Description In A Stored Procedure

Question

Capturing The Error Description In A Stored Procedure

IsaacGoGo

SSC Eights!

Points: 944
More actions
April 26, 2004 at 2:48 pm

#107776

Comments posted to this topic are about the content posted at http://www.sqlservercentral.co

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

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

msurasky Mr or Mrs. 500 Points: 595 More actions · Answer 1

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

Jim Underwood Mr or Mrs. 500 Points: 593 More actions · Answer 2

Enjoyed your article Joseph.

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

"Capturing The Error Description In A Stored Procedure"

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 3

"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]

bjure Ten Centuries Points: 1086 More actions · Answer 4

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.

wildh SSCarpal Tunnel Points: 4370 More actions · Answer 5

Very much akin to discussion

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=93756#bm93832

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.

rachel SSCommitted Points: 1626 More actions · Answer 6

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!

vakula manikonda SSC Rookie Points: 29 More actions · Answer 7

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

thanks

vakula

Brian Laws SSCommitted Points: 1556 More actions · Answer 8

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.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 9

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?

Thanks

Kindest Regards,

Just say No to Facebook!

Agson Chellakudam Ten Centuries Points: 1387 More actions · Answer 10

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

Dunc SSC Enthusiast Points: 108 More actions · Answer 11

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

Erich Cervantez Grasshopper Points: 15 More actions · Answer 12

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

DROP TABLE 'contacts'

GO

Finally I removed the single quotes and it worked:

DROP TABLE contacts

GO

weird...

bond007 SSCrazy Points: 2056 More actions · Answer 13

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

wildh SSCarpal Tunnel Points: 4370 More actions · Answer 14

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