Inconsistent ODBC Error

Question

Inconsistent ODBC Error

Michael Piper

Valued Member

Points: 70
More actions
November 9, 2006 at 2:42 pm

#170374

I'm an ad-hoc DBA with Cisco and MS certs that is having trouble tracking down the source and solution for an inconsistent ODBC error. It's inconsistent because it only occurs on two of ten users with similar desktop environments. I've reviewed the SQL server logs and OS events as well as the workstation events but have not been successful in finding a resolution. The user error message is as follows:
Vendor: Microsoft
Driver: ODBC SQL Server Driver
State: 22001
String data, right truncation
Last SQL String:
insert into Journal(CallID,HEATSeq,Tracker,EntryDate,EntryTime,EntryText,QuikJournal,HelpLabel) values(?,?,?,?,?,?,?,?)
Last ODBC Command:
Function: SQLExecute()
Can someone give me some hints on where I should be looking to troubleshoot this error?
TIA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

I'd venture a guess that one of the fields in the insert statement is receiving data that is longer than the field; for example if QuikJournal was defined as 200 characters and someone types in a 500 char detailed message; can you check to see if the application using this connection is validating the fields prior to running the insert command?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Piper Valued Member Points: 70 More actions · Answer 2

Thanks Lowell, I'll check it out.

Update:

I think I've found the source of the error, just not sure yet how to safely correct it. The application is pulling a text field (Tracker) with a width of 96 from one table and trying to insert it into a linked field in another table where the field length is only 8. The tracker field represents user IDs which were originally created with 8 character limits. Since migrating the DB from Oracle to MSSQL, I've enabled MS authentication and thus some of the user name lengths now exceed 8 characters; I think this is causing the inconsistent errors.

I cannot change the field length from the application management interface (not an option). I've opened the table up in Enterprise manager table design and found the 8 character limited field but am reluctant to increase it during working hours (noob alert).

I'm heading to MSDN and TechNet and possibly the application vendor for assistance, but would anyone be willing to shed some light on any potential ramifications of changing the field length "on the fly?"

swede SSCommitted Points: 1773 More actions · Answer 3

From my experience it more likely a numeric field. Example moving 99.99999 into int field.

Michael Piper Valued Member Points: 70 More actions · Answer 4

Opened a trouble ticket with the Vendor. It was a "read only" system table field that could "only" be expanded by upgrading the application. The upgrade was covered in our maintenance contract and we were three revisions behind, so not biggie (Heat by Forefront, upgraded 8.0 to 8.3.8).

Thanks for the responses, they were both constructive and enhance my SQL knowledge for future issues.