May 31, 2017 at 1:14 pm
This is a strange issue, but I'm not sure how to troubleshoot this. Users are using an ERP system which is using .NET 4.6.1 that is tied to a SQL Database using 2014 and they are on Windows Server 2012 R2. In this one custom screen they are entering data into a primary key field and they are claiming that the data they are entering is being transformed into a single # character in that field. They don't see it actually happening though, but I did confirm some data is corrupted this way. I'm going to create a custom log on the front end, but I wanted to pose the question to see if this is possible? Would SQL misinterpret data and transform it? Could Windows regional settings affect this? Is there a way to log data changes on a table and be able to monitor what is added and updated? Thanks for any help!
June 1, 2017 at 7:59 am
There are a bunch of things it could be, probably most of them on the client side. However, the paranoid DBA in me says, check for corruption of the database first. Have you run DBCC on it lately?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2017 at 4:02 pm
Grant Fritchey - Thursday, June 1, 2017 7:59 AMThere are a bunch of things it could be, probably most of them on the client side. However, the paranoid DBA in me says, check for corruption of the database first. Have you run DBCC on it lately?
No, this company has a company that specializes in SQL that supports them on the database side, I'm just the .NET developer consultant working with their ERP. I could ask them to run that check to see, it doesn't hurt.
June 1, 2017 at 7:46 pm
This is probably low-hanging fruit, but since it's low-hanging, you might as well take a look at it.
In the .NET project, I presume it's calling a stored procedure to do the save. Id' check the parameter type in .NET to make sure it isn't sized to a length of 1. Also take a look at the stored procedure - the parameter received should be of sufficient size and make sure it isn't being trimmed down to 1 before being inserted into the table. I presume the column in the table is properly-sized, but you might as well check it while you're there to make sure an "unadvertised feature" wasn't introduced in an upgrade, service pack or anything else.
When you say the users don't see it, is any new data getting into the tables intact? Or is everything being truncated?
f this is new behavior, do yourself a favor and have the DBA set aside a full backup from before the behavior was first reported. Most times, backups will be retained for X days and deleted when they get older than that. You might need an old backup to do some comparisons of table and procedure definitions later in life, so set it aside now, just to be sure. If, on the other hand, there's a source control system in use, that may have your answer if a procedure was updated recently.
June 5, 2017 at 12:45 pm
Ed Wagner - Thursday, June 1, 2017 7:46 PMThis is probably low-hanging fruit, but since it's low-hanging, you might as well take a look at it.In the .NET project, I presume it's calling a stored procedure to do the save. Id' check the parameter type in .NET to make sure it isn't sized to a length of 1. Also take a look at the stored procedure - the parameter received should be of sufficient size and make sure it isn't being trimmed down to 1 before being inserted into the table. I presume the column in the table is properly-sized, but you might as well check it while you're there to make sure an "unadvertised feature" wasn't introduced in an upgrade, service pack or anything else.
When you say the users don't see it, is any new data getting into the tables intact? Or is everything being truncated?
f this is new behavior, do yourself a favor and have the DBA set aside a full backup from before the behavior was first reported. Most times, backups will be retained for X days and deleted when they get older than that. You might need an old backup to do some comparisons of table and procedure definitions later in life, so set it aside now, just to be sure. If, on the other hand, there's a source control system in use, that may have your answer if a procedure was updated recently.
Actually there is data that is intact and there are two issues actually, which are the same but occur on different fields. There are 5 primary key fields (four of which are not available to the user because I set them in the screen) and the fifth is the data the user inputs. The columns in the database are VARCHAR(50).
Issue 1) is that sometimes a # sign replaces data in one of columns Key1 to Key4, which is beyond me because Key1 to Key3 are integers from the screen that also cannot be modified by the user and Key4 is hard-coded text that the user doesn't have access to. I am however using business objects that were created by the ERP vendor, so I guess there is a possibility there could be a bug of some sort.
Issue 2) is the pound sign replacing data in Key5.
Issue 1 I haven't seen happen, but my help desk tech says it has happened a couple of times. There are tons of transactions in this table, so I would say less than 1% of the transactions result in this issue, maybe less. Issue 2 occurs more, but it turns out it could be a data entry problem. I'm now being told this was not taken into account before reporting it to me, so maybe this is resolved. But Issue 1 bothers me. In theory, that shouldn't even be possible. The data is saved using a stored procedure via LINQ. I can't really change the SP but I could troubleshoot it. If there is something wrong with it, I have to report it to the ERP vendor. This is a new customization using this table (which is a basic table created just for customizations), so there are no backups per se and supposedly once issue 1 occurs, it breaks the reports that are tied to this table, so they have to fix it immediately.
For now, I'm going to wait for it to happen again, but it is very odd.
June 7, 2017 at 1:31 pm
Perhaps the better question to ask is where, exactly, these # characters are actually appearing. Many products will do that to a field when the value has either a numeric size that overflows the data type for the control, or is a longer character value than the existing field's maximum allowable length. You need to actually see this happen, and know exactly what software is attempting to render the value, Then you can hopefully determine exactly which database record represents that data being displayed and then query that record in SSMS and see what the actual values are.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 7, 2017 at 3:31 pm
As a thought, could this be a client processing issue not a server side processing issue?
What I have seen with some of our 3rd party solutions is that the end users will copy and paste data from one program into our 3rd party software and sometimes it'll complain.
The biggest culprit for us is when an end user copy-pastes from Excel as it throws in a new line character sometimes and the 3rd party software is storing the value as a CHAR(25) on the database side (if memory serves), and the 3rd party software, instead of just removing the new line character, moves it to a seemingly random point after the last useful character... and puts in non-breaking spaces between the new line and the last usable character. Try to report off of that with something like:SELECT distinct LEN(value1)
FROM table
WHERE value1 LIKE 'input'
you'd expect to get 1 row with 5 for the result, but we get a lot of different rows with numbers ranging from 5 to 25.
As a thought, are they copy-pasting values into that field? I am wondering if the software is doing sanitation on the input and is returning "#" when the sanitation fails?
If you have access to the source, and you (or an end user) can reproduce the problem, can you dump the input from screen to a text file for logging purposes before it goes to the database? That may help in resolving the issue.
As for your monitoring changes on the table, if you are using enterprise edition, you could turn on CDC (change data capture). If you are using Standard edition or express, you could create a trigger on the table for after insert, update and delete. I expect that the problem is in the application more than in the database though.
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.
June 9, 2017 at 7:44 am
Hello Y'all,
While I'm no DBA, we ran into something similar when exporting from a report. One system added all sorts of strange looking characters along with the data and the other system exported the data as it appeared. Also had the same issue with a client's site, we'd get the data as it appeared when testing on our systems, and they'd get a Latin A before certain characters. We believe it's tied to Encoded setting for the system. Also investigating if Language and collation setting could be affected the output.
Would Encoding, Language and/or Collation be a possibility to consider?
Sincerely,
Bruce Edgar
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply