March 5, 2009 at 1:31 pm
We have SQL Server 2005, SP3 running on Windows 2003. I have SQL Server Jobs set up to execute Maintenance Plans. I have configured the Reporting and Logging (click Button) dialog. I checked the "Send report to an email recipient" option. I have verified the Database System Parameters under the Database Mail Configuration Wizard. Everything seems to be set okay (i.e. Maximum File Size, Prohibited Attachment File Extensions, etc). These parameters are not configured to stop an attachment from being sent. The job runs successfully and the text files are created, but they are not being sent with the email.
BTW, I am using an smtp server.
Should the Database Mail Account Name be set up in the MSDB Database as a user and given a certain role? Also, should this account be given permissions to the server's directory that stores the text files?
Thanks, Kevin
March 7, 2009 at 8:38 am
does it send mail without the attachment.
March 9, 2009 at 6:14 am
I'm going to assume you've already activated database mail & set up your profiles & accounts. However, as Tracey posted, test the dbmail proc in SSMS without an attachment to make sure dbmail itself is working.
What account is SQL Server Agent using? Local System or domain user? If local system, that's you're first problem. It needs to be a plain vanilla domain user.
Does the Agent account have access to the share where the text files exist? Your thread seems to indicate it doesn't. This account will need read & execute perms at the very least. Maybe even modify / write, though you'll have to experiment with this.
Also, check with your mail team & network admin. It could be that there's a filter to prevent the text file from getting through the STMP Mail server itself or a firewall issue, etc. This is the last thing you'll want to check though. The problem is likely something on the SQL Server side.
The DB mail account name does NOT need to be set up as a user. We use fake DBMail account names all the time (not even real users) and don't have a problem.
Hope this helps.
March 9, 2009 at 7:17 am
Yes, the email is being sent. The attachment is not being sent.
March 9, 2009 at 7:33 am
Did any of the other information help?
March 9, 2009 at 8:43 am
Not yet. We are using a Domain User which is used to start the SQL Agent Service. (This is SQL Server 2005 running on Windows Server 2003.) This is what I have tried this morning. I put the Domain User in the Administrators Group and also in the 'sysadmin' Server DB Role. I also changed the Job (that I am testing) Owner to the Doman User. When I run the job, I get the email but no attached file. The file is created. I noticed my Maintenance Plan is showing myself as the owner (because I was logged into the machine when I created the Maintenance Plan). Would this be the problem? I guess I would need to log into the SQL Server as the Domain User and then create the Maintenance Plan. Any thoughts?
Thanks, Kevin
March 9, 2009 at 10:36 am
I put the Domain User in the Administrators Group and also in the 'sysadmin' Server DB Role.
ACK! Domain Admin BAD karma for security reasons. Make the user account a plain vanilla account, remove Sysadmin access.
When you say Administrators Group, though, are you talking on the domain level or the server built in admin group?
BTW, Did you check the share permissions to see if the domain account has access?
The domain user account needs to have several global policies attached to it. Here are the permissions needed to support SQL Server properly (per BOL):
Permission to act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)
Permission to bypass traverse checking (SeChangeNotifyPrivilege)
Permission to replace a process-level token (SeAssignPrimaryTokenPrivilege)
Permission to adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Permission to log on using the batch logon type (SeBatchLogonRight)
Check those and check the security settings on the folder / share which contains the attachment files. Remember that DENY overrides all other permissions and the principal of least permissions applies.
March 9, 2009 at 1:08 pm
I have made progress on this today. (I removed the user account from the built in admin group and from the db server sysadmin role.) I have it so the attached file content is displayed within the email. I had to set the profile for the smtp account (within the DBMail Feature) to a default profile. (This is okay, but I would like to know how to get DBMail to send the attached file.)
I also had to enable DBMail Feature using code. It appeared not to have been enabled when using the GUI.
-- SQL Code:
--To enable the Database Mail Feature (with code)
USE Master
GO
sp_configure 'show advanced options', 1
GO
reconfigure with override
GO
sp_configure 'Database Mail XPs', 1
GO
reconfigure
GO
sp_configure 'show advanced options', 0
GO
Does anyone have SQL Server 2005 DBMail working with Jobs/Maintenance Plans so that it sends the attached file (verses the content of the file displayed within the email)?
March 9, 2009 at 1:15 pm
I don't got it working it just sends me success or failure email. I remeber asking a Microsoft Consultant same question once he said it was not possible.
So how I did it was have my output go to same file and manually attach that file in T-SQL statement.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 10, 2009 at 4:23 am
We actually have this working with attachments, but it's been a while since we set it up.
However, just got a new Dev cluster, so I'll play with it in the new cluster since I have to configure the cluster. @=)
Soon as I know something, I'll let you know.
March 10, 2009 at 6:44 am
As far as writing the output to the same file, I am talking about the file that contains the database tasks (check integrity, update statistics, backups, etc) output. It sounds like you (Mohit) are talking about the file that contains the Job Output (which is not as detailed). The file which contains the database tasks output is created by the Maintanance Plan (verses the Job) and has a date-timestamp as part of the file name. Therefore, how would you grab this ever-changing file name with sql code?
Thanks, Kevin
March 10, 2009 at 6:46 am
(Mohit), as far as writing the output to the same file, I am talking about the file that contains the database tasks (check integrity, update statistics, backups, etc) output. It sounds like you (Mohit) are talking about the file that contains the Job Output (which is not as detailed). The file which contains the database tasks output is created by the Maintanance Plan (verses the Job) and has a date-timestamp as part of the file name. Therefore, how would you grab this ever-changing file name with sql code?
Thanks, Kevin
March 10, 2009 at 7:05 am
Kevin,
Can you give us the details of the job step that sends out the mail and is supposed to include the attachment? Things like Step Type, the database it's running against and anything in the step window below that.
And going over your posts, I still don't see the part where you have actually checked the security for the folder which contains the attachment file. Have you checked this?
If so, is it a UNC share or a local drive on your box? What's the path you're using for the attachment? (sanitize the link)
Thanks,
March 10, 2009 at 8:43 am
Brandie, here are more details:
BTW, the folder has been shared and permissions given.
This is the dialog within the Maintenance Plan that creates the file I want attached:
Edit a Maintenance Plan and click the Reporting and Logging Icon at the top. The Reporting and Logging Dialog appears.
Select 'Generate a text file report' option.
Select 'Create a new file' option.
Enter: S:\Prod Backups\Reports
Select 'Send report to an email recipient' Option.
Select an 'Agent Operator'
(For Logging, I have checked 'Log extended information'. This is not related to my issue.)
The name of the text file being created:
S:\Prod Backups\Reports\MaintenancePlanKevinTest_Subplan_1_20090310094459.txt
The output stored in the file (above) is below. (Also this is the output that is now being sent within the email, not as an attached file.)
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4035 Report was generated on "TIPDB\TIPWEB".
Maintenance Plan: MaintenancePlanKevinTest
Duration: 00:00:10
Status: Succeeded.
Details:
Check Database Integrity (TIPDB\TIPWEB)
Check Database integrity on Local server connection
Databases: TipWeb_OriginalData
Include indexes
Task start: 2009-03-10T09:44:49.
Task end: 2009-03-10T09:44:52.
Success
Shrink Database (TIPDB\TIPWEB)
Shrink Database on Local server connection
Databases: TipWeb_OriginalData
Limit: 50 MB
Free space: 10 %
Task start: 2009-03-10T09:44:52.
Task end: 2009-03-10T09:44:52.
Success
Update Statistics (TIPDB\TIPWEB)
Update Statistics on Local server connection
Databases: TipWeb_OriginalData
Object: Tables and views
All existing statistics
Task start: 2009-03-10T09:44:53.
Task end: 2009-03-10T09:44:59.
Success
Remember, I am concerned about the file created within the Maintenance Plan not the file created (optional) within the Job Step. I want to be able to attach the file created within the Maintenance Plan.
Also, the SQL Server is using drives (S: and T:) which are located in an External Cabinet. The SQL Server sees the drives as internal drives but they (drives) are not physically stored inside the SQL Server. (I do not think this is an issue per our NW Group.)
Thanks for everyones help, Kevin
March 10, 2009 at 8:49 am
Okay. This is different from what I thought you were doing.
Check the plan history. Any errors or informational messages regarding the email part?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply