Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Question Unanswered: SQL select in xp_sendmail...what's up with the space after?

    HI all, I am using xp_sendmail to select from a temporary table some data that wants to get out to the real world from the database.

    The select is easy, as it just involves a simple select of a varchar(1200) from a temporary table.

    What I'm seeing though (WITHOUT the assistance of Mr. Cuervo in this instance ) is that in the email message, even if my varchar only has 20 characters in it, that the select in the email is padded with blanks out to what appears to be the full 1200 byte maximum in the varchar (for each row inserted into the email).

    I have used "RTRIM(myvarchar)" both during writing to the temp table, and in the xp_sendmail select variable, and STILL get the blanks on the end.

    Anybody know any easy way to drop my extra padding on my varchar when it's added to the email (embedded in the actual email).

    Tanks!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    SELECT
    CAST (column AS VARCHAR(55))
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Hey! Why didn't I think of that? Actually, my data is variable length, but I suppose I could capture the length of the data and put it in the cast maybe? Hmmmm...guess I'll have to play with that some.

    Alas...nope...tried
    Code:
    select CAST(message AS varchar(LEN(message))) from eventlog where msglevel = 2
    and (as expected) it won't play nice with me.

    thanks anywho...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    hmmmmm

    For fun try:

    select max(len(rtrim(ltrim(message)))) from eventlog
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    well, you're right, it WAS fun but all it did for me was tell me my longest string (at the moment) is 247 bytes long...

    Also, thinking (uh-oh, trouble) that perhaps you were leading me toward something else, I also slapped that select inside the varchar size definition in the previous select, and again, it called me stupid and laughed at me! I don't think it wants me defining the varchar size on the fly like that.

    The select SEEMS to work just fine outside of the sp_sendmail call...I just wonder why it's ignoring my explicit directions...Hey...maybe it was written by a 17-year-old daugther?!?!?!?! (oops, sorry, I'm projecting again...)
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Hmmm...it appears I am not the first to tread this path...
    http://dbforums.com/t417771.html

    (though it would be cool if the forum's search function showed me this rather than the Dogpile search > )
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just being perverse here, but humor me for a minute and try something like:
    Code:
    SELECT *
       FROM (SELECT Cast(big_column AS VARCHAR(255) AS b
          FROM myTable AS c) AS a
    -PatP

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You're perverse Pat. If that doesn't work, do the select from earlier in one stored procedure. In you xp_sendmail procedure, EXEC that proc. Or maybe that's what you're already doing???????????
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Pat Phelan
    I'm just being perverse here, but humor me for a minute and try something like:
    Code:
    SELECT *
       FROM (SELECT Cast(big_column AS VARCHAR(255) AS b
          FROM myTable AS c) AS a
    -PatP
    OK, that worked...now...any hints as to WHY???? *LOL* and will I get into trouble if I happen to have a big_column with a length greater than 255? (remember, they are variable size, up to a max of 1200 bytes - though most will be around the 250-byte size.

    The results actually show up with a column name of 'b' also...that confuses me further.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    In fact, I tried it with a smaller CAST size, and indeed (as expected) it chopped off my column contents...so I would also expect that result if my column text has a size greater than 255 as in the original suggestion/test.

    I can't figure why your suggestion would be any different than the original attempt at a cast ala
    Code:
     select CAST(big_column AS Varchar(255)) from MyTable
    - is there an underlying functionality/quirk that you were shooting for?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It comes from having a perverse nature!

    The column width used by TDS (the Tabular Data Stream) format is determined by the SQL Server metadata. By using the virtual table, I changed the underlying metadata as well as changing the actual data itself, which is also why you came up with a column name of b because that is also part of the virtual table's metadata.

    See, sometimes it helps to have perverse friends!

    -PatP

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Hey, I'd be the first one to line up in the "perverse friends" line, believe you me! And keep my shelves lined with more of the same. One day the perverse will rise up!....errr...I mean "get down"! Errr...I mean "Slip into the mainstream"...hmmmm...well, you know what I mean

    So then, besides needing to read up on the TDS concept (and oh-so-many-other things) I need to play around to see if the 255 thing (which is a limit, correct? - I mean, if I raise it to 1200 I am likely to see the same results I originally saw...) can be made workable in my situation.

    Off to try the same thing with 1200 to verify my expectations.

    Thanks again for the lesson in perversity though, regardless...it's a subject at which I always try to keep up on...err...I mean...keep abreast of...wait...ummm... I mean "learn more about" sheesh
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Don't get too excited there... TDS is only a transport protocol that gets tabular formatted data from point A (the server) to point B (the client/consumer).

    If you are looking for an opportunity to "study up" on T&A, you are better off to concentrate on TCP/IP and ASCII than you are on TDS and Asynch Data Transfer. The more general stuff (TCP/IP and ASCII) will see 100 times the use that the specific stuff does, although I guess when you really need the GQ (geek quotient) effect, then TDS is really handy.

    -PatP

  14. #14
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    well...as expected, if I changed your code from 255 to 1200, I get the same extended, and padded results as I originally did.

    I also tried my original code modified as
    Code:
    ELECT CAST(EventDesc AS varchar(255)) FROM EventLogTbl ORDER BY EvtID'
    and got the same result as your more perverted code...

    *scratching head*

    So, my lesson learned is that in SQL Server work, as in "real life", the underlying perversions often have no real bearing on the functionality of the individual (task)...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Now, if you wanted to be really twisted, you could use:
    Code:
    DECLARE @c VARCHAR(10)
    
    SELECT @i = Str(Max(Len(EventDesc)))
       FROM EventLogTbl
    
    EXECUTE ('SELECT Cast(EventDesc AS varchar(' + @c + '))
       FROM EventLogTbl
       ORDER BY EvtID')
    This gives you customized perversion, tailored to your needs, at the cost of a second pass through your table!

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