Temporary tables I'm having a lot of trouble debugging the following stored procedures (mostly temporary table operations):
CREATE PROCEDURE [C_readtop] @eachrow int=10 as
DECLARE @tmpcat varchar (16)
CREATE TABLE #tmp_result (arid int,cat2 varchar), title varchar (MB), Upday datetime)
DECLARE rt_cursor cursor
For the select Cat2 from category where cat1= ' computer manuals ' and catl=2
Open Rt_cursor
Fetch from Rt_cursor into @tmpcat
While @ @fetch_status =0
Begin
SET ROWCOUNT @eachrow
Insert into #tmp_result (arid,cat2,title,upday) Select top arid,cat2,title,upday from article as a
Left join category as B on A.sortid=b.catid where b.cat1= ' computer manuals ' and b.cat2= @tmpcat order BY upday Desc
Fetch from Rt_cursor into @tmpcat
End
SELECT * FROM #tmp_result
drop table #tmp_result
Close Rt_cursor
Deallocate rt_cursor
The purpose of this stored procedure is to remove the latest 10 records for each category.
The error message that appears is (occurs when the recordset is returned by the operation):
ADODB. Recordset error ' 800a0e78 '
The operation requested by the application isn't allowed if the object is closed.
This stored procedure works correctly in QA and gets the correct results, and the correct results can be obtained by using ODBC to connect to the database. So first suspected OLE DB side
Connection failed to return the recordset. The following debugging is done:
(a) with the debug tag, use set Rs=rs.nextrecordset to test if the recordset is hit returned before calling the recordset ...
(b) Since the deposit was originally part of another process, suspecting that some statements in the stored procedure could not be used concurrently, the process was separated into a separate
Stored procedures, the error remains.
(iii) There is a problem with the ASP that calls the process, so redo an ASP program that simply invokes the stored procedure, and the error remains.
(iv) Change the connection mode to an ODBC connection (build DSN, set login ID for SQL Server, set permissions), and the error disappears. Re-use OLE DB connections,
Mistakes remain.
(v) suspicion of inserting data into temporary tables, canceling the insertion of data into temporary tables, and returning an empty recordset.
(vi) The temporary table was built on the temporary database, Bigeagle, by the prompt, and the error remains
(vii) Removing the drop table in the stored procedure, running the stored procedure in QA, observing the generation of the temporary table, discovering that the temporary table is correctly generated and having the correct
Data insertion, baffled, where is the data output?
(eight) The Bigeagle prompts the CREATE table to return the recordset, and then again use multiple sets Rs=rs.nextrecordset before outputting the recordset (most
Put 4 more), the wrong hint is still.
(ix) Doubt that temporary table operations are problematic, change the temporary table to a fixed table, return an empty recordset without inserting data, and still prompt for errors when inserting records. In a recordset
Before the output executes one or more set rs=rs.nextrecordset, and finally there is no prompt error (detect rs.eof to false), then suddenly big
Awareness-not only the CREATE table returns the Recordset, but also the INSERT INTO statement returns the recordset, but the recordset is a special set of records
(No fields, no action on the recordset-even the detection rs.eof is not allowed), I call it a special recordset to facilitate the following
Use.
(10) Know the crux of the problem, immediately resolved, in the stored procedure do not want to return to the recordset before the set NOCOUNT on, to return to the recordset, first
Executes the SET NOCOUNT off.
Which is changed to:
CREATE PROCEDURE [C_readtop] @eachrow int=10 as
DECLARE @tmpcat varchar (16)
SET NOCOUNT ON
CREATE TABLE #tmp_result (arid int,cat2 varchar), title varchar (MB), Upday datetime)
DECLARE rt_cursor cursor
For the select Cat2 from category where cat1= ' computer manuals ' and catl=2
Open Rt_cursor
Fetch from Rt_cursor into @tmpcat
While @ @fetch_status =0
Begin
SET ROWCOUNT @eachrow
Insert into #tmp_result (arid,cat2,title,upday) Select top arid,cat2,title,upday from article as a
Left join category as B on A.sortid=b.catid where b.cat1= ' computer manuals ' and b.cat2= @tmpcat order BY upday Desc
Fetch from Rt_cursor into @tmpcat
End
SET NOCOUNT OFF
SELECT * FROM #tmp_result
drop table #tmp_result
Close Rt_cursor
Deallocate rt_cursor
Solve the problem.
During the process of debugging the stored procedure, there is a big difference between OLE DB and ODBC, and when ASP takes the recordset to ODBC, the ODBC filters the special
The extraordinary recordset (a recordset that occupies only a location but is not able to do anything), which is generated by CREATE TABLE or insert into, and the ASP takes a record from OLE DB
OLE DB does not filter the special recordset when set.
Also, recognizing that when you return a recordset using a stored procedure, you should use SET NOCOUNT on to prevent the return of the stored procedure where you do not want to return the record
Recordset, otherwise it may be around a lot of detours.
Finally understand why so many detours: I did not expect OLE DB to return so many special recordsets (or by a loop, the loop executes
Times 5, 6 times, no wonder that in the record set before the implementation of the set Rs=rs.nextrecordset, but the end due to insufficient data and failed to find the crux of the error.
Thank you very much for the bigeagle given in the process of debugging.
