SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error Message: String or binary data would be truncated.


Error Message: String or binary data would be truncated.

Author
Message
jruddy-784673
jruddy-784673
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 14
Three databases that were forced restored now do nor allow manual entry, even one single character, although using an update statement works. This is not a matter of changing the length of the datatype, but something else. For the life of me, I can't figure out why this mere attempt at manual data entry generates the error below:

"No row was updated.
The data is row 2 was not committed.
Error Source: >Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.
Correct the errors and retry or press ESC to changes the change(s).

Why does this message speak about truncating when only one character is entered?

We are using SQL Server 2005 SP 2

Jim
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18450 Visits: 14894
Run a Trace, SQL Profiler, when you attempt to edit the data in SSMS, then my guess is you will see an update statement with your one character followed by spaces that are already in the column.

In SSMS you can do a select all in the cell, and I'm betting there are spaces in there, delete and then enter the new characters.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
jruddy-784673
jruddy-784673
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 14
Jack,

If you are right then every one of our hundred of columns in our hundred of tables needs to have such extra characters deleted, since I cannot manually enter data in ANY column. Besides, before I tried the manual entry I pressed the delete button to delete the pressumed unseen spaces, and it still wouldn't accept manual entry.

Jim
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18450 Visits: 14894
Have you run Profiler like I suggested to see what is happening behind the scenes? All SSMS is doing is issuing an update statement so there has to be something else in the data.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
jruddy-784673
jruddy-784673
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 14
How does one run a trace on a single cell of a column?

Jim
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18450 Visits: 14894
You can't. You just run a trace a trace using the Standard Template (be sure to include the TextData column for the RPC completed event) and add filters on Database ID, and for application like Microsoft SQL Server Management Studio.

Attached is a zip file with a template. You just need to change the filter on DatabaseID. Put the template in C:\Documents and Settings\[your user name here]\Application Data\Microsoft\SQL Profiler\9.0\Templates\Microsoft SQL Server\90 and it will show up in Profiler.

Once you have profiler running, open SSMS connect to the DB and do an update. YOu should find and RPC:Completed event with TextData like sp_execute_sql 'Update statement', 'Parameter Defintion', Paramter list.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Attachments
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search