Re: Problem opening stored procedure that contains cursor

SET NOCOUNT ON

did not function !

In order to reproduce the error I think that is enough to create a stored
procedure that use a SQL CURSOR to create a temporary table.

a part of code is below:

DECLARE aux_cursor CURSOR FOR <sql statement>


CREATE TABLE #CrossTabBam(NumOrder CHAR(35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL, ItemID INT NOT NULL, CrossBamText
VARCHAR(4000))
ALTER TABLE #CrossTabBam ADD CONSTRAINT PK_CrossTabBam PRIMARY KEY
CLUSTERED (NumOrder, ItemID)

FETCH NEXT FROM aux_cursor
INTO @ItemID, @NumOrder, @Bolla, @DataBolla, @Qty

SET @MemNumOrder = @NumOrder
SET @MemItemID = @ItemID

WHILE @@FETCH_STATUS = 0
BEGIN

-- Concatenate and display the current values in the variables.
SET @Text = @Text + RTRIM(CONVERT(Char(10), @DataBolla, 103)) + '-' +
RTRIM(@Bolla) + ' (' + RTRIM(CAST(@Qty AS nVarChar(10))) + ')'

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM aux_cursor
INTO @ItemID, @NumOrder, @Bolla, @DataBolla, @Qty

-- Se l'item successivo è diverso dal precedente allora aggiungo la riga
alla tabella temporanea
IF @MemItemID <> @ItemID
BEGIN
INSERT INTO #CrossTabBam VALUES (@MemNumOrder, @MemItemID, @Text)
SET @MemItemID = @ItemID
SET @MemNumOrder = @NumOrder
SET @Text = ''
END
ELSE SET @Text = @Text + ', '

END

CLOSE aux_cursor
DEALLOCATE aux_cursor
"Bob Barrows [MVP]" wrote:

Pier-Paolo wrote:
Inside the stored procedure I use CURSOR to creeate a temporary table.

After I use the temporary table that I have created in JOIN with
other table in order to have a resultset as a result

I have just inserted on the top of the sored procedure the istruction

SET NOCOUNT ON


And did that help? if not, provide a repro script (www.aspfaq.com/5006)
so we can attempt to reproduce the problem.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.



.

Relevant Pages

  • Re: Dynamic SQL another example
    ... > You cannot have a column name in a variable and expect SQL Server to ... resolve that value as a column name. ... > convertyed to datetime. ... SET NOCOUNT OFF is not needed. ...
    (microsoft.public.sqlserver.programming)
  • Verständnisproblem bei Stored Procedure
    ... Voraussetzungen Server: SQL Server 2000 auf Windows Server 2003 ... Eine Stored Procedure soll den höchsten Wert eines Datenfeldes ... SET NOCOUNT OFF ...
    (microsoft.public.de.sqlserver)
  • Re: Whats the deal with PAGEIOLATCH_SH? - PAGEIOLATCHIssue20040426.zip (0/1)
    ... After a great deal of help with Microsoft/PSS we finally isolated the issue! ... a reproducible table - the key driving factor to reproduce the problem is table width. ... Thanks to everyone for their help - I learned a great deal about SQL queries :-) ... >from the temporary table and loop. ...
    (microsoft.public.sqlserver.programming)
  • Re: Dynamic SQL another example
    ... set nocount off ... > Tibor Karaszi, SQL Server MVP ... >> Syntax error converting datetime from character string. ...
    (microsoft.public.sqlserver.server)
  • Re: How to make a SQL run longer?
    ... To reproduce one of our cusotmer's probem, I need to make the SQL to ... have large amount of data in the database to simulate the dealy. ... Our is an reporting application that can connect to either SQL server ...
    (comp.databases.ms-sqlserver)