xp_sendmail with an Attachment, Sometimes....
-
I am using xp_sendmail to email people images as attachments. The problem I run into is that the email completely fails if that specific image is not available in the directory. If there is not an image, I would like to use a different email message saying not available.
Is there a way to verify a file path before using it as an attachment in SQL?
Thank you for your time.
-
This was removed by the editor as SPAM
-
I think you should use a stored procedure for this and use xp_cmdshell to check existence of the file.
I'm trying something similar right now and it appears that xp_sendmail cannot use UNC paths for file attachements
-
/******************************************************************************************************
** Check to see if a file exists
******************************************************************************************************/
DECLARE @chkfile INT
DECLARE @exists BIT
EXEC @chkfile = sp_MSget_file_existence 'C:\boot.ini', @exists = @exists output
IF @exists = 1 -- The file exists
PRINT 'File exists'
ELSE
PRINT 'File does not exist'
-
Hi,
Can you tell me where I should write the code in a package? When I tried writing it in SQL task, it throws some error stating syntax error. Please help.
Regards,
Vidhya
-
Hi,
It should be fine in a SQL task! can you paste teh full error message for us. Have you googled the error message?
-
Hi Wildh,
this is the code I've written in hte SQL task.
DECLARE @chkfile INT
DECLARE @exists BIT
EXEC @chkfile = sp_MSget_file_existence 'C:\Documents and Settings\144022.CTS\My Documents\timesheet_list.xls', @exists = @exists output
IF @exists = 1 -- The file exists
drop table timesheet
drop table defaulter
ELSE
PRINT 'File does not exist'
got hte following error while doing a parse
Invalid SQL statement:expected 'delete' 'insert' 'procedure' 'select' or 'update'
Actually for hte first time the records are inserted into excel perfectly. If I run it again, the data got appended in the excel.
Regards,
Vidhya
-
Try this
IF @exists = 1 -- The file exists
begin
drop table timesheet
drop table defaulter
end
ELSE
begin
PRINT 'File does not exist'
end
instead of
IF @exists = 1 -- The file exists
drop table timesheet
drop table defaulter
ELSE
PRINT 'File does not exist'
you need to wrap multiple line IF condition statements in a begin..end. It was seeing the ELSE statement as it's own statement, not a continuation of the IF structure
Gregory Culler
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply