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 |
|
cooro
Starting Member
5 Posts |
 Posted - 2008-04-14 : 16:36:26
|
| I'm running the following testdeclare @dbccdate varchar(60)set @dbccDate='dbcc east' + convert(varchar(60),getdate(),10)exec msdb.dbo.sp_send_dbmail@recipeints='human@gmail.com',@body = 'This is a test'@subject= @dbccdate ,@query='dbcc checkdb (msdb)',@attach_query_result_as_file=1,@query_attachment_filename='Dbcc_MSDB_Results.txt'This works fine but, I'd really like the subject to contain the line "Checkdb found 0 errors..." So mgmt doesn't have to open the txt file to view the results. I've tried dumping the results to a txt file first. Then trying to read the text file for the line inquestion. I wrote a for loop to parse the results command line, but can't figure how to add that value to the subject.The script is this:for /f %i in ('findstr /B /I "CHECKDB" y:\dbcclog\msdblog.txt') do echo %iIs there anyway to do this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-14 : 16:48:12
|
| When DBCC CHECKDB contains errors and is scheduled as a job, the job will fail. When there are no errors, the job will be successful. So instead of your current approach, just run it as a job and then have two more job steps that do the email. One job step will execute if the job fails, the other will execute if the job is successful.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
cooro
Starting Member
5 Posts |
Posted - 2008-04-14 : 17:28:19
|
| I see. But how is what you're suggesting different than my method. The end result is you still get emailed under both conditions no? Sry if I missed the point. Also, I've been asked to have the subject line contain that specific message. Any ideas how to get it?Thnx again Tara. |
|
|
|
cooro
Starting Member
5 Posts |
Posted - 2008-04-14 : 17:37:55
|
| Ahhh. I understand. Nevermind. I just had to go through the motions to get it.Thnk You! This way is much much smarter. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-14 : 17:39:17
|
| Well with my method, you could hard code the subject since you'll have two job steps for emailing. Job step1: Run DBCC CHECKDB with completion action of on success - go to step2, on failure - go to step3Job step2: Success email with completion action of quit the job reporting successJob step3: Failure email with completion action of quit the job reporting success (or failure, depends on what the DBA wants to see)Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
cooro
Starting Member
5 Posts |
Posted - 2008-04-14 : 18:30:39
|
| Yup Yup. So straight forward. In hindsight, not sure where I was going with my above method. lol!Thank You VERY VERY much! |
|
|
|
|
|
|
|
|
sp_send_dbmail question