Re: Linked Server: WHERE clause not being executed
From: BoB Teijema (BTE_at_euroforum.nl)
Date: 01/20/05
- Next message: RandyH: "Re: SQL Server 7.0"
- Previous message: Anonymous: "Insufficent Memory Available message when running query"
- In reply to: Simon Hayes: "Re: Linked Server: WHERE clause not being executed"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 20 Jan 2005 15:57:53 +0100
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
>
>
- Next message: RandyH: "Re: SQL Server 7.0"
- Previous message: Anonymous: "Insufficent Memory Available message when running query"
- In reply to: Simon Hayes: "Re: Linked Server: WHERE clause not being executed"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
