Re: SQL 2000 Stored Procedure Problem

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: Returning SCOPE_IDENTITY from SQLDataSource and DetailsView
    ... Sql Server Express) and then I saw this post by you, ... Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ... @intPages as int, ... @returnValue as int OUTPUT ...
    (microsoft.public.dotnet.framework.aspnet)
  • 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: SQL 2005 Management Studio Bugs
    ... default state is row count echo, ... set nocount on stops those -- e.g., during a script that does a lot ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
Quantcast