String or binary data would be truncated.

Question

String or binary data would be truncated.

Viewing 11 posts - 16 through 25 (of 25 total)

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

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 1

Now, I ran the code which Jeff has provided. It's working fine when I run as a select command but when I try to convert into a stored procedure I get the same error. " String and Binary Data would be truncated". This is happening only on particular database. I have checked all the option of this database and compared to other database and it's same.

really don't know what is going on.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Are the other databases that it works on in the same server?

If the answer to this is Yes, then goto the DB that does not work, right-click on it in SSMS and select "Script Database As .. Create" and post the results here. Then do the same to a database on the same server where it does work. Post that here also.

If you are concerned about security info, just strip out the file names in the beginning.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

I think that you should also check for a Create Proc DDL trigger on this database.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

balbirsinghsodhi (8/31/2008)
Now, I ran the code which Jeff has provided. It's working fine when I run as a select command but when I try to convert into a stored procedure I get the same error. " String and Binary Data would be truncated". This is happening only on particular database. I have checked all the option of this database and compared to other database and it's same.
really don't know what is going on.

1. If you run the code as just a SELECT on THAT database, do you also get the error?

2. If you run DBCC CHECKDB on THAT database, do you get any errors?

3. Shot in the dark, but do any tables in THAT database have "Text In Row" active?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

rbarryyoung (8/31/2008)
I think that you should also check for a Create Proc DDL trigger on this database.

I think you're on to something there, Barry...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

You can check for Database level DDL Trigers with this command:

Select * from [DatabaseName.]sys.triggers Where parent_class_desc = 'DATABASE'

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 7

There was a DDL trigger on that database and I deleted that trigger and ran the code as a stored procedure and it's working fine. You guys rock. Thanks a lot.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

balbirsinghsodhi (9/1/2008)
There was a DDL trigger on that database and I deleted that trigger and ran the code as a stored procedure and it's working fine. You guys rock. Thanks a lot.

Heh... Glad you found it, but I'm thinking that that trigger was there for a reason. It may have been better to just disable it, test your code, and then find out who put it there and why. 😉 Or, at least make a copy of it, first. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 10

balbirsinghsodhi

SSCrazy Eights

Points: 9210

September 2, 2008 at 9:16 am

#866041

Sure, I will do that.

LS-487884 SSC Rookie Points: 34 More actions · Answer 11

I had the same issue. I ran the query and found that there was indeed a database level DDL trigger on the database that was causing the problems. It was added by an update that was done the day before so I know that it wasn't needed as the update had something to do with Red Gate Tools. Great code snippet and solution.....Kudos!