Post reply

Display More Than 8000 Characters (SQL Spackle)

  • January 27, 2011 at 3:06 pm

    #1279422

    nadabadan (1/27/2011)

    DECLARE @v-2 varchar(max)

    DECLARE @t varchar(max)

    SET @t = 'abcdefghij'

    SELECT @v-2 = REPLICATE( @t, 10000 )

    SELECT @v-2

    If you copy and paste the value in the cell, what is the length of the copied string? 100000 or 65535?

    In SSMS2005 - 65535, in SSMS2008r2 - 43680. Possibly due to a setting in one or both of my environments, but in either case way more than the 8000 character limit which was the basis for the article. If you need more than that, then return the value as XML. Using your sample code it would be along the lines of

    DECLARE @v-2 varchar(max)

    DECLARE @t varchar(max)

    SET @t = 'abcdefghij'

    SELECT @v-2 = REPLICATE( @t, 10000 )

    SELECT @v-2 t for xml raw

    This returns a link that can be clicked to view the full length (100000 characters plus the XML tags in this case). I tested it to around 2MB with no problems.

    Matt.

  • January 27, 2011 at 4:25 pm

    #1279458

    Nice article Jeff. I tried to rate it 5 stars, but think i accidentally rated it 1 star. Very useful for visually inspecting the contents of large variables. I still wish MSFT would just go ahead and fix this, though.

    Thanks,

    Bill

  • January 27, 2011 at 7:20 pm

    #1279480

    matt stockham (1/27/2011)

    nadabadan (1/27/2011)

    DECLARE @v-2 varchar(max)

    DECLARE @t varchar(max)

    SET @t = 'abcdefghij'

    SELECT @v-2 = REPLICATE( @t, 10000 )

    SELECT @v-2

    If you copy and paste the value in the cell, what is the length of the copied string? 100000 or 65535?

    In SSMS2005 - 65535, in SSMS2008r2 - 43680. Possibly due to a setting in one or both of my environments, but in either case way more than the 8000 character limit which was the basis for the article. If you need more than that, then return the value as XML. Using your sample code it would be along the lines of

    DECLARE @v-2 varchar(max)

    DECLARE @t varchar(max)

    SET @t = 'abcdefghij'

    SELECT @v-2 = REPLICATE( @t, 10000 )

    SELECT @v-2 t for xml raw

    This returns a link that can be clicked to view the full length (100000 characters plus the XML tags in this case). I tested it to around 2MB with no problems.

    Matt.

    And that's quite a bit more than "... if you just want to see the content of a long field then the easiest way is to copy the value (ctrl-c) from the cell and paste it (ctrl-v) into the query window."

  • January 27, 2011 at 9:34 pm

    #1279491

    nadabadan (1/27/2011)

    And that's quite a bit more than "... if you just want to see the content of a long field then the easiest way is to copy the value (ctrl-c) from the cell and paste it (ctrl-v) into the query window."

    If pressing 14 keys instead of 4 is "quite a bit more" then you are absolutely correct. Cut and paste probably works fine for most situations.

    Either way, the point is that you don't need to write queries with moderately complex string manipulation (which certainly need some testing to confirm accuracy) to get this information. That doesn't mean that the techniques in the article aren't valid or useful, just that they aren't the simplest or quickest means to this end.

    Matt.

  • January 28, 2011 at 6:44 am

    #1279671

    Just gotta love the things you can do with a Numbers table!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • January 28, 2011 at 1:05 pm

    #1279907

    Hey Jeff, I'm late here, but really this is very nice article, thanks for sharing!

    Wish you more success my friend,

    Dugi

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • January 31, 2011 at 11:14 am

    #1280528

    Believe it or not, this kind of thing can have a production purpose. I have to send "narrative" from our database to a database in another agency. The other agency set their narrative column to have a max of X characters even though the actual data would be much bigger than X. (Don't ask why.) When they display the narrative to the user on their web application, they string the text from multiple rows together. When the narrative is saved to the database, they break it back up into X chunks/rows.

    So, when I send the agency our data, I have to break it into chunks of size X. It doesn't mater where the break is, and it doesn't even matter if there is an extra row at the end. So, this kind of technique could work great for my production purpose. (Note: I don't currently use a loop. I also don't use this spackle method. I will do some research first, but I will likely switch methods in the future.)

  • January 31, 2011 at 11:35 am

    #1280541

    Hi Jeff, Great article as usual. 🙂

    -Roy

  • February 1, 2011 at 3:43 pm

    #1281172

    I have run into this problem off and on, and once even wrote a solution to it ( http://www.sqlservercentral.com/scripts/Print/63240/[/url] ). After struggling with it myself a few times, I am in awe of the elegance of your solution. It is very clean and effecient and easy to understand.

    One challenge that I think remains in your technique is that the output is in the form of a select statement rather than a print. While you can certain output to text as you mentioned, that can be problematic if your are troubleshooting something and want other output to come out as a grid. Still, an absolutely beautiful piece of code.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • February 2, 2011 at 10:53 pm

    #1281723

    TheSQLGuru (1/28/2011)

    Just gotta love the things you can do with a Numbers table!

    They do make certain things in life a whole lot easier. Thanks for stopping by, Kevin.

    --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)

  • February 2, 2011 at 10:54 pm

    #1281724

    Dugi (1/28/2011)

    Hey Jeff, I'm late here, but really this is very nice article, thanks for sharing!

    Wish you more success my friend,

    Dugi

    No problem. Thanks for the read and the kind words, Dugi!

    --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)

  • February 2, 2011 at 10:56 pm

    #1281725

    JJ B (1/31/2011)

    Believe it or not, this kind of thing can have a production purpose. I have to send "narrative" from our database to a database in another agency. The other agency set their narrative column to have a max of X characters even though the actual data would be much bigger than X. (Don't ask why.) When they display the narrative to the user on their web application, they string the text from multiple rows together. When the narrative is saved to the database, they break it back up into X chunks/rows.

    So, when I send the agency our data, I have to break it into chunks of size X. It doesn't mater where the break is, and it doesn't even matter if there is an extra row at the end. So, this kind of technique could work great for my production purpose. (Note: I don't currently use a loop. I also don't use this spackle method. I will do some research first, but I will likely switch methods in the future.)

    Very cool feedback, JJ B. If you're not currently using a loop or the method in the article, what are you using?

    --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)

  • February 3, 2011 at 6:59 am

    #1281887

    Roy Ernest (1/31/2011)

    Hi Jeff, Great article as usual. 🙂

    Hi Roy! Thanks for the read and the comment. Always a pleasure.

    --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)

  • February 3, 2011 at 7:07 am

    #1281891

    timothyawiseman (2/1/2011)

    I have run into this problem off and on, and once even wrote a solution to it ( http://www.sqlservercentral.com/scripts/Print/63240/[/url] ). After struggling with it myself a few times, I am in awe of the elegance of your solution. It is very clean and effecient and easy to understand.

    One challenge that I think remains in your technique is that the output is in the form of a select statement rather than a print. While you can certain output to text as you mentioned, that can be problematic if your are troubleshooting something and want other output to come out as a grid. Still, an absolutely beautiful piece of code.

    Wow! :blush: I'm humbled, Timothy. Thanks for the very kind words and the awesome feedback.

    I absolutely agree... the grid/text mix is very helpful in troubleshooting and forcing the results to the Messages tab to see a bit of correctly formatted dynamic SQL along with the data it produced works best when the grid/text mix is available.

    --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)

  • February 3, 2011 at 9:27 am

    #1281992

    JJ B. If you're not currently using a loop or the method in the article, what are you using?

    I'll tell ya, but you have to be kind (not that you wouldn't be). I put this together several years ago. I didn't have access to rank functions at the time, and I made this up myself. Also, this is run once a night on very few records (less than 50). So, stressing about being terribly efficient didn't make sense.

    It is a multi-step stored proc. The proc does use what you call a tally table, but not in the same, most likely more efficient way, as you did. Below are comments copied from my code. If they don't make sense and you are still interested, I could attach the proc - which has more comments and actual SQL. 🙂

    /*

    So, just how is the text broken out?

    There were two general possible approaches. I don't know which is faster, but

    speed is not a major issue here. I just liked the approach taken here best. It

    seems the cleanest.

    The approach not taken: Use cursors and a couple embedded loops to go through

    each applicable record and then to break out each applicable text chunk in each

    record.

    The lovable approach used below: Three steps:

    a) for each return record, create copies in @RetTable. The number of copies created for all records equals the maximum possible number of chunks needed for

    the data being exported now. A simple DataLength() function will tell us

    the length of the largest narrative in the data to be exported. If you divide

    that number by the ChunkLength and round up, you get the maximum number of

    chunks needed (and is the number of duplicate records we want to create in

    this step). For example, if the record with the largest narrative will need no more than 3

    records/chunks, then all the narrative records will essentially be unioned

    to themselves/duplicated 3 times in this step. This data will be stored in our return

    table variable, @RetTable.

    Note that we give each duplicate record a sequence number using the Number table. In the

    example used here, the duplicate allegation narrative records will have

    sequence numbers 1, 2 and 3.

    b) run a query to delete records which do not have enough text to fill up any

    part of that chunk. Continuing the example from above, if allegation 234

    has a narrative that takes 3 chuncks/records, then step a) above makes 3

    three copies of each allegation, including allegation say 543, which say only

    needs two chunks. So, in this step, we delete the record for the record

    belonging to the third sequence number for allegation narrative 543. If

    any allegations only need one chunk, then we delete the records for the last

    sequence numbers. The where clause in our query already eliminates narratives

    records without any text.

    c) run a query that uses SUBSTRING to make each remaining record only have

    the relevant chunk of text needed for that sequence number. For example,

    the records belonging to sequence/chunk #1 will delete everything after the

    first 8,000 characters. The records belonging to sequence 2 will delete

    all data except for characters 8,001 through 16,000.

    */

Viewing 15 posts - 31 through 45 (of 71 total)

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