sp_send_dbmail

• Anchelin

  SSCrazy

  Points: 2417

  More actions

  September 21, 2011 at 4:13 am

  #248173

  Hi All

  I need to sent reports via Database Mail to specific people.

  My scripts looks like the following; below script rename and move to another directory (they want to have the date attached to file):

  DECLARE

  @TodayDate as varchar(40),

  @NewFileName as varchar(100),

  @cmdstr as varchar(128),

  @files as varchar(128)

  SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)

  SELECT @NewFileName = 'File1' + '_' + @TodayDate +'.csv'

  --print @NewFileName

  --set @files = 'DEL /Y E:\MSSQL\GWork\BCOM\BCOM_test\BCOM_.*'

  --EXEC master..xp_cmdshell 'DEL E:\MSSQL\GWork\BCOM\BCOM_test\*.csv'

  set @cmdstr='COPY /Y E:\MSSQL\GWork\BCFile1.csv E:\MSSQL\GWork\BCOM\' + @NewFileName

  --print @cmdstr

  --EXEC master..xp_cmdshell @files

  EXEC master..xp_cmdshell @cmdstr

  Below script must email the recipients:

  SET NOCOUNT ON

  DECLARE

  @JobName VARCHAR(100),

  @Bodytext VARCHAR(200),

  @SubjectText VARCHAR(200),

  @Servername VARCHAR(50)

  SELECT @JobName = ' Reports'

  SELECT @Servername = @@servername

  SET @bodytext = 'Reports run at '+CONVERT(VARCHAR(20),GETDATE(),100)

  SET @subjecttext = @Servername+' : '+@jobname+ ' run at '+CONVERT(VARCHAR(20),GETDATE(),100)

  -- send out email

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'Sserver_Mail', --<<< insert your Mail Profile here

  @recipients = 'manager@somehwere.co.za',

  @blind_copy_recipients = 'Me@something.co.za',

  @body = @bodytext,

  @subject = @subjecttext,

  @file_attachments = 'E:\MSSQL\GWork\File1_20110921.csv;E:\MSSQL\GWork\File5_20110921.csv;E:\MSSQL\GWork\File2_20110921.csv;E:\MSSQL\GWork\File3_20110921.csv;E:\MSSQL\GWork\File4_20110921.csv'

  GO

  But when I run this script the next day it wont work; as tomorrows date will be 20110922.

  How can I overcome this?

  Thanks in advance.

  A

• NJ-DBA

  SSChampion

  Points: 13834

  More actions

  September 21, 2011 at 8:44 am

  #1384543

  Anchelin (9/21/2011)

  ---

  Hi All

  I need to sent reports via Database Mail to specific people.

  My scripts looks like the following; below script rename and move to another directory (they want to have the date attached to file):

  DECLARE

  @TodayDate as varchar(40),

  @NewFileName as varchar(100),

  @cmdstr as varchar(128),

  @files as varchar(128)

  SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)

  SELECT @NewFileName = 'File1' + '_' + @TodayDate +'.csv'

  --print @NewFileName

  --set @files = 'DEL /Y E:\MSSQL\GWork\BCOM\BCOM_test\BCOM_.*'

  --EXEC master..xp_cmdshell 'DEL E:\MSSQL\GWork\BCOM\BCOM_test\*.csv'

  set @cmdstr='COPY /Y E:\MSSQL\GWork\BCFile1.csv E:\MSSQL\GWork\BCOM\' + @NewFileName

  --print @cmdstr

  --EXEC master..xp_cmdshell @files

  EXEC master..xp_cmdshell @cmdstr

  Below script must email the recipients:

  SET NOCOUNT ON

  DECLARE

  @JobName VARCHAR(100),

  @Bodytext VARCHAR(200),

  @SubjectText VARCHAR(200),

  @Servername VARCHAR(50)

  SELECT @JobName = ' Reports'

  SELECT @Servername = @@servername

  SET @bodytext = 'Reports run at '+CONVERT(VARCHAR(20),GETDATE(),100)

  SET @subjecttext = @Servername+' : '+@jobname+ ' run at '+CONVERT(VARCHAR(20),GETDATE(),100)

  -- send out email

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'Sserver_Mail', --<<< insert your Mail Profile here

  @recipients = 'manager@somehwere.co.za',

  @blind_copy_recipients = 'Me@something.co.za',

  @body = @bodytext,

  @subject = @subjecttext,

  @file_attachments = 'E:\MSSQL\GWork\File1_20110921.csv;E:\MSSQL\GWork\File5_20110921.csv;E:\MSSQL\GWork\File2_20110921.csv;E:\MSSQL\GWork\File3_20110921.csv;E:\MSSQL\GWork\File4_20110921.csv'

  GO

  But when I run this script the next day it wont work; as tomorrows date will be 20110922.

  How can I overcome this?

  Thanks in advance.

  A

  why not use the exact same code you use to build the copy string you will use in @file_attachments...

  for example

  delcare @futureattachments VARCHAR(250),

  set @futureattachments= @NewFileName+';'

  do this as you loop through the files in the copy part, then in the email part:

  @file_attachments=@futureattachments

  something like that should work.