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

Posted on 2007-03-28
Medium Priority
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.
Question by:schwennjr
  • 5
  • 4
LVL 30

Expert Comment

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?

Author Comment

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 :)
LVL 30

Expert Comment

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.

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


Author Comment

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!!!!!!!

Author Comment

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)
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.
LVL 30

Accepted Solution

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

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.

Author Comment

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

Author Comment

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 :)
LVL 30

Expert Comment

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.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

601 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