Minor Error during Insert

Question

Post reply

Minor Error during Insert

mr_adeelalisyed

SSC-Addicted

Points: 470
More actions
November 13, 2009 at 10:27 am

#96026

Hello experts,
I’m trying to insert some values though stored procedure into one of my temporary table but having the following errors again and again.
Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
I’ve tried to change all the values both length and data type without any luck. Can anybody see problem with the following code?
------------------------------------------------
Create Table #TempTotalTable(
DBname varchar(50),
TBId nchar(50),
TBName varchar(50),
CapturedDate datetime,
TBCreationData datetime,
TBLastModifiedDate datetime,
NoOfCol int
)
sp_msForEachdb
'INSERT INTO #TempTotalTable
SELECT
''?'',
object_id as ID,
name as TableName,
getdate() as CapturedDate,
create_date as CreationDate,
modify_date as LastModifiedDate,
null as NoOfColumns
FROM ?.sys.objects
WHERE type_desc = ''USER_TABLE'''
select * from #temptotaltable
-----------------------------------------------
However when I just use the following stored procedure to just fetch data it works absolutely fine.
------------------------------------------------
sp_msForEachdb
'
SELECT
''?'',
object_id as ID,
name as TableName,
getdate() as CapturedDate,
create_date as CreationDate,
modify_date as LastModifiedDate,
null as NoOfColumns
FROM ?.sys.objects
WHERE type_desc = ''USER_TABLE'''
------------------------------------------------
Which shows nothing is wrong with the stored procedure and I’m passing all the values its needs. But still couldn’t figure out the above error.
Thanks a lot in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Seems like you have database and/or table names that are longer than the 50 char you defined for your temp table.

As per BOL

database_name can be a maximum of 128 characters

table_name can be a maximum of 128 characters

I also would recommend to change column type for TBId from nchar(50) to INT.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mr_adeelalisyed SSC-Addicted Points: 470 More actions · Answer 2

Thanks a lot guys for your input. Yes the problem was varchar length. When i defined the table as follows everything worked. Once again Thanks,

Create Table #TempTotalTable(

DBname varchar(100),

TBId nchar(100),

TBName varchar(500),

CapturedDate datetime,

TBCreationData datetime,

TBLastModifiedDate datetime,

NoOfCol int

)

LutzM SSC Guru Points: 107049 More actions · Answer 3

Is there any specific reason to make the table name colum wider than it ever can get and at the same time making the DBname smalller than it might get?

I quoted BOL to give you a hint what size you should use...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

To add to that, object_id is an int, not an nchar(100). You're wasting nearly 200 bytes of space per row with that. (4 bytes vs 200)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass