Showing posts with label binary. Show all posts
Showing posts with label binary. Show all posts

Thursday, March 22, 2012

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 8152 in sql update statement

hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.
Please post your DDL plus the statement that is giving you trouble.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"KristinaY" <KristinaY@.discussions.microsoft.com> wrote in message
news:56B41165-C240-4111-9914-04672C4FA8BF@.microsoft.com...
hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.

error 8152 in sql update statement

hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.Please post your DDL plus the statement that is giving you trouble.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"KristinaY" <KristinaY@.discussions.microsoft.com> wrote in message
news:56B41165-C240-4111-9914-04672C4FA8BF@.microsoft.com...
hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.sql

error 8152 in sql update statement

hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.Please post your DDL plus the statement that is giving you trouble.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"KristinaY" <KristinaY@.discussions.microsoft.com> wrote in message
news:56B41165-C240-4111-9914-04672C4FA8BF@.microsoft.com...
hi all.
I am doing a simple sql update and getting below error:
Error Messages:
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
I verified that the values specified are within the column length limits of
the table. I tried to searche for troubleshooting guidelines and couldnt
find any.
Please assist. Thanks.

Error 8152

I'm trying to add a record to a table and am getting Error 8152:
String or binary data would be truncated.
Here is my statement written with VBA in an Access project.
Both datatypes are nvarchar
Private Sub cmdCreateJob_Click()
Dim strJcn As String
Dim strParcTag As String
Dim strSql As String
strJcn = 999999
strParcTag = Me.cboEquipID.Column(0)
strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES ('strJcn','strParcTag')"
DoCmd.RunSQL strSql
End Sub
Can anyone see what I'm doing wrong? Thanks.Can you post your DDL for table ESR?
Perayu
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:E21FB5F9-31A7-4FA4-89AD-49F37A3CA0E7@.microsoft.com...
> I'm trying to add a record to a table and am getting Error 8152:
> String or binary data would be truncated.
> Here is my statement written with VBA in an Access project.
> Both datatypes are nvarchar
> Private Sub cmdCreateJob_Click()
> Dim strJcn As String
> Dim strParcTag As String
> Dim strSql As String
> strJcn = 999999
> strParcTag = Me.cboEquipID.Column(0)
> strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
> & " VALUES ('strJcn','strParcTag')"
> DoCmd.RunSQL strSql
> End Sub
> Can anyone see what I'm doing wrong? Thanks.
>
>
>|||Check the ESR_JCN column length and strJcn string length
and ESR_PARCTAG column length and strParcTag string length
The data length must be longer than column length
Thanks & Rate the Postings.
-Ravi-
"AkAlan" wrote:

> I'm trying to add a record to a table and am getting Error 8152:
> String or binary data would be truncated.
> Here is my statement written with VBA in an Access project.
> Both datatypes are nvarchar
> Private Sub cmdCreateJob_Click()
> Dim strJcn As String
> Dim strParcTag As String
> Dim strSql As String
> strJcn = 999999
> strParcTag = Me.cboEquipID.Column(0)
> strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
> & " VALUES ('strJcn','strParcTag')"
> DoCmd.RunSQL strSql
> End Sub
> Can anyone see what I'm doing wrong? Thanks.
>
>
>|||It looks like this VB(?) code is wrong: " VALUES ('strJcn','strParcTag')".
You are inserting 'strJcn', not the value of strJcn, to ESR_JCN column. You
need to try something like " VALUES ('" + integerToString(strJcn) + "', '"
+ strParcTag+ "')". But I am not sure what the syntact is for concatinate
the single quots in VB.
Perayu
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:E21FB5F9-31A7-4FA4-89AD-49F37A3CA0E7@.microsoft.com...
> I'm trying to add a record to a table and am getting Error 8152:
> String or binary data would be truncated.
> Here is my statement written with VBA in an Access project.
> Both datatypes are nvarchar
> Private Sub cmdCreateJob_Click()
> Dim strJcn As String
> Dim strParcTag As String
> Dim strSql As String
> strJcn = 999999
> strParcTag = Me.cboEquipID.Column(0)
> strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
> & " VALUES ('strJcn','strParcTag')"
> DoCmd.RunSQL strSql
> End Sub
> Can anyone see what I'm doing wrong? Thanks.
>
>
>|||Ok I got everything to work up until I add the datOpenDate field. It only
puts 12:00 AM in the record when I put 1 mar 06 in the form. Here is what I
have so far:
I have tried formatting the datefield but no go. Thanks,
strJcn = "'" & NextJCN() & "'"
strParcTag = "'" & Me.cboEquipID.Column(0) & "'"
strPerfWc = "'" & Me.cboPWC & "'"
strRptby = "'" & Me.cboReportedBy.Column(0) & "'"
strDisc = "'" & Me.txtDisc & "'"
datOpenDate = “’” & Me.txtOpenDate & “’”
strSQL = "INSERT INTO ESR
(ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY,
ESR_OPEN_DATE)" _
& " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "" _
& "," & strRptby & ", " & datOpenDate & " )"
DoCmd.RunSQL strSQL
"Perayu" wrote:

> It looks like this VB(?) code is wrong: " VALUES ('strJcn','strParcTag')"
.
> You are inserting 'strJcn', not the value of strJcn, to ESR_JCN column. Yo
u
> need to try something like " VALUES ('" + integerToString(strJcn) + "', '
"
> + strParcTag+ "')". But I am not sure what the syntact is for concatinate
> the single quots in VB.
> Perayu
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:E21FB5F9-31A7-4FA4-89AD-49F37A3CA0E7@.microsoft.com...
>
>|||It looks like you have typo here: datOpenDate = "'" & Me.txtOpenDate & "'".
"'" should be "'".
Perayu
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:2ABA6908-E32F-4C09-9BC0-BCF089F15C0F@.microsoft.com...
> Ok I got everything to work up until I add the datOpenDate field. It only
> puts 12:00 AM in the record when I put 1 mar 06 in the form. Here is what
> I
> have so far:
> I have tried formatting the datefield but no go. Thanks,
> strJcn = "'" & NextJCN() & "'"
> strParcTag = "'" & Me.cboEquipID.Column(0) & "'"
> strPerfWc = "'" & Me.cboPWC & "'"
> strRptby = "'" & Me.cboReportedBy.Column(0) & "'"
> strDisc = "'" & Me.txtDisc & "'"
> datOpenDate = "'" & Me.txtOpenDate & "'"
> strSQL = "INSERT INTO ESR
> (ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY,
ESR_OPEN_DATE)" _
> & " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "" _
> & "," & strRptby & ", " & datOpenDate & " )"
> DoCmd.RunSQL strSQL
>
> "Perayu" wrote:
>