Sending Attachments by mail thru a SQL Job
-
Our project has a requirement where in the SQL Server 2000, we have a scheduled job to send emails with attachments to the users of the application . The data pertaining to the email ids is stored in the database.
Requirement:
We have a procedure, which is scheduled as a JOB in SQL server. This procedure first generates 4 files using bcp and these files need to be mailed as attachments. The part that handles file generation is already in place. What we need is the sending of these files through mails.
What we have tried:
We have configured a SMTP client in the SQL Server system and created a profile that SQL server should use to send mails.
The SQL Server is expected to recognize this profile and use this profile to send mails. But when we tried , SQL Server hasn't recognized this profile.
Thanks in advance.
-
quote:
We have configured a SMTP client in the SQL Server system and created a profile that SQL server should use to send mails.The SQL Server is expected to recognize this profile and use this profile to send mails. But when we tried , SQL Server hasn't recognized this profile.
If you log onto the SQL Server at the console, using the login ID that SQL Server uses, can you connect to your mail system using the profile you created?
Thomas Rushton
blog: https://thelonedba.wordpress.com -
What do you mean when you say you've 'configured a SMTP client in the SQL Server System'. Is this a third-party addition to the regular SQL Mail?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I am the Local Admin of the Box. SQL Server has started under the Local System Account while the SQLServerAgent has started under the Domain Account.
In Management\SQL Server Agent, I have added an Operator with a valid email-id (profile). I have a job which upon successful completion sent a notification to this profile. The notification works without any problem.
When I go to Support Services\SQL Mail & click on properties, I am unable to see the profile. And when I manually, enter the profile test and click on Test, the following error message is displayed:
Error 18025: xp_test_mapi_profile : failed with mail error 0x80040111
-
You may need to have SQL Server start under the same domain account as SQLServerAgent. Also create the profile while logged in the SQL Server machine with the starting domain account.
Joseph -
Yes, in order for SQL Mail to work you will need to use the domain account. This is because the LocalSystem account has no authentication to use Network resources. Also, SQL Agent Mail and SQL Mail are two seperate things. You can even use different profiles for each.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
Made the necessary changes to have SQL Server start under the same domain account as SQLServerAgent.
Now I am able to view the mail profile in the SupportServices/SQLMail.
xp_sendmail works fine now.
exec master..xp_sendmail @recipients='test@yahoo.com',
@subject='Mail Test', @attachments='c:\temp\sysfiles.txt;c:\temp\sysusers.txt'
Thanks a Lot
-
Here is a script from a job that I run as a step after I generate the report and bcp it to our ftp site, I also have to email it to different people. We have mail set up on one of our servers and use that (as a linked server) to do all of our emails.
declare @temp varchar(250)
set @temp = '\\UssWeb1\CompanyName\Weekly_Handset_AD'+ REPLACE(CONVERT(char(10),getdate(),102),'.','')+'.csv'
exec fastcarddb1.master..xp_sendmail
@recipients= 'user1@company1.net;user2@company2.com',
@copy_recipients='jcampbell@mycompany.net',
@subject='Weekly Handset A-D Report ',
@message='The weekly Handset report is enclosed as an attachment.',
@attachments = @temp
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply