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?Are you using sp_executesql with any params?
I noticed in-correct results when doing this from MSSQL to AS400.
Tim S|||We are in Query Analyzer, executing a storedprocedure which contains this
query. Any clues or resolutions would be appreciated.
"Tim" wrote:

> Are you using sp_executesql with any params?
> I noticed in-correct results when doing this from MSSQL to AS400.
> Tim S
>|||Hi Lisa,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood your results from Linked Server is
less than you expected randomly. If I have misunderstood your concern,
please feel free to point it out.
First of all, please double confirm that records are the same in original
database,I mean nobody else is updating the records. If so, It is really
strange when the result will be 3 records and then 5 records in the next
one.
Secondly, would you please help me collect more detailed scenario
information by answering the questions below?
- What's your database in AS/400? Is it IBM DB2?
- Does your network or AS/400 endure a high workload?
- Does the database in AS/400 is case sensitive?
- What is the OLE DB driver you are using?
Thirdly, please perform the steps below and let me know whether it will
make any contribution to your issue
1) Have you upgraded to the latest SQL Server 2000 service pack? We have
released SQL Server 2000 SP4 recently and you could download it here
http://www.microsoft.com/downloads/...fc8d-c20e-4446-
99a9-b7f0213f8bc5&DisplayLang=en
2) Please try using OPENQUERY instead of SELECT it from four part name
direcly
3) Please ensure the query will get the correct information while execute
in database of AS/400 directly.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:1F4Tyu3VFHA.3052@.TK2MSFTNGXA01.phx.gbl...
> 1) Have you upgraded to the latest SQL Server 2000 service pack? We have
> released SQL Server 2000 SP4 recently and you could download it here
> [url]http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-[/ur
l]
> 99a9-b7f0213f8bc5&DisplayLang=en
How about "next to latest SQL Server 2000 service pack"? SP 4 has an
outstanding issue or two that might affect you. SP 3a might be your best
bet in a production environment.|||Hi Michael,
Thanks for your post.
Yes my fault, I should have mentioned SP4 has an known issue now with AWE
for more than 2 GB memory, you may have noticed that we have such note in
the download page
Important Note: Microsoft has found an issue with the final build of SP4
that impacts customers who run SQL Server with Address Windowing Extensions
(AWE) support enabled. This issue only impacts computers with more than two
gigabytes (2 GB) of memory where AWE has been explicitly enabled. If you
have this configuration, you should not install SP4. Microsoft is currently
working on the problem and will issue an update soon.
Our SQL Development is working on a public hotfix package for this fix.
This will take longer to have the right tests done and get this properly
released with the hotfix installer. As stated in KB below this problem is
specific to customers that use AWE. The problem limits the amount of
physical memory that SQL Server can use for data cache. The bug only allows
SQL Server to use at maximum 50% of the physical memory in the server. You
can observe this behavior by looking at various perfmon counters including
SQL Server:Memory Manager/Total Server Memory (KB). On an SP3 SQL Server
this value can be up to the amount of physical memory on the computer, but
with SP4 it will never be more than 50% of physical RAM.
Refer the Knowledge Base article for more detailed information
BUG: Not all memory is available when AWE is enabled on a computer that is
running a 32-bit version of SQL Server 2000 SP4
http://support.microsoft.com/kb/899761
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I've read that the hotfix will be available in approximately 7 days (I read
this on 5/16). Is there any update if it will be released soon?
Thanks
"Michael Cheng [MSFT]" wrote:

> Hi Michael,
> Thanks for your post.
> Yes my fault, I should have mentioned SP4 has an known issue now with AWE
> for more than 2 GB memory, you may have noticed that we have such note in
> the download page
> Important Note: Microsoft has found an issue with the final build of SP4
> that impacts customers who run SQL Server with Address Windowing Extension
s
> (AWE) support enabled. This issue only impacts computers with more than tw
o
> gigabytes (2 GB) of memory where AWE has been explicitly enabled. If you
> have this configuration, you should not install SP4. Microsoft is currentl
y
> working on the problem and will issue an update soon.
> Our SQL Development is working on a public hotfix package for this fix.
> This will take longer to have the right tests done and get this properly
> released with the hotfix installer. As stated in KB below this problem is
> specific to customers that use AWE. The problem limits the amount of
> physical memory that SQL Server can use for data cache. The bug only allow
s
> SQL Server to use at maximum 50% of the physical memory in the server. You
> can observe this behavior by looking at various perfmon counters including
> SQL Server:Memory Manager/Total Server Memory (KB). On an SP3 SQL Server
> this value can be up to the amount of physical memory on the computer, but
> with SP4 it will never be more than 50% of physical RAM.
> Refer the Knowledge Base article for more detailed information
> BUG: Not all memory is available when AWE is enabled on a computer that is
> running a 32-bit version of SQL Server 2000 SP4
> http://support.microsoft.com/kb/899761
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi,
Thanks for your question.
Unfortunately, I am afraid here is no hotfix available now. If it is ready,
we will find the hotfix in RESOLUTION section in KB article 899761.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.