servers: Access 97

Hello,
I am trying to create an Linked Server for MS Access in SQL 7.0. I have
executed the following stored procedures with no problem:
exec sp_addlinkedserver
@.server = "PECRM",
@.srvproduct = "Access 97",
@.Provider = "Microsoft.Jet.OLEDB.4.0",
@.datasrc = "\\apollo\sys\apps\datastor\appdata\contdata.mdb"
exec sp_addlinkedsrvlogin
@.rmtsrvname = "PECRM",
@.useself = "false",
@.locallogin = "sa",
@.rmtuser = "Admin",
@.rmtpassword = Null
When I execute the following SQL Statement
SELECT * FROM PECRM...ContAddr
I get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\apollo\sys\apps\datastor\appdata\contdata.mdb'. It is
already opened exclusively by another user, or you need permission to view
its data.]
In SQL EM I get the dreaded 7399 error, I have followed the suggestions of
changing the SQL server login to an account that I know has access to a temp
directory but with the same results. Can any one help?
Thanks,
DanHello Solex (wonder if this is your real name, remember post with real names
here)
Seems to be that you are using a LDB file to secur your access database.
Information about this
file must be provided too. Look in the provider settings and provide the
path and name
to this security information file.
Jens Süßmeyer.|||Jens,
The access databaes is not using a WorkGroup database for security. In fact
the database is not secured at all.
Any other suggestions?
Thanks,
Dan
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:%23w%23XcV1ZDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hello Solex (wonder if this is your real name, remember post with real
names
> here)
> Seems to be that you are using a LDB file to secur your access database.
> Information about this
> file must be provided too. Look in the provider settings and provide the
> path and name
> to this security information file.
> Jens Süßmeyer.
>|||Hello Dan !
Did you set the database to exclusive ? Does another Lock-File exists, that
you can´t open the db. Look in the
directory of the mdb and search for a lck file. Delete it und try again.
Somethings Access hangs and stop responing which such suspects errors, and
there is nothing left than restarting the host system of the access mdb (if
killing all processes to the network share doesn´t help to fix it)
Jens Süßmeyer.
"solex" <solex@.nowhere.com> schrieb im Newsbeitrag
news:u4mjr41ZDHA.652@.tk2msftngp13.phx.gbl...
> Jens,
> The access databaes is not using a WorkGroup database for security. In
fact
> the database is not secured at all.
> Any other suggestions?
> Thanks,
> Dan
> "Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
> news:%23w%23XcV1ZDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > Hello Solex (wonder if this is your real name, remember post with real
> names
> > here)
> >
> > Seems to be that you are using a LDB file to secur your access database.
> > Information about this
> > file must be provided too. Look in the provider settings and provide the
> > path and name
> > to this security information file.
> >
> > Jens Süßmeyer.
> >
> >
>

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.
BoB
Hi
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...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...GP09.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
|||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...[vbcol=seagreen]
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
a[vbcol=seagreen]
where[vbcol=seagreen]
The[vbcol=seagreen]
contains[vbcol=seagreen]
It[vbcol=seagreen]
it
> 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...collation& 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
>

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 a
sk
> me why)
> So it looks like if there is a selection on a text field (varchar) the whe
re
> 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, cou
ld
> 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/defaul...kb;en-us;276225
http://groups.google.ch/groups? q=s...ial&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=e...FTNGP09.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...
a[vbcol=seagreen]
where[vbcol=seagreen]
The[vbcol=seagreen]
contains[vbcol=seagreen]
It[vbcol=seagreen]
it[vbcol=seagreen]
> 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/defaul...kb;en-us;276225
>
http://groups.google.ch/groups? q=s...ati
on&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=e...=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
>

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.

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/defaul...kb;en-us;276225
http://groups.google.ch/groups?q=sq...ial&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=e...FTNGP09.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/defaul...kb;en-us;276225
http://groups.google.ch/groups?q=sq...%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=e...=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

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
>