I have a SQL Server Stored procedure that I am executing via ODBC.
However, I am seeing behavior that I cannot explain and do not
understand.

Given the following T-SQL code that I have applied to my database:

--
-- BEGIN SQL CODE
--

--
-- Create a non-temp table to access at any time. Drop the table
first,
-- just in case it already exists.
--
if exists (select *
from dbo.sysobjects
where ((id = object_id(N'MyStatusTable'))
and (OBJECTPROPERTY(id, N'IsUserTable') = 1))
)
begin
drop table MyStatusTable
end
go

create table MyStatusTable
(
Status varchar(256)
)
go


--
-- Insert a value into the table. This value
-- will be modified when the MyTestProc stored procedure
-- is executed. (see below)
--
insert into MyStatusTable
(Status)
values
('EXECUTING...')
go


--
-- If the stored procedure we are about to create already
-- exists, then drop it
--
if exists (select *
from dbo.sysobjects
where ((id = object_id(N'MyTestProc'))
and (OBJECTPROPERTY(id, N'IsProcedure') = 1))
)
begin
drop procedure MyTestProc
end
go

create procedure MyTestProc
as
begin
set nocount on

declare @Ctr integer -- counter
declare @MyCursorInteger integer -- value read
from cursor


--
-- Create a result set
--
select 0, 'Begin MyTestProc'


--
-- Create a temp table to hold integer values.
-- Load the table with 2500 integers.
--
if (exists( select 1
from tempdb.dbo.sysobjects
where (name like '%#MyIntegerTable%')
and (xtype = 'U')))
begin
drop table #MyIntegerTable
end

create table #MyIntegerTable
(
MyIntValue integer
)


set @Ctr = 0
while (@Ctr < 2500)
begin
set @Ctr = @Ctr + 1
insert into #MyIntegerTable
(MyIntValue)
values
(@Ctr)
end


/*
--
-- Start Code without cursor
--
set @Ctr = 0

while(1=1)
begin
set @Ctr = @Ctr + 1

select @MyCursorInteger = MyIntValue
from #MyIntegerTable
where MyIntValue = @Ctr

if (@@ROWCOUNT = 0)
begin
break
end
end
--
-- End Code Without Cursor
--
*/


--
-- Create and open a cursor to walk the table.
--
-- Note: Depending upon the memory available to your SQL Server
-- you may require more or fewer values. You can tinker
-- with fewer by replacing the "top xxx" within the select
clause
-- and adjusting the number of values used to populate
each
-- row in #MyIntegerTable above
--

declare MyCursor cursor local for
select top 2500 MyIntValue
from #MyIntegerTable

open MyCursor


--
-- Grab each row in turn, until there is nothing left
--
while (1=1)
begin

fetch next from MyCursor
into @MyCursorInteger

if (@@fetch_status <> 0)
begin
break
end

end


--
-- Update the status table
--
update MyStatusTable
set status = 'FINISHED'


--
-- Close and deallocate cursor
--
close MyCursor
deallocate MyCursor



--
-- Create a result set before exiting
--
select 1, 'End MyTestProc'


set nocount off
return 0

end
go


--
-- END SQL CODE
--



Before executing the MyTestProc stored procedure, if I issue the
command:

SELECT * FROM MyStatusTable

I will receive the result set

EXECUTING...


If I then do the following:

1. Within ODBCTest, obtain a full connection to the database
2. Within ODBCTest, issue the SQL command 'EXEC MyTestProc'
3. I receive back the following within ODBCTest:

SQLExecDirect:
In: hstmt = 0x00821A00, szSqlStr = "exec MyTestProc",
cbSqlStr = -3
Return: SQL_SUCCESS=0

This leads me to believe the stored procedure has executed
successfully.

4. Within Query Analyzer, I re-issue the command:

SELECT * FROM MyStatusTable

I still obtain the result set

EXECUTING...


5. Within ODBCTest, I choose Results | Get All Data.

6. I receive the following within ODBCTest:

"", ""
0, "Begin MyTestProc"
1 row fetched from 2 columns.

"", ""
1, "End MyTestProc"
1 row fetched from 2 columns.
--


7. Within Query Analyzer, I re-issue the command:

SELECT * FROM MyStatusTable

This time I get the following

FINISHED


Why is it that the ODBC indicates that the stored procedure has
finished executing successfully, when it really hasn't? Why does the
database update MyStatusTable only after I retrieve the result sets?
What is going on?


Some things to note:
1. If you eliminate the cursor, the problem will not occur (i.e., the
status updates to 'FINISHED') [There will still be result sets to be
retrieved.]
2. If you eliminate the result sets within the stored proc, the problem
will not occur
3. If you reduce the number of items associated with the cursor (e.g.
"top 10"), the problem will not occur
4. The SQL Server that I am executing on has dynamically configured SQL
Server Memory with a minimum of 255 MB and a maximum of 510MB. ( I
have also tried this with a fixed memory size.) If I reproduce this
test on a SQL Server with more memory (3967 MB), the problem DOES NOT
OCCUR
5. There does not appear to be a "time out" period where things will
complete. When executing MyTestProc within ODBCTest, it completes
instantaneously. I have waited in excess of 30 minutes to determine if
the status will change. It will not change until I retrieve the result
sets.



It appears that this is a memory consumption problem, but only when I
use result sets in conjunction with a cursor that is associated with a
large number of items. In other words, if I (a) increase the amount of
memory available on the SQL Server, (b) reduce the number of rows
associated with the cursor, (c) eliminate the usage of the cursor or
(d) eliminate the result set that is generated, then the problem "goes
away".

This proble will only occur under an ODBC connection. If this test
scenario is executed within Query Analyzer, then the result sets will
be returned immediately and MyStatusTable will be updated.

Any helpful suggestions would be greatly appreciated.