How to place a file into a file directory.

Question

Post reply

How to place a file into a file directory.

hoseam

SSCertifiable

Points: 5533
More actions
June 2, 2016 at 12:08 am

#319229

Hi
I have this code, which is just a part of my code:
SET @Attachment = @FilePath + @ReportFileName
SELECT
@SQLCommand = 'BCP "SELECT * FROM ##!TableName!" QUERYOUT ' +
@FilePath + @ReportFileName + ' -c -t, -T -S' + @@SERVERNAME
EXEC MASTER..XP_CMDSHELL @SQLCommand
EXEC MASTER..xp_fileexist @Attachment, @FileExistsEmailFlag OUT
IF @FileExistsEmailFlag = 1
BEGIN
EXEC msdb..sp_send_dbmail
@recipients = @Recepients
,@copy_recipients = @cc
,@subject = @EmailSubject
,@file_attachments = @Attachment
,@body_format = 'HTML'
END
That send the attachment, however the files can be bigger. I need to rather place a file in the directory then send email that the file can be found that directory. please help.

Viewing 3 posts - 1 through 2 (of 2 total)

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

subramanian.esakkimuthu 40485 SSCrazy Points: 2022 More actions · Answer 1

where you kept the file in server particluar directory. and the path is stored in the database ?

if your size is bigger , how you are going compress

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

hoseam (6/2/2016)
Hi
I have this code, which is just a part of my code:
SET @Attachment = @FilePath + @ReportFileName
SELECT
@SQLCommand = 'BCP "SELECT * FROM ##!TableName!" QUERYOUT ' +
@FilePath + @ReportFileName + ' -c -t, -T -S' + @@SERVERNAME
EXEC MASTER..XP_CMDSHELL @SQLCommand
EXEC MASTER..xp_fileexist @Attachment, @FileExistsEmailFlag OUT
IF @FileExistsEmailFlag = 1
BEGIN
EXEC msdb..sp_send_dbmail
@recipients = @Recepients
,@copy_recipients = @cc
,@subject = @EmailSubject
,@file_attachments = @Attachment
,@body_format = 'HTML'
END
That send the attachment, however the files can be bigger. I need to rather place a file in the directory then send email that the file can be found that directory. please help.

You have the file path and the file name. Concatenate them together along with whatever text you wish to include and use it as the body of the email. Of course, you can take out all references to the attachment.

--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)