Solved

SQL 2005 error "OLE DB provider 'SQLNCLI' reported an error. The provider ran out of memory"

Posted on 2007-03-28
9
1,137 Views
Last Modified: 2008-01-09
I am getting the following error when I run a stored procedure. After testing different sections of the stored proc, we have narrowed it down to the problem, but the problem makes no sense why it is breaking. I will attempt to clearly explain the db and table set up and give an similar sample of the code that isn't working.

There is a db that has many tables. However, this is a test environment set up like prod, but smaller in size because the data is constantly purge for new testing. In this database, there are two tables that have the same schema / structure. One is used as a "holding" container for the "middle tier" to process the new apps. When processed, they are moved the the "archive" table. Tthere is a third table in an "archive" (different) database that holds the records that are over 90 days old. This table has the same schema as the other two, it just resides on a different server (SQL 2000 on Win2000). My query selects a single column containing a  binary value which I use to convert into a PDF file. It checks the first table. If no rows are returned then, it checks the second. This is repeated for the third table. The problem exists with the second table (not the third on a different server).

If I do:  
select top 1 binary_column, guid from table2.

 I get the first column with the binary value and its guid (primary key for this table). HOWEVER, if I take the guid from the above select statement and use it in a where clause as follows:

select binary_column from table2 where guid = 'known guid value from above'

I get the following error:
SQL 2005 error "OLE DB provider 'SQLNCLI' reported an error. The provider ran out of memory"

Why am I getting this error when I use a WHERE clause? What is this error? I am returning the same value, so how is it out of memory?

We have SQL Server 2005 linked server cluster installed on Win2003 machines. Each machine has 2 GB or ram and 200 GB of hardspace (approx 150 gig remaining)

Please help so that we are not required to use Microsoft's (Lack of) Support Line.
0
Comment
Question by:schwennjr
  • 5
  • 4
9 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 18812349
What happens if you use

select TOP 1 binary_column from table2 where guid = 'known guid value from above'

Maybe the WHERE isn't limiting it to one row due to GUID casting problems? Anyway the above test might tell you.

Does this error occur in the application? If you run it in Management studio do you get an errror?
0
 

Author Comment

by:schwennjr
ID: 18813549
we tried the top 1 as well thinking the same thing (even though the guid is set up as the PRIMARY KEY). We noticed the problem through the app, but we pulled the individual select statements out of the query and ran them individually in the management studio and still received the same error.

Looking over my previous post there is additional information I forgot to add (sorry). The query exists in a different database which is still on SQL 2000 / win 2000. The query calls the tables (1, 2, and 3) using the Link alias (see example). HOWEVER, the query still throws an error in Management Studio when we are connected directly to the server containing table1 and table2 and run the individual select statement on that server (not calling across different databases).

EXAMPLE: (with link alias)
SELECT binary_column
FROM [link_alias].[database_name]..[table2]
WHERE guid = 'known guid value'

We think there is something regarding the linked servers and possibly some setting that is preventing the large binary file from being able to be "pulled" from another distributed/linked server in the cluster.

P.S. I am not real familar with database terminology. I'm just the developer who created the app which found this error / bug in sql server. So please correct me if my term usage is wrong :)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 18813603
When you connect to a SQL 2005 db and run that query in SSMS, do you get the error?. Is it the same error - SQLNCLI?

You might want to list all the different test cases and whether they worked.

0
 

Author Comment

by:schwennjr
ID: 18815549
The error that I am receiving is the same from the app (which calls the stored proc) and the individual select statements executed in SSMS. We receive this error only when we try to use the WHERE clause on TABLE2 (and only Table2). We used the TOP 1 (see examples above) to see if it would return a record without using the where clause, and it did without throwing an error, otherwise TOP 1 isn't part of the original query. We even pulled 20 rows (select top 20 * from table2) without any problems, but as soon as you use the where clause to get 1 row, it blows up. We have 2 DBAs and me trying every combination we can think of, and the only time we get the error is when the WHERE clause is used???

By the way I REALLY appreciate your help with this!!!!!!!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:schwennjr
ID: 18815986
OOPS... scratch the last comment. I need to revise where and what queries are throwing the error.

I mentioned that we were having problems in the db (lets call this database DB1 which resides on a separate box running SQL 2005 / Win 2003) containing table1 and table2. This is incorrect. If we run the query in SSMS on the db that has table1 and table2, we get no errors (see example)
ex:
SELECT binary_column       <-- WORKS. No link aliases or db names needed because we are running
FROM table2                             the query on the box
WHERE guid = 'known guid'

However we experience errors when we run the stored procedure or individual select statements in SSMS or from the app from the linked database (lets call it DB2 which resides on different a box running SQL 2000 / Win 2000). We get the errors only when we use the where clause. If we use TOP 1 up to 20, we get the expected rows. Anything over twenty we get SQLNCLI memory error. However, if we used the guid returned from one rows from  TOP 20 in a where clause (which should return 1 and only 1 row) we get the SQLNCLI error. (see examples). Also this problem exists for table1 and table2 when calling from DB2.

EX 1: (select statements in SSMS from DB2 calling tables in DB1. use link server alias to get to DB1)
SELECT TOP 20 binary_column, guid    <-- WORKS (no errors as long as WHERE clause is not used)
FROM [link_alias].[DB1_name]..[Table1]

EX 2: (sample select statement in query where query fails. It also fails by itself in SSMS when ran in DB2)
SELECT binary_column                               <-- FAILS giving SQLNCLI memory error. Tried changing
FROM [link_alias].[DB1_name}..[Table1]           the column being selected column used in where clause.
WHERE guid = 'known guid'                             Doesn't matter, they all fail with same error.

Again I apologize for the wrong information :). Hope this clears up some of the questions.

I know it is difficult to pinpoint the problem using my "generic" names, but being that I work at a big business, I don't want to jeopardize my position here by providing real names.
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 18827799
You could try it using a pass through query instead:

SELECT a.*
FROM OPENQUERY([link_alias],'SELECT binary_column, guid FROM DB1_name..Table1 WHERE guid = ''known guid''')


It passes the query through and runs it on the source server. (You might need to mess with the escaped quotes a bit to get it right.)

When you use a four part name, as in your original example, it sucks ALL the data over and then applies the WHERE clause.
0
 

Author Comment

by:schwennjr
ID: 18828418
Interesting. I will try that out next week and let you know if that worked.
0
 

Author Comment

by:schwennjr
ID: 18844029
The DBAs felt it would be better to separate the linked server data calls from the local stored procedure and create a new stored procedure on the linked server and call that one directly. They have the final say in things like that :). However, I tried the OPENQUERY in SSMS and it ran the select statement with the where clause without an error. This wasn't the case before.

THANK YOU FOR YOUR EFFORT!!! I know this will come up again :)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 18849273
Encapsulating the call in a remote stored procedure is basically doing the same thing - allowing the remote server to filter the data first. In fact it is a neater method to implement this. Sounds like your DBA's are switched on.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now