Temp Table not working in Stored Procedure in ODBC environment!

[biglarj]

Hi,
I inherited a stored procedure which works in an ADODB environment. When run
in an ODBC environment, it fails with a SQL_NO_DATA error at SQL_Execut(). I get an
"Invalid object name '#T' " error in the Query Analyser along with the data. It seems
that ADODB ignores the error and ODBC errors out? Hacking the code line by line
until the "Invalid ..." error goes away yields a happy SQL_Execute (wrong data
because of hack though). So - can anyone see the problem in this code, or can
anyone propose a rewrite so as to eliminate the temp table?

CREATE Procedure Broken
(
@nTop INT,
@OldStatus INT,
@OldOwner VARCHAR(100),
@TimeIndicator DECIMAL(15,7),
@NewStatus INT,
@NewOwner VARCHAR(100),
@nMode INT
)
As
--what does this do?
SELECT A.ID INTO #T FROM TOBJ A WHERE 1 = 2
--identify records status and owner, and record their ids in a temp table
INSERT INTO #T
SELECT TOP @nTop ID
FROM TDATA WHERE STATUS = @OldStatus
AND OWNER = @OldOwner
ORDER by TIMEORDER
--update those records status and owner fields (can't reselect now!)
UPDATE TDATA SET STATUS = @NewStatus, OWNER = @NewOwner FROM #T
WHERE TDATA.ID = #T.ID
--now, using the same set of ids, select and return OBs from the OBJ table
SELECT OB FROM TOBJ, #T
WHERE TOBJ.ID = #T.ID

DROP TABLE #T

Any help is greatly appreciated!!!

 

[tlbroadbent]

SELECT A.ID INTO #T FROM TOBJ A WHERE 1 = 2

The above statement creates the table without inserting any data.

Need some more info to answer other questions.

1- Which version of SQL Server is installed?
2- Does the error in Query Analyzer occur when creating or executing the stored procedure?

Terry

http://members.home.net/tlbroadbent/index.htm

 

[biglarj]

The version of SQL Server is 7
The error in Query Analyzer (7.00.623) occurs when I run "Display Estimated Execution Plan"

Thanks much!

P.S. I'm trying to rewrite the Stored proc to eliminate the table problem, but I think I'm going
backwards SQL_Execute() doesn't return now ... :-(

 

[tlbroadbent]

The Estimated Query plan cannot be created because #T doesn't exist until the stored procedure is executed. There is no table problem.

Will the SP execute in Query Analyzer? Terry

http://members.home.net/tlbroadbent/index.htm

 

[biglarj]

Yes, the SP executes in Query Analyzer. I realize now what you're saying about
the table problem, so I'm wondering if maybe I'm missing a
service pack?

Thanks Still!

 

[tlbroadbent]

Monitor the server with SQL Profiler to see what the application is actually sending to SQL Server. That might give you an idea where to look. Terry

http://members.home.net/tlbroadbent/index.htm

 

[biglarj]

(much water has gone under the bridge...)
I thought I would relate what I've learned through this exercise.
After taking the good suggestion of watching closely what was happening with
the querry analyzer, I discovered that an input parameter was not bound properly,
but alas, this was not my only problem. The stored procedure would execute to the
second SELECT and then appear to stop! After much hair pulling I realized that I
needed to execute SQLMoreResults() to get succeeding record sets. They are
apparantly held up on the server until the client requests them. However, in the ADODB
environment this is not the case - all record sets appear to be delivered to the client
immediately. There must be a way to do this in ODBC as well - no? Does anyone know
how to get all the record sets delivered to the client immediately? My current ODBC
solution is slower than the ADODB solution, and I was hoping for a gain! Thanks
for your patience, and for your great pointers!

 

[biglarj]

<bump> anybody?
Thanks!

 

[tlbroadbent]

Add the command SET NOCOUNT ON at the beginning of the SP to eliminate the multiple result set problem. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot