Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: Minor Error During Insert

    Hello experts,

    Im 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.

    Ive 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 Im passing all the values its needs. But still couldnt figure out the above error.

    Thanks a lot in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think your objectname column should be at least 132 characters, the length of the system datatype.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...can you tell us what you are actually trying to accomplish instead?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2009
    Posts
    28
    Thanks a lot guys for you input. Yes the problem was varchar length. After i redefined my table as follows it worked.
    Create Table #TempTotalTable(
    DBname varchar(100),
    TBId nchar(100),
    TBName varchar(500),
    CapturedDate datetime,
    TBCreationData datetime,
    TBLastModifiedDate datetime,
    NoOfCol int
    )

    Thanks again

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think you were paying close enough attention...
    132 characters, not 100 characters.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •