Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Attachment file is invalid - db mail Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 12:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 06, 2013 6:07 AM
Points: 42, Visits: 97
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


Post #1505360
Posted Wednesday, October 16, 2013 2:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:40 AM
Points: 58, Visits: 310
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
Post #1505420
Posted Thursday, October 17, 2013 6:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 06, 2013 6:07 AM
Points: 42, Visits: 97
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.
Post #1505649
Posted Thursday, October 17, 2013 9:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
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 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

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1505767
Posted Thursday, October 17, 2013 9:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 06, 2013 6:07 AM
Points: 42, Visits: 97
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
Post #1505778
Posted Friday, October 18, 2013 1:31 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
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





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1506005
Posted Friday, October 18, 2013 6:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 06, 2013 6:07 AM
Points: 42, Visits: 97
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.
Post #1506111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse