|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Code and diff sql serversI'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. ?? Show quote "mikeb" <m***@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 Show quote On Fri, 17 Feb 2006 14:03:14 -0800, "mikeb" <m***@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" <m***@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? >> >> >> > 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) Show quote "Roy Harvey" <roy_har***@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" <m***@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" <m***@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? >>> >>> >>> >> 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. Show quote "mikeb" <m***@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_har***@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" <m***@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" <m***@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? >>>> >>>> >>>> >>> > > 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. Show quote "Roy Harvey" <roy_har***@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  |
|||||||||||||||||||||||
Other interesting topics