Msg 22051 Database Mail Attachment file is invalid

_{Thursday, March 5, 2015 12:38 AM}

Hello,

I'm testing a trigger to output a text file and then attach it to an email in Database Mail. So far, I've gotten the correct text file to export to a TEMP folder, but when I try to attach it to database mail, I get the message below. I think it is a permissions problem with the TEMP directory, but I've tried adding some permissions, but nothing seems to work. The profile name is TFRONT_DBMAIL and the account name is Albert using Basic Authentication for the SMTP server. I think you have to add the SQL Server User Account or the Database Mail account to the permissions, but I can't seem to find the exact one to use. I have set up other triggers that use database mail that only send messages with no attachments and they work fine.

Any help appreciated!

Albert

Msg 22051, Level 16, State 1, Line 0
Attachment file "C:\TEMP\NTCDManifestInv135255.txt" is invalid.

Albert S

_{Thursday, March 5, 2015 9:51 PM ✅Answered}

did you try what I suggested above.

I tired your script on my box and it was erroring because of quotes around the path.

try removing the char(34) the fullname variable and test it out.

Hope it Helps!!

_{Thursday, March 5, 2015 3:40 AM}

Hello,

Does the SQL Server service account have access to that temp folder?

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

_{Thursday, March 5, 2015 4:46 AM}

Is that a local folder path? If not you should specify it in UNC format.

Also account executing the code needs to have access to path and file

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Thursday, March 5, 2015 5:12 AM}

Hello ,

In you query, Is the attachment path contain share folder name like @file_attachments=N'\sharefolder\Testing.txt' ?

If that use drive letter . Have checked that it's not a antivirus impact ? 

Other wise please share your SQL Statement

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

_{Thursday, March 5, 2015 8:47 PM}

Hello, I moved the temp file here:

DECLARE

@PathVARCHAR(200)='C:\Users\MSSQLSERVER\Temp\

SQL Server service account (MSSQLSERVER) has full access to that folder. Still getting this message:

Msg 22051, Level 16, State 1, Line 0

Attachment file "C:\Users\MSSQLSERVER\Temp\NTCDManifestInv135255.txt" is invalid.

The file is created and it is in the Temp directory. It just won't attach. Thank you for any help! Below is the full statement.

Albert

DECLARE @CustomerID varchar(10) = 'NTCD'
DECLARE @EmailBody varchar(1000)
DECLARE @CustomerName varchar(50)
DECLARE @InvoiceNo  VARCHAR(6) = '135255'
DECLARE @ManifestName VARCHAR(11) = 'ManifestInv'
DECLARE @Extension VARCHAR(4) = '.txt'
DECLARE @FullName VARCHAR(100)
DECLARE @BCP VARCHAR(8000)
DECLARE @Path VARCHAR(200) = 'C:\Users\MSSQLSERVER\Temp\'

SET @FullName = char(34) + @Path + @CustomerID + @ManifestName + CAST(@InvoiceNo as VARCHAR) + @Extension + char(34)
SET @BCP = 'bcp "SELECT char(34)+ComposerAuthor+char(34), char(34)+Title+char(34), char(34)+(ISNULL([Place],char(32))+char(32)+char(58)+char(32) + [Publisher]+char(44)+char(32) + [Copyright])+char(34) AS PlacePubDate, char(36)+CAST([ExtPrice] AS varchar), char(34)+ScoreFormat+char(34) FROM tfront.dbo.tblCustInvItems INNER JOIN tfront.dbo.tblInventory on tfront.dbo.tblCustInvItems.InventoryID = tfront.dbo.tblInventory.ID WHERE InvoiceNo = ' + @InvoiceNo + '" queryout  '+ @FullName + ' -T -c -t\t -S'

EXEC master..xp_cmdshell @BCP

SET @EmailBody = 'The Account ' + @CustomerID + ' was billed today.' + Char(13) + 'A text manifest was sent.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TFRONT_DBMail',
@recipients = 'albert@tfront.com',
@body = @EmailBody,
@subject = 'TFront Database Auto Email',
@file_attachments= @FullName;

Albert S

_{Thursday, March 5, 2015 9:24 PM}

It's because of the double quotes in the file path

 "C:\Users\MSSQLSERVER\Temp\NTCDManifestInv135255.txt" 

it should be just 

C:\Users\MSSQLSERVER\Temp\NTCDManifestInv135255.txt

remove the char(34) in the front and end  it would work..

SET @FullName =  @Path + @CustomerID + @ManifestName + CAST(@InvoiceNo as VARCHAR) + @Extension
select @FUllname

Hope it Helps!!

_{Thursday, March 5, 2015 9:48 PM}

Hello,

I tried this also, checked the shared permissions, etc.:

DECLARE

@PathVarchar(200)=N'\DD1LZDZ1\Temp\

But got this message:

Attachment file "\DD1LZDZ1\Temp\NTCDManifestInv135255.txt" is invalid.

Albert S

_{Thursday, March 5, 2015 9:51 PM}

Ah, excellent!

That was it. Works perfectly.

If only SQLServer could return an error of "Don't use quotes"...

Thanks again!

Albert

Albert S

_{Thursday, March 5, 2015 10:12 PM}

Got it. Thanks again.

I'm going to implement the full trigger to see if it works. I'll post back if any problems. I'm also going to try creating a text file, then running another program with the xp_cmdshell that will transform the text file into another format. I'll post if any trouble on that one.

Take care,

Albert

Albert S

_{Friday, July 28, 2017 12:18 AM}

Absolutely!

That's typical with many software vendors...

But with Microsoft.... it's the rule!

Wish Microsoft has better support and its products provide more readable information about problems or errors it throws