January 26, 2010 at 8:24 am
I am using several components of SQL Server 2005 to produce a SSRS report. The data sources are gathered using an SSIS package that queries an Oracle database (this part works) and copies the data into a SQL Server 2005 (32-bit, SP3) table (this part works too). Then, a stored procedure is used to join that table with a several tables on another SQL Server 2005 (64-bit, SP3) through a linked server (this is where the problem is). I've searched for the specific error, and found that even after SP2, this was a known issue, and there was a hotfix supplied by Microsoft for it. I then installed SP3 on the 64-bit machine, but the problem persists. Wouldn't the hotfix not be included in SP3? If not, I guess I need to grab the hotfix and install it.
The specific error I'm getting in Management Studio is:
Msg 7399, Level 16, State 1, Procedure PSRC_LV_Report, Line 10
The OLE DB provider "SQLNCLI" for linked server "CTSQLPROD02\MANUFACTURING" reported an error. The provider ran out of memory.
Msg 7330, Level 16, State 2, Procedure PSRC_LV_Report, Line 10
Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server "CTSQLPROD02\MANUFACTURING".
The error in BIDS is pretty similar:
An error occurred during local report processing. An error has occurred during report processing. Cannot read the next data row for the data set LV_Query. The OLE DB provider "SQLNCLI" for linked server "CTSQLPROD02\MANUFACTURING" reported an error. The provider ran out of memory.
Also, I don't know if there is a specific setting that can be set to adjust the memory usage, or if that would even help in this situation.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 26, 2010 at 8:39 am
Are you trying to join a table in oracle to tables in sql servers with in the stored procedure?
SQL Server divides total memory to two components 1) Buffer Pool
2) MemtoLeave Area.
By default sql server allocates 384 MB of memory to MemtoLeave area which is used by external components such as Extended Stored procs, Linked Servers, COM+ objects etc.
To me in this case when you are trying to fetch required data by joining a table from oracle with tables residing in sql server, you are having this memory issue.
As an alternative to over come this issue, you can fetch the required data from oracle to a temp table and from temp table you can join the tables that are in sql server, this will resolve the issue.
January 26, 2010 at 8:51 am
You may have to apply the hotfix again. Hotfix may or may not be part of service pack and it depends on whether such a fix is ready to be released and also other factors such how many customers are impacted and such. At least thats my understanding.
Be cautious of one thing though. A hotfix may have requirements, so if you upgraded to SP3 and SP4, the specific hotfix may not meet those requirements and may make your system unstable. In your case the hotfix is for SP2. Check to make sure it applies to SP3 or SP4.
TEST TEST TEST in a test environment first.
January 26, 2010 at 9:23 am
No, the joins are happening between two SQL Servers. The data in the first table is populated using the SSIS package that pulls that data from Oracle, but then the stored procedure is pulling the data from the local SQL Server (32-bit version) and joining to the 2nd SQL Server (64-bit version).
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 26, 2010 at 11:18 am
Can you initially get the required data from the linked server through open query if possible and then do a join on the server?
January 26, 2010 at 11:42 am
The join is not occuring on the oracle server at all. It is occurring with a linked server, and if I leave out the table from Server1, I get results from Server2. For example, Server1 has 3 tables that I need data from, Table1, Table2 and Table3. Running the query in management studio on Server2, I can get all of the data from the 3 tables just fine until I join the one table on Server2, which then causes the error.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 26, 2010 at 11:53 am
SQL_Hacker (1/26/2010)
The join is not occuring on the oracle server at all. It is occurring with a linked server, and if I leave out the table from Server1, I get results from Server2. For example, Server1 has 3 tables that I need data from, Table1, Table2 and Table3. Running the query in management studio on Server2, I can get all of the data from the 3 tables just fine until I join the one table on Server2, which then causes the error.
You can get the required data from Table1,Table2 and Table3 into a temp table on Server2 and then do a join on temp table and required table on server2.
January 26, 2010 at 1:14 pm
I found the problem--and, as usual, the problem had nothing to do with the error message generated in the applications. The problem was an incorrect join on a third server (that I forgot about). Once I fixed that, it runs just fine.
Thanks for your help.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply