server: WHERE clause not being executed

Hi all,
One of our companies is having problems with a query on a linked server.
They have two servers, serverA and serverB. On serverA they have set up a
linked server to serverB.
Query: select * from oas_company where code = 'TEST'
If I look on serverB via Profiler the query is executed without the where
statement.
Query: select * from oas_company where tstamp = 0
Again via profiler I can see that the where statement is now included. The
tstamp field is a numerical field, the code field is a text field (don's ask
me why)
So it looks like if there is a selection on a text field (varchar) the where
statement is not included.
For the above selection this is not a problem as this table only contains
10 - 20 records. However the real problem is on the oas_balance table. It
should only return 3 records, but because it skips the where statement, it
returns 10.000.000 records.
Any idea? I know the default collations are different on both servers, could
that be the cause of the problem? And if so, what could I do to overcome
this problem?
Any suggestions would be greatly appreciated.
BoBHi
Have you tried:
select * from oas_company where code = N'TEST'
or
select * from oas_company where code IN (N'TEST')
Sometimes the re-writing of a query might solve the problem for a specific
build of SQL Server.
Regards
Mike
"BoB Teijema" wrote:
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers, could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>|||"BoB Teijema" <BTE@.euroforum.nl> wrote in message
news:csnvc4$eeg$1@.reader11.wxs.nl...
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's
> ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the
> where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers,
> could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>
Presumably your real query looks like this? Or are you using OPENQUERY()
perhaps?
select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
You don't mention your MSSQL version(s), but a bit of Googling shows that
there are sometimes issues with linked servers having different collations,
especially with SQL 7:
http://support.microsoft.com/default.aspx?scid=kb;en-us;276225
http://groups.google.ch/groups?q=sql%202000%20linked%20server%20collation&hl=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
The information in BOL (for SQL 2000) seems to suggest that if both servers
are running SQL Server, then collations shouldn't be a problem, however it
isn't particularly clear - see "Optimizing Distributed Queries," for
example. Assuming you have SQL 2000, you could experiment with using COLLATE
in your queries to see if forcing a different collation does make a
difference:
http://groups.google.ch/groups?hl=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
If you don't get any more useful suggestions, and if tinkering with COLLATE
doesn't help, I would consider contacting MS - the effect of collations on
remote query plans is a fairly obscure area (at least for me), so it might
be worth opening a case with PSS.
Simon|||Thanks for your reply. We will investigate your solution asap.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41efb920$1_1@.news.bluewin.ch...
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
> > Hi all,
> >
> >
> >
> > One of our companies is having problems with a query on a linked server.
> > They have two servers, serverA and serverB. On serverA they have set up
a
> > linked server to serverB.
> >
> > Query: select * from oas_company where code = 'TEST'
> >
> > If I look on serverB via Profiler the query is executed without the
where
> > statement.
> >
> > Query: select * from oas_company where tstamp = 0
> >
> > Again via profiler I can see that the where statement is now included.
The
> > tstamp field is a numerical field, the code field is a text field (don's
> > ask
> > me why)
> >
> > So it looks like if there is a selection on a text field (varchar) the
> > where
> > statement is not included.
> >
> > For the above selection this is not a problem as this table only
contains
> > 10 - 20 records. However the real problem is on the oas_balance table.
It
> > should only return 3 records, but because it skips the where statement,
it
> > returns 10.000.000 records.
> >
> > Any idea? I know the default collations are different on both servers,
> > could
> > that be the cause of the problem? And if so, what could I do to overcome
> > this problem?
> >
> >
> >
> > Any suggestions would be greatly appreciated.
> >
> >
> >
> > BoB
> >
> >
> >
> Presumably your real query looks like this? Or are you using OPENQUERY()
> perhaps?
> select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
> You don't mention your MSSQL version(s), but a bit of Googling shows that
> there are sometimes issues with linked servers having different
collations,
> especially with SQL 7:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;276225
>
http://groups.google.ch/groups?q=sql%202000%20linked%20server%20collation&hl
=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
> The information in BOL (for SQL 2000) seems to suggest that if both
servers
> are running SQL Server, then collations shouldn't be a problem, however it
> isn't particularly clear - see "Optimizing Distributed Queries," for
> example. Assuming you have SQL 2000, you could experiment with using
COLLATE
> in your queries to see if forcing a different collation does make a
> difference:
>
http://groups.google.ch/groups?hl=en&lr=&client=firefox-a&rls=org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
> If you don't get any more useful suggestions, and if tinkering with
COLLATE
> doesn't help, I would consider contacting MS - the effect of collations on
> remote query plans is a fairly obscure area (at least for me), so it might
> be worth opening a case with PSS.
> Simon
>