09-29-2010 11:18 PM
I have a rather baffling mystery when trying to insert record into an SQL Server 2005 database.
The complete error message is:
Error -2147217833 occurred at NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Insert Data.vi->Ansur Database insert.vi->Insert data into dummy database.vi
Possible reason(s):
ADO Error: 0x80040E57
Exception occured in Microsoft OLE DB Provider for SQL Server: String or binary data would be truncated. in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Insert Data.vi->Ansur Database insert.vi->Insert data into dummy database.vi
I've underlined the part of the message that seems very odd. Basically I'm inserting some simple strings and the contents of an XML file (attached) which is stored in a VARCHAR(max) column. The error message seems to suggest that the XML file length is too long, but this cannot be because:
a) VARCHAR(max) datatype can contain huge amounts of characters 2^32-1 (I think) and the file is only 121KB long.
b) I have already successfully inserted many similar XML files that are even larger in size.
All I can think of is that something in the file itself is causing an error, but I can't understand why, since the file contents should just be treated as characters when inserting into the database column - anyone got any ideas?
Chris.
P.S. I also tried it without transactions and it made no difference.
Solved! Go to Solution.
09-30-2010 03:06 PM
I can't say why this might happen, but I have a few ideas for you:
09-30-2010 10:59 PM
Yipee!! - Solved the problem. It turned out that one of the XML Tags didn't have a space in it and so instead of returning the abbreviated technicians name (eg. FAN) it returned the whole name (eg. FAN-Fabio Conta) - and what's the problem with that you ask? Well I defined the Technicians Name column in the database to be CHAR(5) - hence I got the ADO Error "String or Binary Data Truncated" when it went beyond the five character limit - it all makes sense now.
Can I give myself some Kudos?
Chris.
10-01-2010 07:12 AM
You can mark the thread as solved and yourself as the solution. 😉
/Y