Accessing Same Remote Table Form Two Linked Servers Return Different Results

We have two SQL Server 2000 instances A and B, both have
a linked server "NAR" defined to access the same database
also called "NAR" at a third remote SQL Server instance.
But executing the following query returns different
results: A returns NULL result set, while B returns the
expected result set.
The query simply selects the new or updated transactions
in the remote table based on a timestamp.
What could be the reason causing this different result?
What makes things even more complicated is that, While
executing on A, not all dates returns NULL set, some of
the dates return result set just fine. While on B, it
always return expected result set no matter what dates you
set. This is what we expected. But how do you explain this
behavior on A?
Select s.TransID
From NAR.NAR.dbo.StoreTransaction s
Where s.CreateTimeStamp > '2004-03-02 18:00:00'
And s.CreateTimestamp <= getdate()
OR
s.updateTimeStamp > '2004-03-02 18:00:00'
And s.updateTimestamp <= getdate()- What results do you get if you were to execute that query on NAR itself?
Same results as from B?
- Have you tried using OPENQUERY when running the query from A to see what
results you get?
- Another suggestion might be to run a profiler trace when the query is
being executed from A->NAR and B->NAR and check for differences.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Thank you for your reply, I posted the problem in many places and to
many so-called SQL experts, nobody replied so far except for you.
Regarding your suggestion:
- What results do you get if you were to execute that query on NAR
itself?
Good point! It occurred to me last week that it may be a problem on NAR
itself. And IT IS! I ran the query in SQL ANALYZER of NAR instance
changing the from clause to reference table StoreTransaction directly,
what is interesting is:
Select count(*) returns correct number in a few seconds, but, select any
column or run all listed columnsreturns NULL after running for almost 2
minutes.
e.g, select count(*) OR Select count(Transid)
returns correct number, select Transid returns NULL.
The from and where clause is exactly the same.
And this problem can be found only on certain dates. For example, Mar.
22 is not OK, but March 23 is fine.
It seems to be data related, but how do you explain aggregate returns
corerct results?
- Have you tried using OPENQUERY when running the query from A to see
what
results you get?
How do I use OPENQUERY in SQL ANALYZER?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!