SSRS Report Subscription Fails (Failure sending mail) due to column contents
-
I've had this problem rear its head before but not in some time. There's a bug in SSRS apparently where some whitespace and a period and more whitespace can cause a subscription to stop, send, but report failure. The received email is plain text and stops where that character was. I'm trying to find out how I can find these columns and fix them.
I'm referring to a post here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32908192-reporting-services-subscription-rendering-error
I found one example and when I SELECT that column, the output in SSMS looks like this:
It would help if I could find these characters and fix them easily. Any suggestions? I can't tell if those are spaces or if there are other invalid characters in there.
- This topic was modified 3 years, 6 months ago by street9009.
-
One thought on how to "fix" this (based on the reply to the thread you indicated) would be to have that column returned inside a CASE statement where you could do:
CASE WHEN (LEN(column) = 1 and column = '.') THEN '' ELSE REPLACE (column,' .','') END AS column
Problems with that come in IF you need to keep the space period ("% .%") OR need to keep the period only columns ("."). In those cases, I am not sure how you would handle it. My understanding is that this is due to how the email server is reading and handling the message. My understanding of the issue is that the mail server is seeing a period on its own (either with a preceding space or on a line by itself) as a "stop" character. Basically an "end of transmission" character.
I just confirmed this by looking at how to send email via telnet. After sending the proper initial commands, you get presented with:
354 Enter mail, end with "." on a line by itself
So when the mail server sees a . off on it's own, it is thinking it is an end of mail and all data after that is junk data. So to add to that CASE statement above, you would likely also want to tackle CHAR(10) and CHAR(13) (usually together which indicates a new line in Windows, but I think either on their own can be interpreted as a new line... carriage return and newline are what they correspond to).
Another potential solution would be to add a non-printable character before all periods. Something like CHAR(0) (NULL), CHAR(7) (beep), CHAR(9) (tab), etc. Basically, look for all cases where you have a period and put a non-printable character before that the mail server MIGHT be happy with and not think it is an end of message.
References for the telnet thing I found - https://tecadmin.net/ways-to-send-email-from-linux-command-line/#:~:text=5%20Ways%20to%20Send%20Email%20From%20Linux%20Command,POP%2FIMAP%20servers.%20...%204%205.%20Using%20%27telnet%27%20Command
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. -
So I've been trying to write a query for now to just detect this issue. The problem is the newline, period, newline sequence is appearing inside other text (it's a text field in SQL) so someone is typing, pressing enter, hitting the period, pressing enter again, and continuing to type (or something along those lines - in the screenshot above I'm not exactly sure what happened).
So anyway, I've been trying to at least find these instances and I'm trying to write a WHERE statement to pick up the record I am interacting with above. So far I've tried these:
WHERE sNoteText LIKE '%'+CHAR(10)+CHAR(13)+'.'+CHAR(10)+CHAR(13)+'%' --0 Results Returned
WHERE sNoteText LIKE '%'+CHAR(13)+CHAR(10)+'.'+CHAR(13)+CHAR(10)+'%' --60 Results Returned, but not the one with the issue
WHERE sNoteText LIKE '%'+CHAR(10)+'.'+CHAR(10)+'%' --34 Results Returned, but not the one with the issue
WHERE sNoteText LIKE '%'+CHAR(13)+'.'+CHAR(13)+'%' --0 Results ReturnedSo now I'm not sure what to try. How many of these combinations do I need to come up with to try to find this one record? Or is there a way to see exactly which characters are in that field somehow (as SSMS just shows it all as whitespace)?
-
One way (that is a pain in the butt but reliable) would be to cast the string as VARBINARY.
I recommend doing it with selecting the text and have the varbinary version next to it something like:
DECLARE @test VARCHAR(255) = 'TEST
.
TEST'
SELECT @test, CAST(@test AS VARBINARY(MAX))So you can see the text (which when SELECTED looks like "TEST . TEST") and the VARBINARY version of the text which in my case is:
0x54455354200D0A2E0D0A54455354
Each 2 characters after the 0X represent a single letter in the text string. So from the above, 54 corresponds to T, 45 corresponds to E, 53 corresponds to S then the 20 is a space, 0D 0A is a new line (which corresponds to CHAR(13) + CHAR(10) when you convert HEX to DECIMAL), 2E is a period.
So if you cast it to VARBINARY(MAX) then grab the value and look for "2E" you will find a period. Then you can look at what characters come before and after it and convert them from HEX to DECIMAL and you can use it in your QHERE clause to find the offending character string.
The reason I say it is a pain in the butt is that reading a long HEX string is not a fun task to do.
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. -
Looks like my problem sequence (for now) is 0A2E0A. Not sure why the CHAR(13)+CHAR(10) sequence didn't pick it up and I can't get the VARBINARY(MAX) to pick it up either. However, when I display it (or rather a SUBSTRING of it), it comes back looking like this:
0x0A0A0A2E0D0A20202020
So multiple line feeds, a period, another line feed and some spaces.
Shouldn't this pick it up?
CAST(CAST(sNoteText AS varchar(MAX)) AS VARBINARY(MAX)) LIKE '%0A2E0A%'
-
That would miss it.
You are getting 0A0A2E0D0A so 0A (CHAR(10) 0A (CHAR(10)) 2E (period) 0D (CHAR(13)) 0A (CHAR(10)) which is weird to me.
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. -
Any way to pick that up with a RegEx of some kind perhaps?
-
Probably, but I don't know RegEx very well and I often get it wrong so I prefer to avoid RegEx.
Also, I don't think SQL Server handles RegEx natively.
Do you need to preserve newline information? If not, then you MAY be able to get by with doing a replace like REPLACE(REPLACE(column,CHAR(10),'')CHAR(13),''). If you do need to preserve newline information, then I think your next best bet would be to look for a period with the previous value being CHAR(10) OR CHAR(13) and put a space before the period. Something like REPLACE(REPLACE(column,CHAR(10)+'.',CHAR(10)+' .'),CHAR(13)+'.',CHAR(13)+' .'). Note I did not test that, I just think it should work. The exception would be if the string started with a period and a new line. Then that approach wouldn't work... so MAY also need to check for that too.
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. -
I don't think that's all encompassing though. While terrible formatting, a new line, a period, and some text wouldn't lead to this problem. So I need a way to find the newline (0A I presume), period (2E), any number of spaces (20), and another newline (0A). The any number of spaces may also need to precede the period as well, I'm not sure.
-
Not sure if the characters after the period really matter. From reading up on the telnet email approach, I think the mail server is just looking for a line that starts with a period and has a new line after it. I expect it is checking for CHAR(10) and/or CHAR(13) being before and after the period.
I am pretty sure that IF spaces exist, then it wouldn't send the email. What I mean is if there was a space before or after the period, then the mail server wouldn't see it as a period on a line all by itself which is the requirement for the mail server to send the message. So if you had your VARBINARY being "0x0A2E200A" it wouldn't send it. If the VARBINARY was "0x0A202E0A" it wouldn't send. It is JUST if it is 0A2E0A, 0A2E0D, 0D2E0A, or 0D2E0D. Those are the only 4 use cases where it is a period on its own.
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. -
Ah, gotcha. That makes sense.
Any idea why this wouldn't pick up the pattern above then?
(CAST(CAST(sNoteText AS varchar(MAX)) AS VARBINARY(MAX)) LIKE '%0A2E0A%' OR CAST(CAST(sNoteText AS varchar(MAX)) AS VARBINARY(MAX)) LIKE '%0A2E0D%' OR CAST(CAST(sNoteText AS varchar(MAX)) AS VARBINARY(MAX)) LIKE '%0D2E0A%' OR CAST(CAST(sNoteText AS varchar(MAX)) AS VARBINARY(MAX)) LIKE '%0D2E0D%')
-
Looking at it, it is because you are comparing VARBINARY to VARCHAR. So SQL is implicitly converting your VARBINARY(MAX) to a VARCHAR and VARBINARY 0x0A2E0A is not the same thing as VARCHAR %0A2E0A%.
So, if you are wanting to compare it like a string, you are going to need to do some conversions in a different way. Rather than CASTing your column data, CAST the other side.
So do something like:
WHERE (sNoteText like '%' + CAST(0x0A2E0A AS VARCHAR(MAX)) + '%' OR
sNoteText like '%' + CAST(0x0A2E0D AS VARCHAR(MAX)) + '%' OR
sNoteText like '%' + CAST(0x0D2E0A AS VARCHAR(MAX)) + '%' OR
sNoteText like '%' + CAST(0x0D2E0D AS VARCHAR(MAX)) + '%')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. -
Your REPLACE() was perfect except for the "space period" still resulted in a stop. Will have to find another character to go there (or just remove the period, that may be best?) but all this is working great. Thanks for your help!
-
Happy to help.
Also, thanks for the follow-up! It is always nice to hear if a solution helped or not and what the final solution ended up being.
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 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply