Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-10-25 : 12:21:04
|
| If this approach doesn't sound right, please let me know.I'm concerned about reporting the results of an automated import process into a database. I've written a long SP that cleans data, eliminates duplicates, and inserts / updates what's new into several different tables in the database. The source data is provided by a customer. It should be good wellformed data, but then it may not be good. That's why they're called customers.. ;DI'd like this procedure to report what it did to me via email (the procedure can run anytime), so I'm thinking of building a varchar string of all the intermediate resultsTime of process -- other identification No of duplicate deletionsNumber of insertions in Table ANumber of insertions in Table BRecord any error codes along the way, etc.Does this make sense or is there a better way to find out how a 2AM process performed?Sam |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-25 : 12:27:53
|
| If you like the email approach... Dump all that info into a temp table. then run your mail program sending the results of a queryif your using xp_sendmailxp_sendmail @recipients = 'me@me.com',@subject 'proc ran',@query = 'select * from #temp'You could go more in depth if you wanted as well... Inside the temp teble record all changes (insert/update/delete and the primary key information) so you can track what changes were made.-----------------------SQL isn't just a hobby, It's an addiction |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-25 : 14:50:19
|
| There is one little problem with this kind of logging ... implicit transaction.If you write a row to a 'importstatus' table inside a stored proc and there is a subsequent error in that proc that causes failure, the implied transaction around that entire batch will be rolled back ... meaning the status record will never be committed.This also may be true with entire DTS packages, depending on how you transaction options are set for the package.The best way I have found around this is to use SQL-DMO to connect back to the server to write the status record. Anything that happens in a SQL-DMO connection is outside the scope of the present transaction.Jay White{0} |
|
|
|
|
|
|
Recording intermediate results