Attachment file is invalid - db mail
-
I am trying to set up a db_mail routine that will send multiple attachments out to a project manager to review their unapproved a/p invoices. When I run this command (i'll explain why the 2nd, and commented out, @file_attachments line in a second):
exec msdb..sp_send_dbmail @profile_name = 'mail account',
@recipients = 'gbankos@xxxxxxxxxxxxx.com',
--@copy_recipients = 'gbankos@xxxxxxxxxxxxxxx.com',
--@copy_recipients = 'lnelson@xxxxxxxxxxxxxx.com',
@subject = 'Invoices needing your approval',
@body = @body,
@body_format = 'HTML',
@execute_query_database = forefront,
@file_attachments=@images
--@file_attachments = '\\waccounting\ffwin\images\vendor\ap invoice\13085316.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085317.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085320.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085327.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085338.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085339.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085341.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085344.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085347.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085352.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13086164.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085353.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085358.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085361.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085362.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085365.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085366.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085368.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085369.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085370.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085396.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085437.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085447.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085451.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085473.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085561.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085349.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13086340.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13086350.TIF'
I get the following error:
Msg 22051, Level 16, State 1, Line 0
Attachment file '\\waccounting\ffwin\images\vendor\ap invoice\13086164.TIF is invalid.
If I hardcode in what the variable @images contains, which is what the 2nd @file_attachments line is, it runs fine. So if I run it with the @images variable, it errors out. If I run it with it hard-coded to contain what the @images variable contains, I'm okay. I've searched and haven't found a fix for this. I have the entire sql statement below and I'm sure there's a better way to come up with my sql, but that is a different issue entirely.
I thank you for your time in advance.
---------------------------------------------------------------------------
use forefront
CREATE TABLE ##ap_routing (vendor_name varchar(30),
invoice_number varchar(20), job_name varchar(25), invoice_amount decimal(9,2), image_file varchar(12))
insert ##ap_routing
select vend.Vendor_Name, inv.invoice_number, job.Job_Description,
case inv.invoice_type_code
when 'C'
then inv.Invoice_Amount * -1
else inv.invoice_amount
end as invoice_amount,
rtrim(images.filename) from vn_invoice_approval_hdr_mc inv (nolock)
inner join x_ap_invoice_images images (nolock)
on inv.Company_Code = images.company_code and inv.Vendor_Code = images.folder and inv.Invoice_Number = images.invoice_no and inv.Invoice_Type_Code = images.invoice_type
inner join VN_VENDOR_MASTER_MC vend (nolock)
on inv.Company_Code = vend.Company_Code and inv.Vendor_Code = vend.Vendor_Code
left outer join JC_JOB_MASTER_MC job (nolock)
on inv.Company_Code = job.Company_Code and inv.Subcontract_Job = job.Job_Number
where inv.Company_Code = 'wor' and Current_Routing_Operator = 'sw'
declare @images nvarchar(max)
set @images = ''''
declare @current_image varchar(12)
while (select COUNT(*) from ##ap_routing) > 0
begin
set @current_image = (select top(1) image_file from ##ap_routing)
if (select COUNT(*) from ##ap_routing) > 1
begin
set @images = @images + '\\waccounting\ffwin\images\vendor\ap invoice\' + @current_image + ';'
end
else
set @images = @images + '\\waccounting\ffwin\images\vendor\ap invoice\' + @current_image + ''''
delete from ##ap_routing where image_file = @current_image
end
insert ##ap_routing
select vend.Vendor_Name, inv.invoice_number, job.Job_Description,
case inv.invoice_type_code
when 'C'
then inv.Invoice_Amount * -1
else inv.invoice_amount
end as invoice_amount,
rtrim(images.filename) from vn_invoice_approval_hdr_mc inv (nolock)
inner join x_ap_invoice_images images (nolock)
on inv.Company_Code = images.company_code and inv.Vendor_Code = images.folder and inv.Invoice_Number = images.invoice_no and inv.Invoice_Type_Code = images.invoice_type
inner join VN_VENDOR_MASTER_MC vend (nolock)
on inv.Company_Code = vend.Company_Code and inv.Vendor_Code = vend.Vendor_Code
left outer join JC_JOB_MASTER_MC job (nolock)
on inv.Company_Code = job.Company_Code and inv.Subcontract_Job = job.Job_Number
where inv.Company_Code = 'wor' and Current_Routing_Operator = 'sw'
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( select vendor_name as 'td', '', job_name as 'td', '', invoice_number as 'td', '', invoice_amount as 'td', '', image_file as 'td'
from ##ap_routing order by image_file FOR XML PATH('tr'), ELEMENTS )
AS NVARCHAR(MAX))
SET @body ='<head>
<title> Embedded Logo Example</title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table>
<tr><td valign="top" align="left">
</td></tr>
<html><H5><p></H5>
<H2></H2><H5><p></H5>
This email is sent because you have invoices waiting for approval. This email lists all the invoices and the images are attached.
Please go into invoice approval and review your invoices.
Thank you for your time and attention.
<table border = "5"><tr><th>Vendor</th><th>Job</th><th>Invoice #</th><th>Invoice Amt</th><th>Filename</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
select @images
exec msdb..sp_send_dbmail @profile_name = 'mail account',
@recipients = 'gbankos@xxxxxxxxxxxxx.com',
--@copy_recipients = 'gbankos@xxxxxxxxxxxxxxxx.com',
--@copy_recipients = 'lnelson@xxxxxxxxxxxxxxx.com',
@subject = 'Invoices needing your approval',
@body = @body,
@body_format = 'HTML',
@execute_query_database = forefront,
@file_attachments=@images
--@file_attachments = '\\waccounting\ffwin\images\vendor\ap invoice\13085316.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085317.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085320.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085327.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085338.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085339.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085341.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085344.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085347.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085352.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13086164.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085353.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085358.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085361.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085362.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085365.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085366.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085368.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085369.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085370.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085396.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085437.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085447.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085451.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085473.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085561.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085349.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13086340.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13086350.TIF'
drop table ##ap_routing
-
First check permission on store where you getting file,
if you have permission try put N before files
@file_attachments = N'\\waccounting\ffwin\images\vendor\ap invoice\13085316.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085317.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085320.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085327.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085338.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085339.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085341.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085344.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085347.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085352.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13086164.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085353.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085358.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085361.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085362.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085365.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085366.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085368.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085369.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085370.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085396.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085437.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085447.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085451.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085473.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085561.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13085349.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13086340.TIF;\\waccounting\ffwin\images\vendor\ap invoice\13086350.TIF'
The size of attachment is limited to 1 MB per file. Check it in Database Mail configuration
-
All my files that I'm attaching are < 200k, but I did bump up the file size to 5mb. The fact that I can hardcode all these files and the email sends tells me I don't have a permission problem, yes? I put the N' in front and it still didn't work, but it did work when I hardcoded the @file_attachments line.
-
Not sure it matters but i noticed the following mismatch, NVARCHAR(MAX) for images and VARCHAR(12) for current_image
declare @images nvarchar(max)set @images = ''''
declare @current_image varchar(12)
The other issue i see is the use of global temp tables (##Temp) vs local temp tables (#Temp), If there are multiple instances of this proc running you will get a race condition where each proc is deleting from the same table as they run.
the other question is how are you generating your hard coded list? Are you running the loop to build @images and then returning the variable to the screen (SELECT @images)?
It is a weird issue. the other thing to do is to remove the entry for the offending file and then seeing if it would run using @images or if it errors on a different file at the same place in the string as the "Bad" file.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
Thanks for your time and pointing out the mismatch. I also changed the statement to local temp tables. I changed the mismatch, but that didn't resolve it.
Yes, I'm building the file listing this way:
declare @images nvarchar(max)
set @images = 'N'''
declare @current_image nvarchar(max)
while (select COUNT(*) from #ap_routing) > 0
begin
set @current_image = (select top(1) image_file from #ap_routing)
if (select COUNT(*) from #ap_routing) > 1
begin
set @images = @images + '\\waccounting\ffwin\images\vendor\ap invoice\' + @current_image + ';'
end
else
set @images = @images + '\\waccounting\ffwin\images\vendor\ap invoice\' + @current_image + ''''
delete from #ap_routing where image_file = @current_image
end
and I am doing a select @images right before the send_ dbmail command so I can see that I'm building the list right. Again, when I copy the output of that select statement and paste it into my @file_attachments line (replacing the @images with what @images contains), it works. It is baffling and I've searched for solutions and I haven't seen any given yet. I figured I would try posting again up on this board.
select @images
exec msdb..sp_send_dbmail @profile_name = 'mail account',
@recipients = 'gbankos@worthandcompany.com',
--@copy_recipients = 'gbankos@worthandcompany.com',
--@copy_recipients = 'lnelson@worthandcompany.com',
@subject = 'Invoices needing your approval',
@body = @body,
@body_format = 'HTML',
@execute_query_database = forefront,
@file_attachments=@images
-
Your issue is wrapping the @images values with single quotes.
You need to initialize @images to a blank string
set @images = ''and your else statement should not put a single quote at the end
set @images = @images + '\\waccounting\ffwin\images\vendor\ap invoice\' + @current_image -
That worked. So if you are doing a variable, you don't need the single quotes. When you are hardcoding it, you do need the single quotes.
Thanks for your time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply