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 7399

Here is my code:

/*--

--

-- Cape Hatteras Adventures v.2 sample database - Populate

-- Paul Nielsen

-- this script will populate the CHA2 database

-- from CHA1_Customers.mdb Access file

-- and CHA1_Schedule.xls Excel Spreadsheet

-- using distributed queries

--

--

USE CHA2

-- establish Access Linked Server

EXECsp_DropServer @.server ='CHA1_Customers'

go

EXECsp_addlinkedserver

'CHA1_Customers',

'Access 2003',

'Microsoft.Jet.OLEDB.4.0',

'C:\SQLData\CHA1_Customers.mdb'

go

-- establish Excel Linked Server

EXECsp_DropServer @.server ='CHA1_Schedule'

go

Executesp_addlinkedserver

'CHA1_Schedule',

'Excel',

'Microsoft.Jet.OLEDB.4.0',

'C:\SQLData\CHA1_Schedule.xls',

NULL,

'Excel 5.0'

go

EXECsp_helpserver

-- Step 0: Initialize the Database

DELETE Customer

DELETE CustomerType

DELETE Event_mm_Customer

DELETE Event_mm_Guide

DELETE Tour_mm_Guide

DELETEEvent

DELETE Tour

DELETE BaseCamp

DELETE Guide

-- Step 1: Customer Types

SELECTDISTINCT CustomerType

FROM CHA1_Customers...Customers

WHERE CustomerType ISNOTNULL

INSERT CustomerType(Name)

SELECTDISTINCT CustomerType

FROM CHA1_Customers...Customers

WHERE CustomerType ISNOTNULL

SELECT*FROM CustomerType

-- Step 2: Customers

SELECTDISTINCT ContactLastName, ContactFirstName, CustomerType

FROM CHA1_Customers...Customers

WHERE ContactLastName ISNOTNULL

SELECT*FROM CustomerType

SELECT*FROM CHA1_Customers...Customers

INSERT Customer(LastName, FirstName, CustomerTypeID,Address,

City,Country, eMail, NickName,FirstTour, Medical)

SELECTDISTINCT ContactLastName, ContactFirstName, CustomerTypeID,BillingAddress,

City, Country, EMailAddress,NickName, FirstTour, HealthIssues

FROM CHA1_Customers...Customers C

LEFTOUTERJOIN CustomerType

ON C.CustomerType = CustomerType.[Name]

WHERE ContactLastName ISNOTNULL

SELECT*FROM Customer

-- Step 3: Base Camps

INSERT BaseCamp(Name)

SELECTDISTINCT [Base Camp]

FROM CHA1_Schedule...[Base_Camp]

WHERE [Base Camp] ISNOTNULL

SELECT*FROM BaseCamp

-- Step 4: Tours

INSERT Tour ([Name], BaseCampID)

SELECTDISTINCT Tour, BaseCampID

FROM CHA1_Schedule...Tour X

JOIN BaseCamp

ON X.[Base Camp] = BaseCamp.Name

WHERE Tour ISNOTNULL

SELECT*FROM Tour

-- Step 5: Guides

INSERT Guide(FirstName, LastName)

SELECTDISTINCT

LEFT([Lead Guide],CharIndex(' ', [Lead Guide])-1),

RIGHT([Lead Guide],Len([Lead Guide])-CharIndex(' ', [Lead Guide]))

FROM CHA1_Schedule...Lead_Guide

WHERE [Lead Guide] ISNOTNULL

SELECT*FROM Guide

-- Step 6: Events

SELECTDISTINCT*

FROM CHA1_Schedule...Event

SELECT*FROMEvent

INSERTEvent(TourID, DateBegin, Code)

SELECTDISTINCT Tour.TourID, [Date], EventCode

FROM CHA1_Schedule...Event X

JOIN Tour

ON X.Tour = Tour.Name

-- Step 7: Event_mm_Customer

SELECT*FROM Event_mm_Customer

INSERT Event_mm_Customer(CustomerID, EventID)

SELECTDISTINCT Customer.CustomerID, Event.EventID

FROM CHA1_Schedule...Customer X

JOIN Customer

ON X.LastName = Customer.LastName

AND X.FirstName = Customer.FirstName

JOINEvent

ON X.EventCode = Event.Code

-- Step 8: Event_mm_Guide

SELECT*FROM Event_mm_Guide

INSERT Event_mm_Guide(EventID, GuideID, IsLead)

SELECTDISTINCT Event.EventID, Guide.GuideID, 1

FROM CHA1_Schedule...Event X

JOIN Guide

ON X.[Lead Guide] = Guide.FirstName +' '+ Guide.LastName

JOINEvent

ON X.EventCode = Event.Code

-- Step 9: Tour_mm_Guide

INSERT Tour_mm_Guide (TourID, GuideID, QualDate)

SELECTDISTINCT Tour.TourID, Event_mm_Guide.GuideID,'1/1/2000'

FROM Tour

JOINEvent

ON Event.TourID = Tour.TourID

JOIN Event_mm_Guide

ON Event.EventID = Event_mm_Guide.EventID

SELECT*FROM Tour_mm_Guide

Select*from vTableRowCount

-*/

Here is my error: What's happeniing here?

/*--

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CHA1_Customers" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".

Msg 7399, Level 16, State 1, Line 16

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CHA1_Customers" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 16

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CHA1_Customers".

*/

It looks like your Access db is opened in exclusive mode and/or the userid/password you're providing are incorrect.