xp_sendmail with an Attachment, Sometimes....

• TML-139925

  SSC Enthusiast

  Points: 113

  More actions

  May 4, 2004 at 10:32 am

  #359637

  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.

• May 7, 2004 at 8:00 am

  This was removed by the editor as SPAM

• Mr.No

  Old Hand

  Points: 390

  More actions

  May 18, 2004 at 9:56 am

  #506813

  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

• wildh

  SSCarpal Tunnel

  Points: 4370

  More actions

  May 19, 2004 at 6:38 am

  #506930

  /******************************************************************************************************

  ** 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'

• vidhya.raghav21

  Grasshopper

  Points: 22

  More actions

  November 20, 2007 at 10:48 pm

  #752955

  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

• wildh

  SSCarpal Tunnel

  Points: 4370

  More actions

  November 21, 2007 at 1:17 am

  #752971

  Hi,

  It should be fine in a SQL task! can you paste teh full error message for us. Have you googled the error message?

• vidhya.raghav21

  Grasshopper

  Points: 22

  More actions

  November 21, 2007 at 4:09 am

  #753024

  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

• gculler

  Grasshopper

  Points: 17

  More actions

  November 21, 2007 at 6:28 am

  #753068

  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