January 14, 2009 at 4:54 pm
Please suggest how to notify mutiple operators using database mail for failed jobs. Also entering email addresses 1@a.com;2@b.com....etc is not helping as there is a limit on the characters in the email name entry of operator.
Another peculiar thing I am facing is, I am able to email individual email IDs but not group emails like Group_name@abc.com. I don't know how the mail server is able to differentiate.
Kindly provide your inputs.
M&M
January 14, 2009 at 5:17 pm
Create an operator with multiple email addresses and use that in the job notification. You create operators by expanding SQL Server Agent in SSMS, right-clicking on Operators, and choosing "New Operator...".
I don't have an answer for your group vs individual emal address problem.
Greg
January 14, 2009 at 5:27 pm
But there is a limit on the number of email addresses we can add in operator. Is there any way we could increase the character length that it would accept. That is a problem I am facing now as the notifications to group emails is not working.
M&M
January 14, 2009 at 5:42 pm
Not sure about the group - but I do know that having a group defined in Exchange will not work. The group usually has to be defined in AD.
The other option you have is to use sp_send_dbmail instead - and build your own notification. One example would be to create a job step that only executes on failure of the previous step - and that job step executes sp_send_dbmail with the message you want.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 15, 2009 at 4:36 pm
Using sp_send_dbmail to send notifications is a good option.
But when I trying to attach an attachment I am getting this error message.
Failed to open attachment file 'T:\master\test_backup_job.txt'. Executing API 'CreateFile' failed with error number 32. [SQLSTATE 42000] (Error 22051). The step failed.
But when I run the below script directly from query window without making it part of job step it runs.
sp_send_dbmail @profile_name = 'Sample'
, @recipients = 'abc.com'
, @copy_recipients = 'xyz.com'
, @subject = 'Job has failed'
, @importance = 'HIGH'
, @file_attachments = 'T:\master\test_backup_job.txt'
Kindly advice
M&M
January 15, 2009 at 5:57 pm
Max characters allowed for specifying email addresses is only 100, beyond that what ever that is specified will get truncated.
The best option is to have ad groups created and include newly ad group as an operator. This should be good enough.
January 16, 2009 at 10:02 am
mohammed moinudheen (1/15/2009)
Using sp_send_dbmail to send notifications is a good option.But when I trying to attach an attachment I am getting this error message.
Failed to open attachment file 'T:\master\test_backup_job.txt'. Executing API 'CreateFile' failed with error number 32. [SQLSTATE 42000] (Error 22051). The step failed.
But when I run the below script directly from query window without making it part of job step it runs.
sp_send_dbmail @profile_name = 'Sample'
, @recipients = 'abc.com'
, @copy_recipients = 'xyz.com'
, @subject = 'Job has failed'
, @importance = 'HIGH'
, @file_attachments = 'T:\master\test_backup_job.txt'
Kindly advice
When you run the command from a job - it runs in the context of the user that is running SQL Server Agent. Does that user have access to the T:\ drive? Is this drive a local drive for the server?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 16, 2009 at 11:07 am
The owner of this job is the sql server agent service account.
However, the T drive is being accessed remotely. It is not local drive.
I don't have box level access on this server.
M&M
January 16, 2009 at 12:56 pm
I would bet that there isn't a T: drive on the server. Even if there were, the Agent wouldn't see the drive because you cannot map a network drive for a service.
You need to reference the UNC path instead of trying to use a drive letter. Additionally, the account running the agent must be a domain account with privileges for the file share that you are accessing.
Without that - you will not be able to access the document and get it attached to the email.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 16, 2009 at 1:30 pm
Please advice, how would I use UNC path? Should I create it or it could be identified if it exists?
M&M
January 16, 2009 at 1:52 pm
Not sure I follow you - the UNC path would be: \\servername\{share name}. Let's say you have a mapped drive called T:. That mapped drive actually references a shared folder on some server and uses the UNC path to define the mapped drive.
From SQL Server - you need to reference the UNC and make sure sure the user running SQL Server Agent is a domain user and has been granted access to that share.
It will look something like:
sp_send_dbmail @profile_name = 'Sample'
, @recipients = 'abc.com'
, @copy_recipients = 'xyz.com'
, @subject = 'Job has failed'
, @importance = 'HIGH'
, @file_attachments = '{server name}\{share name}\master\test_backup_job.txt'
Replace {server name} with the actual server name and {share name} with the actual shared folder name (the full path, down to the 'master' folder).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 16, 2009 at 1:55 pm
Use Unc Path for the T: drive instead.
\\ServerName\ShareName\File.txt
* Noel
January 16, 2009 at 4:53 pm
I am trying to modify the instructions accordingly.
sp_send_dbmail @profile_name = 'Sample'
, @recipients = 'abc@xyz.com'
, @copy_recipients = 'xyz@abc.com'
, @subject = 'Job Test_backup has failed'
, @body = 'Please send email to us for any further queries on this'
, @importance = 'HIGH' ;
, @query = xp_cmdshell 'type T:\master\test_backup_job.txt'
, @attach_query_result_as_file = 1 ;
I am trying to use the @query option instead where we could use
xp_cmdshell instead. Could you please advise if this option is correct. Seems like I am not able to correct the syntax error.
Basically, I am trying to attach the output file. Please advise if you have better ideas. Thanks.
M&M
January 16, 2009 at 5:02 pm
The @query option has to be a query that is run. There is a parameter for including an attachment which is what you should be using instead.
Please look this up in Books Online for further information on how to use the procedure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 16, 2009 at 6:15 pm
I was able run this notification successfuly from my job. I tested now.
sp_send_dbmail @profile_name = 'Sample'
, @recipients = 'abc.com'
, @copy_recipients = 'xyz.com'
, @subject = 'Job Test_backup_job has failed'
, @body = 'Job has failed'
, @importance = 'HIGH'
, @query = 'xp_cmdshell ''type T:\master\job_test.out'''
, @attach_query_result_as_file = 1 ;
@query is working fine, but when I used the same path with @file_attachments the notification failed. Jeff you are right, that path was not reachable, we have to use UNC path for that.
M&M
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply