Re: SQL 2000 Stored Procedure Problem

Richard,

I am certainly confused. There should be no rights issues that prevent you
from using a #temp table.

Could you check to make sure that you do not have a copy of your stored
procedure on master? (I know that it is unlikely, but the behavior sounds
like you are not executing the code that you think you should be executing.)

RLF

"Richard" <rich@xxxxxxxxxxxxxxxx> wrote in message
news:uCJSX0lnHHA.3512@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Erland,

sp_configure 'user options' reports the same on both :
minimum=0, Maximum=32767, config_value=0, run_value=0

I added SET NOCOUNT ON as the first line in spTestTempTable - same
results. I had tried that yesterday while searching the groups for ideas
for 5 hours.

Any other suggestions are welcome.


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns993B19CCA96CYazorman@xxxxxxxxxxxx
Richard (rich@xxxxxxxxxxxxxxxx) writes:
--- SProc 2
CREATE PROCEDURE spTestTempTable AS
CREATE TABLE #MyTempTable
(
ReturnValue varchar(100)
)
INSERT #MyTempTable
SELECT 'Test Temp Table'

SELECT ReturnValue FROM #MyTempTable
----- This works everywhere EXCEPT on the New Server from the VB/ADO
app.

Any ideas? My last resort is to undo all the SProcs back to the slow
inefficient Pass Through method. Yuk!

Do you loop over .NextRecordset? Or do you set SET NOCOUNT ON
when you connect? If you do neither, I think we are on to something.
Try adding SET NOCOUNT ON in the beginning of the procedure.

As for why it works on one server, but not another, what does

sp_configure 'user options'

report on the two machines?


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx




.

Relevant Pages

  • Re: SYS.SQL_DEPENDENCIES, Refresh Dependencies
    ... because you cannot have temp tables in user-defined functions. ... If you are using dependencies to see what you need to migrate from test ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Temp Table Faster?
    ... > Or better to create a temp table, select the records into it, and then ... A temp table could be slower because of recompilations. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: adding identity column dynamically
    ... > In my stored procedure I'm doing a SELECT on ... To get an IDENTITY column you could bounce over a temp table with: ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Most efficient way to run update query
    ... rdraider writes: ... a temp table to hold all the item numbers would be better. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: SQL 2000 Stored Procedure Problem
    ... ReturnValue varchar ... Try adding SET NOCOUNT ON in the beginning of the procedure. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)