October 23, 2008 at 12:43 pm
When my datafile is processed I send an email to distribution lists with Detail information using database mail that is on expression-- thsi is my code "EXEC msdb.dbo.sp_send_dbmail
@profile_name = '"+ @[User::DBProfileName] +"' , " +
"@recipients = " + "'" + @[User::EmailLists] + "'" + ", " +
"@subject = " + "'" + @[System::PackageName] + "-"+
@[System::MachineName]+ ": Inbount SUCCESSFUL"+ "'" +"," +
"@body = " + "'" + "DataFile --" + @[User::MyFileName] +
" with " + (DT_STR, 15 , 1252) @[User::CountofRecord] + " Records has been Processed.
The Actual Count of Typical (records with NPI) Records INSERTED into DB is " + (DT_STR, 15 , 1252) @[User::TypicalCount] +
". Similaraly, Atypical Records Count (records without NPI) INSERTED into DB is " + (DT_STR, 15 , 1252) @[User::AtypicalCount] +
" . And Total Typical Records (records with NPI) UPDATED in DB is " + (DT_STR, 15 , 1252) @[User::UpdateCount]
+
". Also, Total Atypical Records (records without NPI) that was UPDATED in DB is " + (DT_STR, 15 , 1252) @[User::UpdateCountA]
+
". Remaining were SKIPPED records, records that were duplicates or bad. Detail SKIPPED Count is as following : " +
" Bad Records = " + (DT_STR, 15 , 1252) @[User::ErrCount] +
". Intermediate Records (records that doesnt need update) = " + (DT_STR, 15 , 1252) @[User::IntermediateCount] +
". Skipped Insert Typical Records = " + (DT_STR, 15 , 1252) @[User::SkInTypicalRcdsCount] +
". Skipped Insert Atypical Records = " + (DT_STR, 15 , 1252) @[User::SkInAtypicalRcdsCount] +
". Skipped Update Typical Records = " + (DT_STR, 15 , 1252) @[User::SkUpTypicalRcdsCount] +
". Skipped Update Atypical Records = " + (DT_STR, 15 , 1252) @[User::SkUpAtypicalRcdsCount] +
" ' " + "," +
"@body_format =" + "'" + "TEXT" + "'" + "," +
"@importance =" + "'" +"NORMAL" + "'"
The body part of the email looks like this-----
DataFile --InboundPNF with 32974 Records has been Processed. The Actual Count of Typical (records with NPI) Records INSERTED into DB is 15654. Similaraly, Atypical Records Count (records without NPI) INSERTED into DB is 0 . And Total Typical Records (records with NPI) UPDATED in DB is 833. Also, Total Atypical Records (records without NPI) that was UPDATED in DB is 0. Remaining were SKIPPED records, records that were duplicates or bad. Detail SKIPPED Count is as following : Bad Records = 0. Intermediate Records (records that doesnt need update) = 0. Skipped Insert Typical Records = 15654. Skipped Insert Atypical Records = 0. Skipped Update Typical Records = 1666. Skipped Update Atypical Records = 0
How can I make it more clear something liek this in the body part.
i am simply trying to make it clear and organized........
Total InboundPNF:xxxxxx
Typical Insert:xxxxxx
Atypical Insert:xxxxxx
...
Skipped:xxxxxx
...
October 23, 2008 at 1:17 pm
You can embed the ASCII character codes for Tab (Char(9)) and Carraige Return and Line Feed (Char(10) and Char(13), I can never remember which is which). Or you can go the HTML route and embed html in the email and sent the @body_type parameter to HTML.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply