May 30, 2006 at 7:29 am
Hi All
I want to mail my report in the format of excel format to my client.
in my DTS package i made two connections one for SQL one for Excel.
ActiveXScript for mailing.
Actually i am trying to make first excel spreed sheet then with script trying to mail it.
I made my pacakage according this article.
http://www.sqlservercentral.com/columnists/jsack/capturingtheerrordescriptioninastoredprocedure.asp
I need to know how i can configure mail server?
Please let me know what else i can do for mailing Reports directly to client.
Many Thanks
May 30, 2006 at 8:24 am
SMTP is probably the best option for you. I use it and it is very reliable. See this link:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=272242#bm272533
and this link:
http://www.sqldev.net/xp/xpsmtp.htm#Description
May 31, 2006 at 12:01 am
You could do this way as well:
- create a DTS package that should generate .XLS file as an output.
- create a job where you to call the above DTS and script for sending an eMail. you can try xp_smtp_sendmail SP to send an email: Here is a smaple for your ref can be placed in job task
exec master..xp_cmdshell 'dtsrun /S "sqlserver" /U "sa" /P "password" /N "DTSpackagename"'
---------- send mail ---------------
exec @rc = master.dbo.xp_smtp_sendmail
@server = N'yourmailserver',
@FROM = N'youremaild@yahool.com',
@TO = N'emailaddress@@xyz.com',
@subject = @subject,
@type = N'text/plain',
@attachment= N'drive:\path\yourXLSfilename.xls'
Ahmed J. Koya
May 31, 2006 at 7:55 am
Vandy,
We do this daily using the Execute SQL task in a DTS package to call a stored procedure which sends the email with xp_sendmail. We maintain email addresses for a variety of processes in a table, and open a cursor in the stored procedure to select the particular recipients we want, and then use xp_sendmail in the cursor. This is in SQL 2000 (works in 7.0 also). You have to have Outlook on the server. I haven't yet figured out what I will have to do with all these jobs in SQL 2005.
This is better than using the Send Mail task in the DTS package, because in that case when you edit the DTS package on your workstation it picks up the email profile on the workstation, and you have to open and save the task on the server to save the email profile of the server, which is a pain.
Holly
June 2, 2006 at 6:45 am
Thanks A lot
February 15, 2008 at 11:33 am
having a similar situation. using a SQL task inside a DTS pkg to send attachment with xp_smtp_sendmail. getting successful output msgs but no email is being sent? how do i see what's happening? tx in advance.
February 17, 2008 at 3:23 pm
I have a generic email package called by other packages which utilises CDO (Common Data Objects) to send the email.
The activex scripts are attached.
The first script checks all the steps in the package for failure. It then loads and executes a generic email package, setting the Global variables for the package according to the completion status.
The second script is the generic email package. It is well documented to explain the SMTP servers etc that need to be configured.
🙂 What gets us into trouble is not what we don't know, it's what we know for sure that just ain't so....Mark Twain
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply