May 13, 2020 at 1:45 pm
I wrote a SPROC to email some data.
CSV file, single data string column. Between 100KB and 5MB in size
Started with a Query Attachment.
Opened attachment from the email and saw that after a few thousand rows, the data corrupted.
Next I tried using BCP to create the file, then get Database Mail to attach this file. BCP output the file and when checked on the server file system, the file is perfect. No corruption. However, as soon as Database Mail attaches and sends the file, the file is corrupted when opened from the email.
I have no idea what to try now.
This happens only on my customer's production 2016 server. On my 2016 dev server, this works perfectly both as a normal attachment, and as a query attachment.
I've attached copies of both the file pre-email as produced by BCP, and the file once received via email.
Any help appreciated.
edit.
After further testing, it appears the corruption is random. Email the same query results and get different corruption. One file even emailed ok in about 20 attempts.
I then compared the Attachment VARBINARY in msdb.dbo.sysmail_attachments and in that table, the attachments are identical. I can join two attachments on the attachment binary and they join. View the resulting files in Excel or Notebook after opening from Outlook and both corrupted in different ways.
Not sure what else to check.
The forum didn't let me upload the corrupt csv file so I had to zip it first. I also accidentally uploaded the pre email file twice and can't figure out how to remove one copy.
May 13, 2020 at 4:25 pm
My only thought with this if it is a problem on the DATABASE side is something is going goofy with some data type conversions. Such as converting VARCHAR to NVARCHAR, but that is just a guess. Or possibly some patch needs to be applied to the sql instance. But since the VARBINARY is identical for the attachments, my suspicion is that it isn't SQL screwing things up, but something else.
A different thought is since the DATABASE is saying they are the same, it could be something that the mail server OR Outlook is doing to try to help with the attachments and in the process screwing them up intermittently.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 13, 2020 at 8:04 pm
Another thought - when the files get attached to the mail they are converted to 7-bit ascii and I'm not sure of the increase of size in that process. So you could try increasing the size of attachments allowed in database mail since the default is 1 MB. It might be worth a try. You can use exec msdb.dbo.sysmail_configure_sp or change the system parameters in the database mail GUI in SSMS.
Sue
May 13, 2020 at 8:25 pm
Thanks Sue, I have already upped the attachment size to 10MB as some of my files are 4-5MB and I got the size error message at first. Its not related to that I don't think.
May 13, 2020 at 8:28 pm
Its not am email client issue. I see the same corruption when sent to Gmail and viewing the attachment in Google docs. I also see the corruption if downloading the attachment and opening in Notepad++
May 13, 2020 at 9:16 pm
As you have Outlook, I'm thinking you likely have Exchange as the back end mail server. Do you have OWA (outlook web access) on the exchange server? I have a weird thing for you to test assuming you have OWA (outlook Web Access) set up.
On OUTLOOK (not OWA), find one of the emails with the corrupted attachment and mark it as UNREAD. Next, go to OWA and open that email and look at the attachment.
I came across this post:
https://community.spiceworks.com/topic/191604-outlook-corrupting-attachment
which unfortunately had no solution, but they had similar problems and they narrowed it down to the client misbehaving randomly. I found a similar post about the gmail mail server doing something with some attachments and screwing around with the output on a CSV file. Although that one was that it was adding newlines and changing CRLF's to just LF's (removing the CR's).
The fun with the above thread is that it would arrive corrupted in Outlook for desktop for the recipient of the email, but worked fine for another person it was CC'ed to and it opened fine in OWA.
This is the gmail thread I found:
https://stackoverflow.com/questions/359452/cdo-message-text-attachment-gets-corrupted
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 14, 2020 at 9:26 am
Hi Brian,
I just tried that, but the file is still showing corrupted when viewed in OWA.
May 14, 2020 at 2:25 pm
So it isn't the client then... I am wondering if it could be the mail server.
If it was consistent it would be easier to figure out. If it showed up corrupted in sysmail_attachments then I'd also be easier to diagnose as SQL is then screwing it up. To me, this sounds like something is touching the file after it leaves SQL and before it enters a mailbox which is why I am thinking it is a mail server doing a scan on the file.
I was thinking it MIGHT have been something doing a conversion on the text, either accidentally or intentionally, but if it is, I don't see how it is deciding what to convert it to... I ran it through a few text converting tools thinking I may see some logic in how it is converting, but not seeing anything obvious. And since each line starts with BH, I can't think of any logic as to how it is converting... H is not valid in HEX, so it is a weird conversion issue for sure.
If you look at the email header, is there an antivirus or antispam tool running on the message? Often those will write to the message header. I am wondering if one of those tools is corrupting the attachment. Might not hurt to check the header on a good message and a bad message?
If it isn't the mail server, isn't the mail client and isn't SQL, then I am out of ideas on what else to check.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply