Linked server SQL 7 <==>SQL 2k

[Sabnac]

the situation :
Server A :
SQL 7 with linked server on B (ole db driver)
MyDB_A.table : 50 rows

Server B :
SQL 2K with linked server on A (ole db driver)
MyDB_B.table : 40 rows

The problems :
On A
select count(*) from B.MyDB_B.dbo.table
-> 50 rows

On B
select count(*) from B.MyDB_B.dbo.table
-> 40 rows

The Question :
Why my queries are always returning rows from the current server and not from the linked as demanded ?

 

[tlbroadbent]

Must be a problem with the linked server properties. What result do you get when running the following.

Server B:
select count(*) from MyDB_B.dbo.table

Server A:
Select Rows
From OpenQuery(B, 'Select Rows=count(*) From MyDB_B.dbo.table') Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Sabnac]

same problem

Server B:
select count(*) from MyDB_B.dbo.table
40 rows

Server A:
Select Rows
From OpenQuery(B, 'Select Rows=count(*) From MyDB_B.dbo.table')
50 rows

i dont understand why i dont get an error message instead of false result

 

[tlbroadbent]

The linked server properties must not be correct on Server A. Are you sure that the linked server B on Server A isn't actually pointing back to A? Why are you using the OLEDB Driver for SQL Server rather than a SQL Server type? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Sabnac]

I am really lost with this linked servers.
I am sure that the linked is the good one (I have use ip adresse to be sure)
When i use SQL server type i cannot configure any security.

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGIN'"
and i use the sa login in the "be made using this security context" boxes

I read somewhere i have to apply a inscat.sql script. Do you know something about that ?

 

[tlbroadbent]

Do you mean instcat.sql? Do a search in SQL BOL to determine if you need to run the instcat.sql script on SQL 7.0. This file is provided with SQL 2000 but is run on SQL 7. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.