| Fred Foozle 2005-11-11, 1:23 pm |
| 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'MyStatus
Table'))
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'MyTestPr
oc'))
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.
|