Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Really strange behavior in SQL Server 2K

Expand Messages
  • John Warner
    I actually did what I was using Replicate for. I was using it to insert 10 spaces into the string in my JOIN, to me this is the most readable way to do this.
    Message 1 of 8 , Aug 31, 2007
    • 0 Attachment
      I actually did what I was using Replicate for. I was using it to insert 10
      spaces into the string in my JOIN, to me this is the most readable way to
      do this. Thinking of being nice to me and whoever might come behind me in a
      few years. (got to remember to insert a comment in the code now for this)
      So I just hard coded 10 spaces in place of Replicate and it works across
      databases. I think I've stumbled (emphasis on stumble / blind / idiocy)
      onto a bug here. It must have to do with the way the String functions
      allocate memory or such. But that is completely a guess on my part.

      Worth keeping this in mind if you ever have to 'bridge' two databases like
      I'm having to do. Lucky for me the work around was easy.

      Oh, on the EDI question, no I rarely deal in it. The field indeed has to do
      with EDI, but we simply receive some orders in that format and actually at
      a different location then where I am. The reason I'm after that EDI field
      is to provide more detail regarding some customers. That's one reason for
      the ISNULL call, a lot of the return rows have that field null regardless.

      The company here is a forest products broker (layer between the saw mill
      and your local Home Depot, that sort of thing).

      John Warner




      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
      > Sent: Friday, August 31, 2007 3:27 PM
      > To: 'SQLQueriesNoCode@yahoogroups.com'
      > Subject: RE: [SQLQueriesNoCode] Really strange behavior in
      > SQL Server 2K
      >
      >
      > John,
      > I don't have any answers for you, but I'm curious how
      > you've attributed it to Replicate(), since you haven't passed
      > any data from either DB into that function. What about other
      > DBs? If you select a different database (not Master, or
      > ltprod)? What are the data types of the columns you're
      > concatenating with the replicate() output?
      >
      > By the way, do you do a lot of EDI work?
      > Travis-
      >
      >
      > -----Original Message-----
      > From: John Warner [mailto:john@...]
      > Sent: Friday, August 31, 2007 1:48 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: RE: [SQLQueriesNoCode] Really strange behavior in
      > SQL Server 2K
      >
      >
      > Hmm, I'm posting this just to get this covered in the
      > archive, sorry for cluttering the list.
      >
      > The problem is Replicate(). I don't know exactly how to
      > describe it, but it breaks if it is working across databases.
      > It just doesn't work. I wonder if this is an undocumented
      > feature in SQL Server. More to the point I wonder of this
      > behavior has been fixed in SQL 2005 or 2008. Also would this
      > apply to all the string functions?
      >
      > John Warner
      >
      >
      >
      >
      > > -----Original Message-----
      > > From: SQLQueriesNoCode@yahoogroups.com
      > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
      > > Sent: Friday, August 31, 2007 1:32 PM
      > > To: SQLQueriesNoCode@yahoogroups.com
      > > Subject: RE: [SQLQueriesNoCode] Really strange behavior in
      > > SQL Server 2K
      > >
      > >
      > > Follow up to my question from a few minutes ago:
      > >
      > > SELECT
      > > ISNULL(cc.value1, ' ') AS EDI,
      > > LEFT(cs.cust + REPLICATE(' ', 10), 10) AS A,
      > > LEFT(cs.cust + REPLICATE(' ', 10), 10) + cs.ship AS B,
      > > cc.code
      > > FROM
      > > ltprod.dbo.cus_ship cs
      > > LEFT JOIN ltprod.dbo.cde_codes cc
      > > ON LEFT(cs.cust + REPLICATE(' ', 10), 10) +
      > cs.ship = cc.code
      > > AND cc.prefix = 'DEF-EDI-LOC'
      > >
      > > Running this query with ltprod selected and cc.code in the
      > > SELECT has data; from Master all rows are NULL? Cols A and B
      > > are populated (correctly) regardless of the database
      > > selected. What is going on???
      > >
      > > Thanks
      > >
      > > John Warner
      > >
      >
      >
      >
      >
      >
      > Yahoo! Groups Links
      >
      >
      >
      >
      >
      >
      > Yahoo! Groups Links
      >
      >
      >
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.