Code and diff sql servers

I'm running out of ideas...
Given the following example of code:
set @.partialname = 'u'
SELECT col1, col2, col3
FROM tbl1
WHERE col1 LIKE @.partialName + '%'
ORDER BY col1
Why would two different sql servers, having exactly the same data, give
different results? One server returns an empty set, while the other returns
all rows where col1 starts with 'u'. A server setting I'm guessing, but
can't find what it is.
Can you help?crud - I messed up my explanation- it works with the 'u' but not when
@.partialname is blank ('').
Its this code that works on one server, but not the other:
set @.partialname = ''
SELECT col1, col2, col3
FROM tbl1
WHERE col1 LIKE @.partialName + '%'
ORDER BY col1
If @.partialname = 'u' -- both servers process correctly.
Sorry about that.
'
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:uMmFbzANGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I'm running out of ideas...
> Given the following example of code:
> set @.partialname = 'u'
> SELECT col1, col2, col3
> FROM tbl1
> WHERE col1 LIKE @.partialName + '%'
> ORDER BY col1
> Why would two different sql servers, having exactly the same data, give
> different results? One server returns an empty set, while the other
> returns all rows where col1 starts with 'u'. A server setting I'm
> guessing, but can't find what it is.
> Can you help?
>
>|||My guess is that the one thing you do not show - the data type of
partialname - is the problem. Is it, by any chance, CHAR(1)? If so
you are matching on ' %' when it is blank. Try making it varchar.
And you might have tried a little research of your own:
set @.partialname = ''
SELECT @.partialName + '%'
Roy
On Fri, 17 Feb 2006 14:03:14 -0800, "mikeb" <mike@.nohostanywhere.com>
wrote:

>crud - I messed up my explanation- it works with the 'u' but not when
>@.partialname is blank ('').
>Its this code that works on one server, but not the other:
>set @.partialname = ''
>SELECT col1, col2, col3
>FROM tbl1
>WHERE col1 LIKE @.partialName + '%'
>ORDER BY col1
>If @.partialname = 'u' -- both servers process correctly.
>Sorry about that.
>'
>"mikeb" <mike@.nohostanywhere.com> wrote in message
>news:uMmFbzANGHA.3908@.TK2MSFTNGP10.phx.gbl...
>|||TRIED RESEARCH OF MY OWN? I've done tons of searches Roy, spent the last
couple hours trying different options. ALL before posting.
You might want to get your crystal ball in for repair - it doesn't seem to
be working today...
@.partialname is VarChar(50)
It appears that the other database is SQL7, versus SQL2000 (which works)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31mcv1lrouvu2dri9u7b0rbt19a91i4gcv@.
4ax.com...
> My guess is that the one thing you do not show - the data type of
> partialname - is the problem. Is it, by any chance, CHAR(1)? If so
> you are matching on ' %' when it is blank. Try making it varchar.
> And you might have tried a little research of your own:
> set @.partialname = ''
> SELECT @.partialName + '%'
> Roy
>
> On Fri, 17 Feb 2006 14:03:14 -0800, "mikeb" <mike@.nohostanywhere.com>
> wrote:
>|||It seems that the difference was that even though @.partialName, a
varchar(50), was passed an empty string ('') to the s.proc, SQL7 somehow
converted it to a single blank char (' '). Where SQL2000 left it empty. I
could very well be doing something wrong here - I'm just trying to fix an
error in what code we were left with. Open to suggestions if its bad form.
Wow. I even kept researching after my hand was slapped for not (sic)...
pomposity gets really tiring sometimes.
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:udUKw1BNGHA.2752@.TK2MSFTNGP14.phx.gbl...
> TRIED RESEARCH OF MY OWN? I've done tons of searches Roy, spent the last
> couple hours trying different options. ALL before posting.
> You might want to get your crystal ball in for repair - it doesn't seem to
> be working today...
> @.partialname is VarChar(50)
> It appears that the other database is SQL7, versus SQL2000 (which works)
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:31mcv1lrouvu2dri9u7b0rbt19a91i4gcv@.
4ax.com...
>|||Sorry it came across that way. My apologies.
You should be able to get around the problem with:
RTRIM(@.partialName) + '%'
Roy|||Yep, thats exactly what I did to get it working - I meant to mention that
too in the previous post. thx.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:27scv1l4fqtv3hrvuf0msr6q6elv7fl5ej@.
4ax.com...
> Sorry it came across that way. My apologies.
> You should be able to get around the problem with:
> RTRIM(@.partialName) + '%'
> Roy