Please help ... Attaching all files in a folder to an e-mail

Question

Please help ... Attaching all files in a folder to an e-mail

Skypass

Old Hand

Points: 327
More actions
November 25, 2013 at 1:50 pm

#279398

Hi,
How do I attach all files in a network folder to an e-mail (without zipping) using sp_send_dbmail.
The names and number of files in the folder will keep changing. Is there a simple way?
Any help will be greatly appreciated ....

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

Skypass (11/25/2013)
Hi,
How do I attach all files in a network folder to an e-mail (without zipping) using sp_send_dbmail.
The names and number of files in the folder will keep changing. Is there a simple way?
Any help will be greatly appreciated ....

it depends on the implementation; this is via a stored procedure or TSQL script, i assume?

I would recommend a CLR like this, which can be used to list the files in a folder.

http://nclsqlclrfile.codeplex.com/

--MFGetDirectoryList

-- Parameters: @Directory

-- purpose: given a string containing a path returns a three column results table with path,name and fullfilename and path

-- usage:

SELECT * FROM dbo.MFGetDirectoryList('C:\Data\')

with those results, you could loop to add each file.

it's possible to do the same with xp_cmdshell as well, but CLR is the recommended way to go nowadays.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Skypass Old Hand Points: 327 More actions · Answer 2

Thank you for the help.

Actually, below is the code I have at the moment (will look at CLR later when I have a chance)... Need to get it to work in a hurry, but not sure how to throw in a loop here to get a list of file names to attach. Right now, it goes out as multiple e-mails with single attachment each.

------------------------------------------------------------

CREATE TABLE #files(File_Name varchar(200), depth int, fil int)

INSERT #files

EXEC master.dbo.xp_dirtree '\\servername\SalesRpts\',1,1

declare @counter int,

@directory varchar(5000),

@sub varchar(1000)

If (select count(*) from #files)> 0

Begin

Declare Readrecord cursor

Local

For

select [Directory]= '\\servername\SalesRpts\'+[file_name], subject = 'Sales by Territory'

from #files

select @counter = 1

Open ReadRecord

Fetch next From ReadRecord into @directory,@sub

While (@@fetch_status=0)

Begin

exec msdb.dbo.sp_send_dbmail

@profile_name = 'Administrator',

@recipients = ''

@copy_recipients = '',

@blind_copy_recipients = '',

@subject = @sub,

@file_attachments = @directory;

Fetch next From ReadRecord into @directory, @sub

End

Close ReadRecord

Deallocate ReadRecord

drop table #files

End

Lowell SSC Guru Points: 323547 More actions · Answer 3

sorry, no loop required;

you need to create a semicolon delimited list of the files, including the full path.

this worked for me:

CREATE TABLE #files(File_Name varchar(200), depth int, fil int)

INSERT #files

EXEC master.dbo.xp_dirtree 'C:\Bak\',1,1

DECLARE @fileAttachments varchar(max)

SELECT @fileAttachments = s.Filez

FROM(SELECT

Filez = STUFF((SELECT ';' + 'C:\Bak\' + [File_Name]

FROM #files

ORDER BY [File_Name]

FOR XML PATH('')

),1,1,'')

) s

from there, you can set the parameter @file_attachments = @fileAttachments .

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Skypass Old Hand Points: 327 More actions · Answer 4

Hi, Lowell:

I just tried your code and it works great! Thanks a million!!!

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 5

If you don't need the file details like the date created or size, then you can use xp_DirTree to get a list of the files instead of a CLR. Use INSERT/EXEC to drop the file names into a Temp Table for use. Then, use concatenation code as Lowell suggested.

--===== Create a place to store the results of xp_Dirtree

CREATE TABLE #Files

(

RowNum INT IDENTITY(1,1)

,ObjectName VARCHAR(500)

,Depth TINYINT

,IsFile BIT

)

;

--===== Use xp_Dirtree to get the file names

-- and store them in a table.

INSERT INTO #Files

(ObjectName, Depth, IsFile)

EXEC xp_DirTree 'C:\Temp',1,1

;

--===== Replace the following with code similar to Lowell's

SELECT * FROM #Files WHERE IsFile = 1

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)