problems with attachments field in xp_sendmail 1

[MaffewW]

Hi all

I'm having problem with attachments field in xp_sendmail.


I am trying to attach 3 x files from the previous day which are generated automatically from a DTS package, so the files have a ddmm appended.

Error message

Server: Msg 17912, Level 18, State 1, Line 0
Attachment open failure.

The attachments are not already open somewhere else, sending them individually works. Originally the @fileattachments was one big parameter, though I broke it up into 3 x files to try and track where I was going wrong. I have tried every combination of 2 of the 3 files which works ok, but when I send all 3 I get the error. I have also successfully sent 2 of the files and a 3rd document (a test notepad file). Can anyone suggest what else to try



declare @cancellation varchar(200)
declare @enquiry varchar(200)
declare @manual varchar (200)
declare @sendto varchar (500)
declare @messagetxt varchar (250)
declare @subjecttxt varchar (250)
declare @fileattachments varchar (500)
declare @cc varchar (250)
set @cancellation = '\\austen\Documents\reports\ALife_inbound\exportfiles\exports'
+ replace(convert(varchar,getdate()-1,03),'/','') + '\RPTexportcancellations'
+ left(replace(convert(varchar,getdate()-1,03),'/',''),4) + '.csv;'
set @enquiry = '\\austen\Documents\reports\ALife_inbound\exportfiles\exports'
+ replace(convert(varchar,getdate()-1,03),'/','') + '\RPTexportgeneralenquiries'
+ left(replace(convert(varchar,getdate()-1,03),'/',''),4) + '.csv;'
set @manual = '\\austen\Documents\reports\ALife_inbound\exportfiles\exports'
+ replace(convert(varchar,getdate()-1,03),'/','') + '\Without Policy Details '
+ left(replace(convert(varchar,getdate()-1,03),'/',''),4) + '.xls;'

set @sendto = 'mwilson@myemail.com'
set @messagetxt = 'attached are yesterdays cancellation and general enquiry files. Pls email mwilson.@telegenuk.com in the event of any problems with this email or the files attached


Regards'

set @subjecttxt = 'Cancellation/General Enquiry Files for ' + convert(varchar,getdate()-1,106)
set @cc = 'matt wilson'
set @fileattachments = @cancellation + @manual + @enquiry




EXEC bronte.master.dbo.xp_sendmail
@recipients = @sendto,
@copy_recipients = @cc,
@subject = @subjecttxt,
@attachments = @fileattachments,
@message = @messagetxt



Cheers




Matt

Brighton, UK

 

[Sabnac]

Delete the last semicolon

set @manual = '\\austen\Documents\reports\ALife_inbound\exportfiles\exports'
+ replace(convert(varchar,getdate()-1,03),'/','') + '\Without Policy Details '
+ left(replace(convert(varchar,getdate()-1,03),'/',''),4) + '.xls'

 

[MaffewW]

thanks for reply sabnac, however thats what I thought at first though it doesnt seem to mind having a semicolon at the end.

I just tried again these 3 x combinations

@fileattachments = @cancellation + @manual
@fileattachments = @cancellation + @enquiry
@fileattachments = @enquiry + @manual

and they all send fine. however when I try

set @fileattachments = @enquiry + @cancellation + @manual

in various combinations I still get the error message

Server: Msg 17912, Level 18, State 1, Line 0
Attachment open failure.


Matt

Brighton, UK

 

[Sabnac]

Maybe @fileattachments is truncated (200+200+200 = 600)
Set @fileattachments varchar((600)

 

[MaffewW]

no, thanks again, though I think I've sorted it. I checked the length of the string and it was 297. I think the @attachments parameter must be defined as varchar(255) or something , as when I shared the folders at a higher level

ie

\\austen\Documents\reports\ALife_inbound\exportfiles
is now shared as

\\austen\alifeexport
and the length of the string is down to 202 it works ok. So it seems there is a limit on the number of attachments you can send with xp_sendmail depending on the filepaths of the attachments

Cheers


Matt

Brighton, UK

 

[Sabnac]

i just test a mail with an attachement greater than varchar(700) with n times the \\issrv001\ftp_data_ares\ares\ft kms\out\KMS.zip file attached.

It works. Just do a print of @fileattachment before executing the xp_sendmail to be sure of the content.