Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: SELECT string truncation problem!

    Hi Pals,

    Need some help.

    I have big sql which is more than 4000 characters.
    Basically the sql is formed by UNION operator.
    For that reason i have declared the datatype as VARCHAR(MAX).

    and then tried to assign the value to a variable.
    and when i am trying to print the SQL using PRINT / SELECT , the sql is getting truncated.

    What i have done is instead of printing i have used SELECT LEN('SELECT ..........'). Then i am getting
    output as 8000 chars which can easily be accomodated by VARCHAR(MAX) datatype (i.e 2^31 chars.)

    Why is the sql is getting truncated? What could be the problem?

    How could we resolve this problem?
    Do we need to take 2 string variables and do that or else do we have any other solution for this.

    Basically i am inserting the result fetched by the big SELECT stmt inside my stored procedure.

    Any Thoughts?

    Any suggestions will be greatly appreciated.
    Thanks in Advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    PRINT truncates your string. I nicked a sproc called PrintBig off the web that I use. However, you'll need to use multiple variables anyway if you are executing this - 4000 in the first, 4000 in the second etc:
    Code:
    EXEC (@sql_1 + @sql_2 + @sql_3)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2007
    Posts
    41
    Thank You so much.

    Just want to know why the string is getting truncated eventhough the varchar(max) can able to accomadate the string value.

    Thanks so much!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    I nicked a sproc called PrintBig off the web that I use.
    Couldn't be bothered providing a link to that, eh Pootle?
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Couldn't be bothered providing a link to that, eh Pootle?
    It was actually b0rked and I had to implement a minor fix. Pffff - I'll dig it out.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by frank.svs
    Just want to know why the string is getting truncated eventhough the varchar(max) can able to accomadate the string value.
    The odds are near certain that your SQL client is truncating the string. What program are you using to run your query? SSMS is common for SQL2008 and SQL2005, Query Analyzer for SQL2000 and SQL 7.0, ISQL/W for SQL 6.5 and earlier.

    -PatP

Posting Permissions

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