March 23, 2011 at 2:34 pm
We have recently started having an issue with editing/adding rows to one of our tables. We haven't made any changes to the db structure in a long time. We are unable to change one of the columns (SubjectID) (type char(32)) to anything that is alphanumeric (ie. 'D_001'). If I try to update the column to '001', it updates fine. The error I get is: Conversion failed when converting the varchar value 'D_001' to data type int
If I try to execute the following t-sql code, I get the error, or if I try to update the row in SSMS I get the same error.
UPDATE Images SET SubjectID='D_001' WHERE OrderID='12345' AND Roll='0001' AND Frame='0001'
I am not sure why the field is being converted to int since it is char?
If I create the table (with no rows) in a different db, all seems to work OK.
The SubjectID column does contain numeric only in some of the rows, don't know if this would make a difference or not.
Any insight would be appreciated.
We are running SQL Server 2008 on a Windows Server 2008 box.
I have attched the DDL for the table.
March 23, 2011 at 2:54 pm
Is there a trigger on the table?
March 24, 2011 at 6:05 am
No, there are no triggers.
March 25, 2011 at 12:45 pm
I actually had a strange problem about 5 years ago that is somewhat similar. The database was 2005 before SP2. There was a table that had various VARCHAR and REAL (32-bit FLOAT) columns in it. It was designed by the front end Access programmer. The table had data in it so at some time it was working just fine in the Access application.
At some point conversion errors would pop up when trying to update some (not all - only some) of the varchar columns in the table. I got the same error when opening the table in SSMS and just editing one of the varchar columns so it wasn't just an Access problem. I don't recall if I ever tried an update in T-SQL.
Just as an experiment I changed all of the REAL data types to precise decimal (since precise made more sense in the application) and the problem went away. I never figured out what was causing the problem or if it was a known bug that was fixed in later versions of SQL Server.
I noticed you have FLOAT data types in the table and I thought I'd put in my 2 cents.
Todd Fifield
March 25, 2011 at 12:55 pm
The problem was created by a view that was created that linked the subjectId (char(32) field in one table to the subjectId (int) field in another table. In the join I needed to cast the subjectid (int) field as char(32).
March 25, 2011 at 1:27 pm
Tim,
Was the view Schema Bound?
Todd
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply