Mel Brooks
asked on
SQL Error value inconsistent with data type or column length
I have a customer who has been using our software product for years. its a Microsoft Access front end with an ODBC connection to sql server on the back end. They have recently started encountering the following error when pasting large text or even smaller text that contains non-alphanumeric characters into a varchar field.
Here is the error: The value you entered is not consistent with the data type or length of the column.
If I go into Management Studio and paste the data directly into the data field, then there is no error, but data is severely truncated only 1-2 lines of text.
I can paste the exact same text into an sql server table on a different server and it works fine.
Pasting into a table in a different database on the same server doesn't work.
I have backed the database up, restored it to a different server and the data pasted fine.
We have tried restarting sql server, disabling anti-virus, restarting the VM hosting sql server and finally we ran the SQL server repair. Still no success. I'm at my wits end. Any suggestions on what to try or what else I can do to diagnose the issue? The sql server is 2008 R2.
Here is the error: The value you entered is not consistent with the data type or length of the column.
If I go into Management Studio and paste the data directly into the data field, then there is no error, but data is severely truncated only 1-2 lines of text.
I can paste the exact same text into an sql server table on a different server and it works fine.
Pasting into a table in a different database on the same server doesn't work.
I have backed the database up, restored it to a different server and the data pasted fine.
We have tried restarting sql server, disabling anti-virus, restarting the VM hosting sql server and finally we ran the SQL server repair. Still no success. I'm at my wits end. Any suggestions on what to try or what else I can do to diagnose the issue? The sql server is 2008 R2.
Make sure is a varchar field...
Check the data type first in SQL Server, then in the definition of the linked table.
Has the data type a length limit?
Is the data type in Access the correct equivalent?
The normal error in Access for hitting a text size limit is "The text is too long for editing.".
So look into the table definition, especially is a trigger active rasing that error?
And please post a screenshot of the error to provide more context.
Has the data type a length limit?
Is the data type in Access the correct equivalent?
The normal error in Access for hitting a text size limit is "The text is too long for editing.".
So look into the table definition, especially is a trigger active rasing that error?
And please post a screenshot of the error to provide more context.
ASKER
Here is what I have done to help better explain the issue. On the customer's server I created a new table with one column varchar(max). I pasted in the contents of this file: https://yourtimas.com/testdata.txt
SQL server under SSMS accepted the data with no messages or warnings, but the only thing that got saved in the column was this one line: https://yourtimas.com/resultdata.txt
I copied the same data into a new table with a varchar(max) field on a different server and it accepted all of the data and querying the record shows no truncation. I will note one other thing. When saving the results to a text file on the customer's server, Notepad defaulted to ANSI format and so I had to change it to UTF-8 to save the file.
To me it seems like that this server is refusing to accept unicode characters or something.
Any suggestions on what to do next?
SQL server under SSMS accepted the data with no messages or warnings, but the only thing that got saved in the column was this one line: https://yourtimas.com/resultdata.txt
I copied the same data into a new table with a varchar(max) field on a different server and it accepted all of the data and querying the record shows no truncation. I will note one other thing. When saving the results to a text file on the customer's server, Notepad defaulted to ANSI format and so I had to change it to UTF-8 to save the file.
To me it seems like that this server is refusing to accept unicode characters or something.
Any suggestions on what to do next?
If I go into Management Studio and paste the data directly into the data field, then there is no error, but data is severely truncated only 1-2 lines of text.So I guess the key is the Server. Are them identical? Same OS? Same MSSQL Server version? Same Settings?
I can paste the exact same text into an sql server table on a different server and it works fine.
Pasting into a table in a different database on the same server doesn't work.
I have backed the database up, restored it to a different server and the data pasted fine.
Well, VARCHAR() is not-unicode enabled. Here you need NVARCHAR() instead.
When during the paste a conversion is necessary, then you may endup in twice the number of characters, thus exceeding your defined string size.
But still of interest is the original error in Access as screenshot.
When during the paste a conversion is necessary, then you may endup in twice the number of characters, thus exceeding your defined string size.
But still of interest is the original error in Access as screenshot.
ASKER
Remember? I said I pasted the text into a database on a different server with a varchar(max) column and it accepted the entire text.
Just for kicks, I changed the data type of the column to nvarchar(max) and pasted in my test data. The results were almost identical. The field only accepted the first line of the text and the only difference is that instead of displaying a ? mark for the first character, it displayed a unicode box character.
The column data type is not the problem. there is either something different in the configuration settings of the server or of sql server or there is some kind of file corruption or a program conflict. The problem is isolated to this one customer's server and sql server system.
Just for kicks, I changed the data type of the column to nvarchar(max) and pasted in my test data. The results were almost identical. The field only accepted the first line of the text and the only difference is that instead of displaying a ? mark for the first character, it displayed a unicode box character.
The column data type is not the problem. there is either something different in the configuration settings of the server or of sql server or there is some kind of file corruption or a program conflict. The problem is isolated to this one customer's server and sql server system.
Remember?
The normal error in Access for hitting a text size limit is "The text is too long for editing.".
Remember?
We need at least a screenshot of the original error message..
You don't provide enough information, which leaves us guessing.
What does a trace shows as content for the insert?
btw, the default ricochets: localizaion of the systems and collations in SQL Server are identical, as well as the used drivers are all the same. The drivers issue includes that all client machines have the same drivers.
p.s. hardware was tested and is error free?
The normal error in Access for hitting a text size limit is "The text is too long for editing.".
Remember?
We need at least a screenshot of the original error message..
You don't provide enough information, which leaves us guessing.
What does a trace shows as content for the insert?
btw, the default ricochets: localizaion of the systems and collations in SQL Server are identical, as well as the used drivers are all the same. The drivers issue includes that all client machines have the same drivers.
p.s. hardware was tested and is error free?
ASKER
Let's revise this question and pretend I never mentioned the phrases MS Access and ODBC.
The root of the problem is SQL Server (or possibly the machine SQL server is running on)
If I go into management studio and paste in the test data file I previously attached, the table field accepts the data without any error messages, but it truncates it as you can see in the result data that I previously posted.
So, reviewing my comments where I've listed out everything I have already tried, are there any suggestions on how to possibly isolate whether the problem is with the VM itself (or the OS) or if the problem is with the SQL Server application.
I've already resolved that the problem is Not the database itself. What can I further do to troubleshoot and hopefully resolve this issue?
I'm not sure how to go about running a "Trace" as suggested. I don't know what a "ricochets" is.
Comparing server configuration settings to other servers including coallation, the settings are identical.
This was not a problem on this server which has been running for several years. It just started a few weeks ago.
The root of the problem is SQL Server (or possibly the machine SQL server is running on)
If I go into management studio and paste in the test data file I previously attached, the table field accepts the data without any error messages, but it truncates it as you can see in the result data that I previously posted.
So, reviewing my comments where I've listed out everything I have already tried, are there any suggestions on how to possibly isolate whether the problem is with the VM itself (or the OS) or if the problem is with the SQL Server application.
I've already resolved that the problem is Not the database itself. What can I further do to troubleshoot and hopefully resolve this issue?
I'm not sure how to go about running a "Trace" as suggested. I don't know what a "ricochets" is.
Comparing server configuration settings to other servers including coallation, the settings are identical.
This was not a problem on this server which has been running for several years. It just started a few weeks ago.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
is that right?
you should check if there is any code in the app onChange OnEnter before insert/update...
maybe the error comes from the code after pasting...
also, instead of pasting what happens when you type those text including special characters?