By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Linked Server: WHERE clause not being executed

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"BoB Teijema" <BT*@euroforum.nl> wrote in message
news:cs**********@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...b;en-us;276225
http://groups.google.ch/groups?q=sql...al&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...TNGP09.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
Jul 23 '05 #2

P: n/a
Thanks for your reply. We will investigate your solution asap.
"Simon Hayes" <sq*@hayes.ch> wrote in message
news:41**********@news.bluewin.ch...

"BoB Teijema" <BT*@euroforum.nl> wrote in message
news:cs**********@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...b;en-us;276225
http://groups.google.ch/groups?q=sql...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...org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.p hx.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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.