January 15, 2002 at 9:24 am
I tried using xp_sendmail to send an attachment and keep getting "attachment open failure" (not "not found"). Any ideas? The file name is accurate...
This is the code:
Declare @MyRecipients varchar (255)
Declare @MySubject varchar (60)
Declare @Fmly_cd varchar (3)
Declare @Fmly_desc varchar (50)
Declare @MyAttachment varchar (130)
Declare @FileLocation varchar (100)
Declare @MyMessage varchar (2000)
Set @MyMessage = 'This is to notify you of price discrepancies found on EDI Purchase Orders recently transmitted.
'
Set @MySubject = 'EDI Pricing Exception Notice'
Set @FileLocation = '\\Philli1bca1\Data Warehouse Reports\EDI Pricing Error Reports\'
Declare MyCursor Cursor For
SELECT DISTINCT dbo.tbl_FMLY.FMLY_DESC,
(@FileLocation + dbo.tbl_FMLY.FMLY_DESC+ '.pdf') ,
tbl_EDI_NOTIFY_ADDRESS.EMAIL_ADDR
FROM dbo.tbl_FMLY INNER JOIN
tbl_EDI_NOTIFY_ADDRESS ON
dbo.tbl_FMLY.FMLY_CD = tbl_EDI_NOTIFY_ADDRESS.FMLY_CD
INNER JOIN
dbo.tbl_CUST ON
dbo.tbl_FMLY.FMLY_CD = dbo.tbl_CUST.FMLY_CD INNER JOIN
tbl_EDI_PO_LINE_EXCPT ON
dbo.tbl_CUST.CUST_SEQ = tbl_EDI_PO_LINE_EXCPT.CUST_SEQ
WHERE tbl_EDI_PO_LINE_EXCPT.NOTICE_STTUS_CD = 'P'
Open MyCursor
Fetch Next From MyCursor Into @Fmly_cd, @MyAttachment,
@MyRecipients
While @@Fetch_Status = 0
BEGIN
Print @MyRecipients + ' ' + @MyAttachment
Exec Master.dbo.xp_sendmail @recipients=@MyRecipients,
@message=@MyMessage,
@subject=MySubject,
@attachments = @MyAttachment
Fetch Next From MyCursor Into @Fmly_cd, @MyAttachment,
@MyRecipients
End
Close MyCursor
Deallocate MyCursor
Edited by - sessegolo on 01/15/2002 09:36:33 AM
January 15, 2002 at 9:27 am
Sounds like permissions or path may be inaccurate. If you post the code that would be helpful. Thanks.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 15, 2002 at 3:49 pm
Could you be truncating some of the data from @MyAttachment???
Also try to verify that sql has access to the file by executing:
master..xp_getfiledetails 'Your file path here'
Just to check that your Sp can find the Path.
Let us know.
Tom Goltl
January 16, 2002 at 9:58 am
DUH David! Sorry I didn't catch this before but your path to the file has spaces in it and that won't fly. You will have to put quotes around the path so that it can find the file. Sorry for not catching this earlier.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 16, 2002 at 10:43 am
Hey guys, thanks for the help... It seems it was really a permissions issue. The spaces in the path didn't seem to be a problem - It works!
January 16, 2002 at 2:02 pm
Should have stuck with my gut feeling at first. Thanks for letting us know what the problem was.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply