Passed a file to a stored procedure and would like to e-mail as attachment

Hi all,
    There is a stored proc that has a parameter of type image (this parameter is streamed in using ADO).  They are small PDF's.  The files are inserted into a table.  No problem.

    The proc then creates an e-mail and sends out the file as an attachment.  I ended up having to use the TextCopy command to output the file from the table to a file on the hard drive before I could attached it.  

    My question is this:  Is is possible to attach the file without first writing it to the hard drive?  If not, I'm worried about concurrency issues with the procedure using a static path and file name.

    Here is the procedure:

------------------------------------------
ALTER           PROCEDURE pfrmProcessPayrollIndividualStoreAndEmailPDF
(
      @intPID                        INT                      =1,
      @datStartDate                  DATETIME                      ='10-28-2006',
      @datEndDate                  DATETIME                      ='11-04-2006',
      @strDatabaseReportName                      VARCHAR(50)      ='Testing',
      @strReportCategory                            VARCHAR(50)      ='Payroll',
      @strEmail                        VARCHAR(50)      ='Testing@Testing.com',
      @imgFile                        IMAGE            =0x0,
      @intFileSize                  INT                      =0,
      @strFileName                  VARCHAR(50)      ='Testing.pdf',
      @strPW                        VARCHAR(20)            ='Testing'
)
AS

DECLARE @strSubject            NVARCHAR(100)
DECLARE @strExec            VARCHAR(500)
DECLARE @strSSFileName      VARCHAR(50)

--Step 1, Insert data into the table
INSERT INTO tblEmployeeEmailedReports
                  (PID, StartDate, EndDate, EmailAddress, DatabaseReportName, ReportCategory, FileImage, FileSize, FileSystemName)
      VALUES (@intPID, @datStartDate, @datEndDate,@strDatabaseReportName,@strReportCategory,@strEmail,@imgFile,@intFileSize,@strFileName)


--Step 2,  Save the image to the file system
SELECT @strExec = 'C:\Progra~1\Micros~1\MSSQL\Binn\textcopy.exe /S /U ' + SYSTEM_USER
SELECT @strExec = @strExec + ' /P ' + @strPW + ' /D DatabaseName /T tblEmployeeEmailedReports /C FileImage '
SELECT @strExec = @strExec + '/W "WHERE EmailReportID=' + CAST(@@IDENTITY AS VARCHAR) + '" /F c:\Payroll.pdf /O'
--SELECT @strExec

EXEC master..xp_cmdshell @strExec


--Step 3, Send the report via e-mail
SET @strSubject = N'Payroll report from ' +
      cast(datepart(mm, @datStartDate) as nvarchar(2)) + '-' +
      RIGHT('0' + cast(datepart(dd, @datStartDate) as nvarchar(2)),2) + '-' +
      cast(datepart(yyyy, @datStartDate) as nvarchar(4))
      + ' To ' +
      cast(datepart(mm, @datEndDate) as nvarchar(2)) + '-' +
      RIGHT('0' + cast(datepart(dd, @datEndDate) as nvarchar(2)),2) + '-' +
      cast(datepart(yyyy, @datEndDate) as nvarchar(4))

SET @strSSFileName = N'C:\Payroll.pdf'
            EXEC master.dbo.xp_smtp_sendmail
                        @TO = @strEmail,
                        @FROM = N'Testingr@Testing.com',
                           @subject = @strSubject,
                           @message = N'Please find the attached report.',
                        @server = N'MySMTPServer,
                        @attachments = @strSSFileName,
                        @port = 25
--------------------------
Thanks in advance for your help and thoughts.

Mike

   Start Free Trial