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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Declare @msgbody

Author  Topic 

werhardt
Constraint Violating Yak Guru

270 Posts
Posted - 2010-05-11 : 09:05:27

This is part of my message that I send to some departments. Is there a way to have the sentence in the body not be a string? I would like the sentence that says.....


'Please forward to the person who is reponsible for this error and have them fix it. '

In it's own paragraph. How can I do that?



DECLARE @MsgBody VARCHAR(MAX)

SET @MsgBody = 'Please see the attached exception report that lists '
SET @MsgBody = @MsgBody + STR(@NoOfRows) + ' instance(s) of incorrect Product Linking. The Product Linking should be "NULL".'
SET @MsgBody = @MsgBody + 'Please forward to the person who is reponsible for this error and have them fix it. '


EXEC msdb.dbo.sp_send_dbmail @recipients='wsilage@devonhealth.com',
@subject = 'WARNING! Incorrect Product Linking for ECN',
@query = @SQLQuery,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Exception_Report.txt',
@Body = @MsgBody,
@body_format = 'HTML',
@importance = 'HIGH',
@query_result_width = 256,
@query_result_separator = '|'

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-05-11 : 09:08:44
Maybe this:
SET @MsgBody = @MsgBody + char(13) + char(10) + 'Please forward to the person who is reponsible for this error and have them fix it. '

for CRLF

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-05-11 : 09:17:26
Surely, even more easy to Understand would be this

SET @MsgBody = 'Please see the attached exception report that lists ' + STR(@NoOfRows) + ' instance(s) of incorrect Product Linking. The Product Linking should be "NULL",
Please forward to the person who is reponsible for this error and have them fix it.'

You can span multiple lines in the same string.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-05-11 : 09:20:36
quote:
Originally posted by Transact Charlie

Surely, even more easy to Understand would be this

SET @MsgBody = 'Please see the attached exception report that lists ' + STR(@NoOfRows) + ' instance(s) of incorrect Product Linking. The Product Linking should be "NULL",
Please forward to the person who is reponsible for this error and have them fix it.'

You can span multiple lines in the same string.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sorry but I didn't get you.
Where is the "own paragraph" in your post?

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-05-11 : 09:22:21
Well the "Please forward to the person who is reponsible for this error and have them fix it." is on it's own line. That's generally how you'd make a new paragraph in a piece of simple text?

Maybe this is clearer?

SET @MsgBody = 'Please see the attached exception report that lists ' + STR(@NoOfRows) + ' instance(s) of incorrect Product Linking. The Product Linking should be "NULL",

Please forward to the person who is reponsible for this error and have them fix it.'


All I meant was that you don't have to concatenate +CHAR(13) + CHAR(10) you can just press the return key on your keyboard inside the string itself. (the string I posted above has 2 CRLF without having to use + CHAR(13) + CHAR(10).

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-05-11 : 09:22:51
quote:
Originally posted by webfred

quote:
Originally posted by Transact Charlie

Surely, even more easy to Understand would be this

SET @MsgBody = 'Please see the attached exception report that lists ' + STR(@NoOfRows) + ' instance(s) of incorrect Product Linking. The Product Linking should be "NULL",
Please forward to the person who is reponsible for this error and have them fix it.'

You can span multiple lines in the same string.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sorry but I didn't get you.
Where is the "own paragraph" in your post?

No, you're never too old to Yak'n'Roll if you're too young to die.

Aah - I think I understand.
You can span multiple lines in the same string.
and it is using the provided "Keyboard Hit" on return key while typing in that text...

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-05-11 : 09:23:48
But it looks much too easy for me

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-05-11 : 09:25:42
quote:
Originally posted by webfred

But it looks much too easy for me

No, you're never too old to Yak'n'Roll if you're too young to die.

Yeah -- there is that. Better to make your code as complicated as possible so they can't fire you?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-05-11 : 09:28:17
No I don't need to do that because they love me because I am a grind...

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

werhardt
Constraint Violating Yak Guru

270 Posts
Posted - 2010-05-11 : 12:12:35
Thanks so much for everyone's help.

On other question, is there away to only have this sent when there is an error? I was thinking making up a formula that says @NoOfRows > 0, then send email.


begin

DECLARE @SQLQuery NVARCHAR(MAX)
DECLARE @NoOfRows INT


set deadlock_priority high;

SET @NoOfRows = (Select Count(*)
FROM impact.dbo.prop
Inner join impact.dbo.pro
On pro_id1 = prop_id1
WHERE (prop_net <> 'DEVON' and prop_net <> 'GHINA')and
prop_prd = 'DGH' and
(prop_auto <> '#' or
prop_auto is Null))


SET @SQLQuery = 'Select Distinct---*
pro_pcs AS PCS#, prop_id1 AS PRO#,
prop_id2, prop_net, prop_prd,
prop_auto, prop_eff, prop_trm,prop_chgby

FROM impact.dbo.prop
Inner join impact.dbo.pro
On pro_id1 = prop_id1
WHERE (prop_net <> "DEVON" and prop_net <> "GHINA")and
prop_prd = "DGH" and
(prop_auto <> "#" or
prop_auto is Null)
order by prop_auto desc'



DECLARE @MsgBody VARCHAR(MAX)

--SET @MsgBody = 'Please see the attached exception report that lists '
---SET @MsgBody = @MsgBody + STR(@NoOfRows) + ' instance(s) of incorrect Product Linking. The Product Linking should be "#"'

SET @MsgBody = 'Please see the attached exception report that lists ' + STR(@NoOfRows) + ' instance(s) of incorrect Product Linking. The Product Linking should be "#",
please forward to the person who is reponsible for this error and have them fix it.'



EXEC msdb.dbo.sp_send_dbmail @recipients= 'wsilage@devonhealth.com,,kzaccone@devonhealth.com,ptirado@devonhealth.com,
nleh@devonhealth.com,tbrown@devonhealth.com, bholland@devonhealth.com',

@subject = 'WARNING! Incorrect Product Linking for DGH',
@query = @SQLQuery,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Exception_Report.txt',
@Body = @MsgBody,
@body_format = 'HTML',
@importance = 'HIGH',
@query_result_width = 256,
@query_result_separator = '|'

END
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-05-11 : 12:21:18
if you don't want to send the mail unless @noOfRows is populated then just add an

IF @noOfRows > 0 BEGIN
...
END

Maybe


IF @noOfRows > 0 BEGIN

SET @MsgBody = 'Please see the attached exception report that lists ' + STR(@NoOfRows) + ' instance(s) of incorrect Product Linking. The Product Linking should be "#",
please forward to the person who is reponsible for this error and have them fix it.'



EXEC msdb.dbo.sp_send_dbmail @recipients= 'wsilage@devonhealth.com,,kzaccone@devonhealth.com,ptirado@devonhealth.com,
nleh@devonhealth.com,tbrown@devonhealth.com, bholland@devonhealth.com',

@subject = 'WARNING! Incorrect Product Linking for DGH',
@query = @SQLQuery,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Exception_Report.txt',
@Body = @MsgBody,
@body_format = 'HTML',
@importance = 'HIGH',
@query_result_width = 256,
@query_result_separator = '|'

END



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

werhardt
Constraint Violating Yak Guru

270 Posts
Posted - 2010-05-11 : 12:34:12
Thanks, I just figured it out! :)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site