Error 8152: "String or binary data would be truncated"

I've been working with a sample database that the company is using for testing purposes. I (we) did not create the database - it was sent over to us by another company. I'm still a rank newbie at working with MS SQL Server, though I've worked with Access and MySQL in the past.
There is one table that contains bank information. At the moment, it is filled with information on imaginary (fake) banks. I need to change one record so that it contains the information of a real bank the company is using.
The problem is, I am unable to touch anything within this table. Any attempt to make changes gives me an error prompt that reads "String or binary data would be truncated". I ran the profiler, and it shows an Exception - Error: 8152 Severity 16 State 2.
Furthermore, I also get an error prompt stating: "The value you entered is not consistent with the data type or length of this column".
I've checked and checked again, and as far as I can tell, the value I entered _is_ consistent with the data type/length of the column.
I can make changes perfectly fine on the other tables in the database. Only this one table gives me trouble.
Could anyone shed some light on why exactly this is occurring, and why only on this one table?
Thank you :)
in order to shed some light, you would need to show us the schema and the statement you're issuing that's causing the failure.

also check if there are triggers on the table, if so, you should check out the code of those as well.|||Greg - I just had to say thanks. I've been trying to figure out my own similar problem for (way too many) hours. When you mentioned "triggers" a light went on and led me to the source of the problem - a field updated by a trigger was indeed too small. Now I can get some sleep. THANK-YOU!
Randy|||

Make sure that [Table] and ]TableX] are the same type and lenght.

I was getting the same error and I found out that the filed length was different in one table then another table that had the foreign key of the first table.

For example accountnumber field in the statement table was 400 but in statementx table it was set to 255. After I changed the accountnumber field in the statementx table to 400 the problem fixed.

|||

Hi Greg,

Have you ever seen this error occur on few (not all) records that been tried?

Here's the trigger code that get's called to archive the original data after it's been changed:

CREATE TRIGGER dbo.tuASSIGNMENT
ON dbo.ASSIGNMENT
AFTER UPDATE, DELETE AS

INSERT ASSIGNMENTHISTORY(assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
updUser,
updDate,
disposition)

SELECT assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
user,
getdate(),
disposition
FROM deleted

And here's the stored procedure code that works on all cases when executed directly on the SQL Server box, but blows up with the error occasionally when called with the same parameters from an Access 2002 form. All of the parmeter values are passed and read okay.

CREATE PROCEDURE dbo.updPROJECTtoNewDirector
(@.oldID int,
@.newID int,
@.assmtID int)

AS

UPDATE ASSIGNMENT
SET assigneeID = @.newID
WHERE assigneeID = @.oldID
AND assignmentID = @.assmtID

Thanks,

Mark

|||

In this case, it seems that Access 2002/VBA was having trouble with the trigger's behavior. When I diabled the trigger, all of the records that were showing up with the Error 8152 were suddenly updating ok. Bizarre behavior, but that's Access VBA for ya!

Happy New Year to all!

Error 8152: "String or binary data would be truncated"

I've been working with a sample database that the company is using for testing purposes. I (we) did not create the database - it was sent over to us by another company. I'm still a rank newbie at working with MS SQL Server, though I've worked with Access and MySQL in the past.
There is one table that contains bank information. At the moment, it is filled with information on imaginary (fake) banks. I need to change one record so that it contains the information of a real bank the company is using.
The problem is, I am unable to touch anything within this table. Any attempt to make changes gives me an error prompt that reads "String or binary data would be truncated". I ran the profiler, and it shows an Exception - Error: 8152 Severity 16 State 2.
Furthermore, I also get an error prompt stating: "The value you entered is not consistent with the data type or length of this column".
I've checked and checked again, and as far as I can tell, the value I entered _is_ consistent with the data type/length of the column.
I can make changes perfectly fine on the other tables in the database. Only this one table gives me trouble.
Could anyone shed some light on why exactly this is occurring, and why only on this one table?
Thank you :)in order to shed some light, you would need to show us the schema and the statement you're issuing that's causing the failure.

also check if there are triggers on the table, if so, you should check out the code of those as well.|||Greg - I just had to say thanks. I've been trying to figure out my own similar problem for (way too many) hours. When you mentioned "triggers" a light went on and led me to the source of the problem - a field updated by a trigger was indeed too small. Now I can get some sleep. THANK-YOU!
Randy|||

Make sure that [Table] and ]TableX] are the same type and lenght.

I was getting the same error and I found out that the filed length was different in one table then another table that had the foreign key of the first table.

For example accountnumber field in the statement table was 400 but in statementx table it was set to 255. After I changed the accountnumber field in the statementx table to 400 the problem fixed.

|||

Hi Greg,

Have you ever seen this error occur on few (not all) records that been tried?

Here's the trigger code that get's called to archive the original data after it's been changed:

CREATE TRIGGER dbo.tuASSIGNMENT
ON dbo.ASSIGNMENT
AFTER UPDATE, DELETE AS

INSERT ASSIGNMENTHISTORY(assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
updUser,
updDate,
disposition)

SELECT assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
user,
getdate(),
disposition
FROM deleted

And here's the stored procedure code that works on all cases when executed directly on the SQL Server box, but blows up with the error occasionally when called with the same parameters from an Access 2002 form. All of the parmeter values are passed and read okay.

CREATE PROCEDURE dbo.updPROJECTtoNewDirector
(@.oldID int,
@.newID int,
@.assmtID int)

AS

UPDATE ASSIGNMENT
SET assigneeID = @.newID
WHERE assigneeID = @.oldID
AND assignmentID = @.assmtID

Thanks,

Mark

|||

In this case, it seems that Access 2002/VBA was having trouble with the trigger's behavior. When I diabled the trigger, all of the records that were showing up with the Error 8152 were suddenly updating ok. Bizarre behavior, but that's Access VBA for ya!

Happy New Year to all!

Error 8152: "String or binary data would be truncated"

I've been working with a sample database that the company is using for testing purposes. I (we) did not create the database - it was sent over to us by another company. I'm still a rank newbie at working with MS SQL Server, though I've worked with Access and MySQL in the past.
There is one table that contains bank information. At the moment, it is filled with information on imaginary (fake) banks. I need to change one record so that it contains the information of a real bank the company is using.
The problem is, I am unable to touch anything within this table. Any attempt to make changes gives me an error prompt that reads "String or binary data would be truncated". I ran the profiler, and it shows an Exception - Error: 8152 Severity 16 State 2.
Furthermore, I also get an error prompt stating: "The value you entered is not consistent with the data type or length of this column".
I've checked and checked again, and as far as I can tell, the value I entered _is_ consistent with the data type/length of the column.
I can make changes perfectly fine on the other tables in the database. Only this one table gives me trouble.
Could anyone shed some light on why exactly this is occurring, and why only on this one table?
Thank you :)
in order to shed some light, you would need to show us the schema and the statement you're issuing that's causing the failure.

also check if there are triggers on the table, if so, you should check out the code of those as well.|||Greg - I just had to say thanks. I've been trying to figure out my own similar problem for (way too many) hours. When you mentioned "triggers" a light went on and led me to the source of the problem - a field updated by a trigger was indeed too small. Now I can get some sleep. THANK-YOU!
Randy|||

Make sure that [Table] and ]TableX] are the same type and lenght.

I was getting the same error and I found out that the filed length was different in one table then another table that had the foreign key of the first table.

For example accountnumber field in the statement table was 400 but in statementx table it was set to 255. After I changed the accountnumber field in the statementx table to 400 the problem fixed.

|||

Hi Greg,

Have you ever seen this error occur on few (not all) records that been tried?

Here's the trigger code that get's called to archive the original data after it's been changed:

CREATE TRIGGER dbo.tuASSIGNMENT
ON dbo.ASSIGNMENT
AFTER UPDATE, DELETE AS

INSERT ASSIGNMENTHISTORY(assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
updUser,
updDate,
disposition)

SELECT assignmentID,
assignmentName,
assignmentDesc,
assigneeID,
assignDate,
dueDate,
completeDate,
crUser,
user,
getdate(),
disposition
FROM deleted

And here's the stored procedure code that works on all cases when executed directly on the SQL Server box, but blows up with the error occasionally when called with the same parameters from an Access 2002 form. All of the parmeter values are passed and read okay.

CREATE PROCEDURE dbo.updPROJECTtoNewDirector
(@.oldID int,
@.newID int,
@.assmtID int)

AS

UPDATE ASSIGNMENT
SET assigneeID = @.newID
WHERE assigneeID = @.oldID
AND assignmentID = @.assmtID

Thanks,

Mark

|||

In this case, it seems that Access 2002/VBA was having trouble with the trigger's behavior. When I diabled the trigger, all of the records that were showing up with the Error 8152 were suddenly updating ok. Bizarre behavior, but that's Access VBA for ya!

Happy New Year to all!

Error 26

HI

I recently uploaded my site to a hosting company... i am now getting this error

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] CSharpMegaDVD.MegaDVD.Product.GetNewDVD() in c:\myaspx\csharpmegadvd\business objects\product.cs:566 CSharpMegaDVD.index.LoadNewDVDList() in c:\myaspx\csharpmegadvd\index.aspx.cs:39 CSharpMegaDVD.index.Page_Load(Object sender, EventArgs e) in c:\myaspx\csharpmegadvd\index.aspx.cs:24 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

is this a fault at my end or thier end?

how do i fix this problem?

thx

Hi

i fixed it

in my connection string i had local instead of (local)

you think the error message could be a little more accurate

anyway

cheers!!

Error 21776 and dbo Problem

Hello:
We recently installed a SQL 2000 version of an older
application we were running. The company took our old
Paradox data and converted it at their site and gave us a
backup to restore to a new database we created on our SQL
server.
After restoring the database, we began to get Error 15023
messages relating to the user and login that the
application uses to grant access to the database. After
some research, I used query analyzer to drop the
questionable users and logins and then re-created them
without a problem. However, we were still getting errors
when trying to connect to the database using the
application.
While troubleshooting the problem, I went to look at the
properties of the sa account and the system threw up
Error 21776-The name dbo was not found in the users
collection. If I try to add a dbo user account to the
database in question, I get Error 15023. I can't drop
and re-create the users and logins because SQL is telling
me I can't do this with a database owner. I can't use
sp_change_users_login because it involves the dbo account.
I don't know where to go from here.
Any comments or suggestions would be appreciated.
Thanks
BrennanHave you checked this article?
305711 BUG: DBO User Does Not Display in Enterprise Manager
http://support.microsoft.com/?id=305711
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.