server Yields Inconsistent results

On SQL Server 2000 we are connecting to many AS/400 tables using Linked
Servers. We are developing a process which runs solely on SQL using Stored
Procedures. We are finding inconsistent results in the return of a query
utilizing a linked table. The query:
Insert into #tmpTableData(ControlID, ApplicationID, RVTBL, RVCNY, RVSTA,
RVPLN, PlanEffDt,PlanEndDt, RVOPT, PSDESC, PFORMN)
SELECT c.ControlID, c.ApplicationID, f.Field1, f.RVCNY, f.RVSTA, f.RVPLN,
dbo.udf_DTStr_FromDecimalMMDDYYYY (f.RVEFMM,f.RVEFDD,f.RVEFYY),
CASE When LEN(RTRIM(p.PLUSR6)) <> 0 Then cast(p.PLUSR6 as varchar(8))
ELSE Replace(convert(char(10) ,getdate() ,102) , '.' , '')
end,
f.RVOPT, p.PSDESC, p.PFORMN
FROM ( [Prod].LSPFCT.ABCTABLE f inner join [Prod].LSPFCT.PDFTABLE p
on f.RVPLN = p.PPLAN) inner join tblControlData c on f.FIELD1 =
c.BasePlanCode
WHERE c.RecordStatus = 5
And (f.RVSTA = '**' or f.RVSTA = c.IssueState)
On a given record, the join may result in 5 records, but if you run the same
query without any data changes or SP changes may result in 3 records. Next
time 5, etc. We did a test of pulling the AS/400 tables down to SQL Server
and began getting consistent results everytime. We see no error messages in
SQL Server logs. Any thoughts?I'm still looking for assistance on this issue. I thought as a Universal
Subscriber that Managed newsgroup submissions received a timely response.
Any support would be greatly appreciated. Thanks
"LisaConsult" wrote:

> On SQL Server 2000 we are connecting to many AS/400 tables using Linked
> Servers. We are developing a process which runs solely on SQL using Store
d
> Procedures. We are finding inconsistent results in the return of a query
> utilizing a linked table. The query:
> Insert into #tmpTableData(ControlID, ApplicationID, RVTBL, RVCNY, RVSTA,
> RVPLN, PlanEffDt,PlanEndDt, RVOPT, PSDESC, PFORMN)
> SELECT c.ControlID, c.ApplicationID, f.Field1, f.RVCNY, f.RVSTA, f.RVPLN,
> dbo.udf_DTStr_FromDecimalMMDDYYYY (f.RVEFMM,f.RVEFDD,f.RVEFYY),
> CASE When LEN(RTRIM(p.PLUSR6)) <> 0 Then cast(p.PLUSR6 as varchar(8))
> ELSE Replace(convert(char(10) ,getdate() ,102) , '.' , '')
> end,
> f.RVOPT, p.PSDESC, p.PFORMN
> FROM ( [Prod].LSPFCT.ABCTABLE f inner join [Prod].LSPFCT.PDFTABLE p
> on f.RVPLN = p.PPLAN) inner join tblControlData c on f.FIELD1 =
> c.BasePlanCode
> WHERE c.RecordStatus = 5
> And (f.RVSTA = '**' or f.RVSTA = c.IssueState)
> On a given record, the join may result in 5 records, but if you run the sa
me
> query without any data changes or SP changes may result in 3 records. Nex
t
> time 5, etc. We did a test of pulling the AS/400 tables down to SQL Serve
r
> and began getting consistent results everytime. We see no error messages
in
> SQL Server logs. Any thoughts?