CODEWITHSUNDEEP
sqlsql-server
Regon
Regon

Reputation: 392

Send a Mail from SQL server with attachment ( Attachment to be formed dynamically)

I have to send the report to management every morning . For that i have create schedule in Sql Server agent with following code. 

EXEC msdb.dbo.sp_send_dbmail
@recipients='[email protected]',
@body='Dear sir, <Br>Please find the attachment. <P>Regards<Br> <Br>IT Department', 
@subject ='TOURISM-GL( Auto By System) ',
@body_format = 'html',
@profile_name = 'emailserver',
@file_attachments='C:\PUMORI_NEW\**001_TOURISMGL_(14072014)_(SOD).TXT**'

Now the problem is, the file which i need to send as attachment will generate every day with new name. File name will be in format

001_TOURISMGL_(14072014)_(SOD).TXT

In above file name only the date value will be change. The date will be in ddmmyyyy format.

Now Kindly suggest me how can i achieve this. How send mail automatically with attachment.

Upvotes: 0

Views: 4999

Answers (3)

Gary Walker
Gary Walker

Reputation: 9134

Is there any reason you can't use the built in command, xp_sendmail

It have just about every email option you are likely to want. It is sql 2005 or 2008. Older versions of sql, no such features, so people tended to use CDONTS (also by MS), but I would stay away from CDONTS if you have SQL 2005 or higher.

ADDED (accidentally posted prematurely)

In fact, if you have 2008 or above, you should not use xp_sendmail either (ha, misdirection) -- The new solution is Database mail as documented here This uses the stored proc xp_send_dbmail() -- note that this is NOT enabled by default, but the article tells you how by using the DataBase Mail Configuration Wizard

Upvotes: 1

Jay
Jay

Reputation: 2200

Could you try,

declare @pathname varchar(200) = 'C:\PUMORI_NEW\**001_TOURISMGL_(,' + REPLACE(CONVERT(VARCHAR(10),GETDATE(),101),'/', '') + ', )_(SOD).TXT**';

EXEC msdb.dbo.sp_send_dbmail
@recipients='[email protected]',
@body='Dear sir, <Br>Please find the attachment. <P>Regards<Br> <Br>IT Department', 
@subject ='TOURISM-GL( Auto By System) ',
@body_format = 'html',
@profile_name = 'emailserver',
@file_attachments=@pathname

Upvotes: 1

Leptonator
Leptonator

Reputation: 3499

Does it have to be inside of SQL Server? I do it with a batch file using BLAT (or Postie) with Windows Task Scheduler and SQLCMD (OSQL works too) and works just fine..

@ECHO OFF
@Rem -----------------------------------------------
@ECHO Procedure to e-mail daily Row Counts
C:
cd\reports

@ECHO Running database query, please wait
if not exist COUNTS.log osql -Sserver\instance -Uyouruser -Pyourpass -n    -iCOUNTS.sql     -oCOUNTS.log -w250

@rem ------------- Mail out report
if exist COUNTS.log Postie.exe -host:smtp.yourcompany.com -to:[email protected]     -from:[email protected] -s:"Quick Counts for Session/Transaction/User" -file:COUNTS.log -msg:"Please reference below for the row counts for Session/Transaction/Users for DB1/DB2/DB3 Databases. This message was sent from %COMPUTERNAME%"

@for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set dirdate=%%a.%%b.%%c
@if exist COUNTS.log copy COUNTS.log LogBkup\COUNTS-%dirdate%.log
@if exist COUNTS.log del COUNTS.log
rem @exit

Upvotes: 1

Related Questions