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

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.
Who is Participating?
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.
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?
schwennjrAuthor Commented:
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 :)
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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.

schwennjrAuthor Commented:
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!!!!!!!
schwennjrAuthor Commented:
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.
schwennjrAuthor Commented:
Interesting. I will try that out next week and let you know if that worked.
schwennjrAuthor Commented:
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 :)
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.