Re: SQL 2000 Stored Procedure Problem

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: Item Cannot be Found in Collection
    ... The "SET NOCOUNT ON" suggestion is not relevant for Access databases. ... setting that can only be performed in SQL Server. ... > I tried Set NOCOUNT = ON and I got a syntax error. ...
    (microsoft.public.scripting.vbscript)
  • Re: SQL 2000 Stored Procedure Problem
    ... I added SET NOCOUNT ON as the first line in spTestTempTable - same ... SELECT 'Test Temp Table' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Bcp and temp tables
    ... Here is the proc: ... bcp "SET FMTONLY OFF EXEC pubs.dbo.Kristoffer" queryout ... without SET NOCOUNT ON with the same results. ... > Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Newbie question; Why is my procedure slow when a parameter is used?
    ... since your WHERE clause refers to columns in the unpreserved ... Columnist, SQL Server Professional ... Alter Procedure prInvDet ... set nocount on /* set nocount on */ ...
    (microsoft.public.sqlserver.programming)
  • transaction error??
    ... Theere are two sql server in different city,I connect then with VPN, then I ... BEGIN DISTRIBUTED TRANSACTION ... SET NOCOUNT OFF ...
    (microsoft.public.sqlserver.programming)