troubling UPDATE error: String or binary data would be truncated.

Question

troubling UPDATE error: String or binary data would be truncated.

patrick gallaher

Valued Member

Points: 53
More actions
March 7, 2005 at 9:48 pm

#88274

i'm sure somebody is going to make fun of me.. but here i go..
this is coded into an asp page.. i did a response.write to show me my sql statement before i execute the sql query:
UPDATE fus_Workorder SET c_cust_id_WO=4, c_consult_id='sadia', c_tech_id='bryan', c_billing_id='patrick', c_delivery_id='marion', c_bln_lab_drop='ON', c_bln_repairs_done='ON', c_bln_invoice_created='ON', c_bln_delivery_done='ON', c_items_brought='ibtl', c_primary_objectives='po', c_diagnosis='diag', c_bln_diag_call='ON', c_diag_caller_id='sadia', c_diag_call_date='4/4/2004', c_diag_call_done='ON', c_pending_approval='pa', c_stuff_to_be_installed='hstbi', c_bln_labrec_call='ON', c_labrec_caller_id='rich', c_labrec_call_date='4/4/2004', c_labrec_call_done='ON', c_billing_notes='ttbd', c_follow_up='fus', c_bln_follow_up_call='ON', c_bln_follow_up_call_date='4/4/2004', c_follow_up_caller_id='patrick', c_follow_up_call_done='ON', c_follow_up_potential='fucs ', c_repairs_due_date='4/4/2004', c_delivery_date='4/4/2004', c_workorder_potential=3000, c_workorder_cost=1200 WHERE (c_workorder_id=321321);
running it once it updates fine.. no errors.. all formatting is fine.. plenty of space for no need of truncation.. however when i open the same record set up again then update it, i get the error: "String or binary data would be truncated.".. however, if i run this through sql analyzer or enterprise manager, i get no problem.. i can run the update command one split second after another.. the only way to get it to update via odbc is to turn ansi_warnings off then back on afterwards as such:
SET ANSI_WARNINGS OFF; UPDATE fus_Workorder SET c_cust_id_WO=4, c_consult_id='sadia', c_tech_id='bryan', c_billing_id='patrick', c_delivery_id='marion', c_bln_lab_drop='ON', c_bln_repairs_done='ON', c_bln_invoice_created='ON', c_bln_delivery_done='ON', c_items_brought='ibtl', c_primary_objectives='po', c_diagnosis='diag', c_bln_diag_call='ON', c_diag_caller_id='sadia', c_diag_call_date='4/4/2004', c_diag_call_done='ON', c_pending_approval='pa', c_stuff_to_be_installed='hstbi', c_bln_labrec_call='ON', c_labrec_caller_id='rich', c_labrec_call_date='4/4/2004', c_labrec_call_done='ON', c_billing_notes='ttbd', c_follow_up='fus', c_bln_follow_up_call='ON', c_bln_follow_up_call_date='4/4/2004', c_follow_up_caller_id='patrick', c_follow_up_call_done='ON', c_follow_up_potential='fucs ', c_repairs_due_date='4/4/2004', c_delivery_date='4/4/2004', c_workorder_potential=3000, c_workorder_cost=1200 WHERE (c_workorder_id=321321); SET ANSI_WARNINGS ON;
why is this so? (using MS SQL2000 btw)
-pat

Viewing 6 posts - 1 through 6 (of 6 total)

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

Helen-84411 Hall of Fame Points: 3320 More actions · Answer 1

Hi

When SET ANSI_WARNINGS is OFF you may not get any warning messages. Check the lenth of each column in the Table defintion. It may be that the value that you are trying to update is over the column width set in table. It may be there for atleaset one field. Check that first.

Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me

patrick gallaher Valued Member Points: 53 More actions · Answer 2

i checked every value.. it updates fine the first time via asp/odbc.. but when i update it again, it gives the error message. length are not an issue here. i even made sure the messages were TRIMmed.. so there is no extra space. yet, there is still this problem.

-patg

vadba SSChampion Points: 11133 More actions · Answer 3

Do you have an UPDATE trigger on the table? Maybe something is happening there. Also, you could hard code the SQL statement in your .ASP page, then strip off columns one-by-one until the error clears up. This may help you identify which column is causing the error.

Mike

patrick gallaher Valued Member Points: 53 More actions · Answer 4

sorry to be a newbie, but what do you mean "do you have an UPDATE trigger on the table?"..

vadba SSChampion Points: 11133 More actions · Answer 5

Look up CREATE TRIGGER in BOL (SQL Server Books On Line). The definition from BOL is, in part:

... a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table. SQL Server allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement.

If you have an update trigger defined for the fus_Workorder table, then any time an update occurs against that table, the code defined in the trigger will be executed by SQL Server automatically.