|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Linked Server Yields inconsistent resultsServers. 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. Show quote "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/details.aspx?FamilyID=8e2dfc8d-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-min***@online.microsoft.com> wrote in message How about "next to latest SQL Server 2000 service pack"? SP 4 has an 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 > > http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446- > 99a9-b7f0213f8bc5&DisplayLang=en 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 Show quote "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 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. > > 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.  
Other interesting topics
|
|||||||||||||||||||||||
