Page 1 of 1

I get error while execute stored procedure in SQLServer 2005

Posted: Sun 29 May 2011 14:40
by galit
while running stored procedure I ger the following error:

"Query must return exactly one result set - use Execute"

this error occurs in SQL Server 2005 only

when I used Execute for my sp I still get the same error

Please advice

Posted: Mon 30 May 2011 09:22
by AndreyZ
Hello,

Please specify the code of your stored procedure.

Posted: Tue 31 May 2011 06:47
by galit
Hello

see bellow the code of our stored porcedure
as I said previously there is no problem to execute this sp using dbExpress or SDAC with SQL Server 2000/2008


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetNextDocNo]
@DOCCATEGORY CHAR(10),
@DOCTYPE CHAR(20),
@COMPANY SMALLINT,
@DIVISION CHAR(5),
@AUTOINC CHAR(1),
@DELTA INT
OUTPUT

AS

BEGIN TRANSACTION

DECLARE @RESOURCENAME nvarchar(255)

SET @RESOURCENAME = 'GNDN_' + @DOCCATEGORY + '_' + @DOCTYPE + '_' + CAST(@COMPANY AS varchar(2)) + '_' + @DIVISION

EXEC sp_getapplock @Resource = @RESOURCENAME,
@LockMode = 'Exclusive';

IF ((SELECT TOP 1 COUNT(COUNTER) FROM COUNTERSTBL
WHERE DOC_CATEGORY = @DOCCATEGORY AND DOC_TYPE = @DOCTYPE AND COMPANYNO = @COMPANY AND ISNULL(DIVISION, '') = ISNULL(@DIVISION, '')) = 0)
BEGIN

DECLARE @NEXTNUMBER int
DECLARE @INCREMENT int
SET @NEXTNUMBER = 1;
SET @INCREMENT = 1;
IF (@AUTOINC = 'Y')
BEGIN
SET @NEXTNUMBER = @DELTA;
SET @INCREMENT = @DELTA;
END

INSERT INTO COUNTERSTBL
(DOC_CATEGORY, DOC_TYPE, COMPANYNO, DIVISION, INCREMENT, COUNTER)
VALUES (@DOCCATEGORY, @DOCTYPE, @COMPANY, @DIVISION, @INCREMENT, @NEXTNUMBER)

END
ELSE
BEGIN

IF (@AUTOINC = 'Y')
BEGIN
UPDATE COUNTERSTBL
SET COUNTER = COUNTER + INCREMENT
WHERE DOC_CATEGORY = @DOCCATEGORY AND DOC_TYPE = @DOCTYPE AND COMPANYNO = @COMPANY AND ISNULL(DIVISION, '') = ISNULL(@DIVISION, '')
END
ELSE
BEGIN
UPDATE COUNTERSTBL
SET COUNTER = COUNTER + @DELTA
WHERE DOC_CATEGORY = @DOCCATEGORY AND DOC_TYPE = @DOCTYPE AND COMPANYNO = @COMPANY AND ISNULL(DIVISION, '') = ISNULL(@DIVISION, '')
END;

END;

SELECT COUNTER FROM COUNTERSTBL
WHERE DOC_CATEGORY = @DOCCATEGORY AND DOC_TYPE = @DOCTYPE AND COMPANYNO = @COMPANY AND ISNULL(DIVISION, '') = ISNULL(@DIVISION, '')

EXEC sp_releaseapplock @Resource = @RESOURCENAME

COMMIT
[/code]

Posted: Tue 31 May 2011 08:34
by AndreyZ
I cannot reproduce the problem. Please try executing this stored procedure in Microsoft SQL Server Management Studio using SQL Server 2005 and 2008, and post here recordsets that are returned in both cases.

Posted: Sun 05 Jun 2011 14:58
by galit
Hello,

when I open dataset I see in profiler the following text
declare @P1 int
set @P1=1
exec sp_prepare @P1 output, N'@P1 varchar(500) OUTPUT,@P2 varchar(500) OUTPUT,@P3 int OUTPUT,@P4 varchar(500) OUTPUT,@P5 varchar(500) OUTPUT,@P6 int OUTPUT', N'EXECUTE dbo.GetNextDocNo
@P1
, @P2
, @P3
, @P4
, @P5
, @P6', 1
select @P1
go
SET FMTONLY ON exec sp_execute 1,' ',' ',0,' ',' ',0 SET FMTONLY OFF
go
exec sp_execute 1, 'ULLI', 'D', 1, '', 'Y', 1

when I run this text in Microsoft SQL Server Management Studie I get message
"Msg 8179, Level 16, State 2, Procedure sp_prepare, Line 3
Could not find prepared statement with handle 1."

please explain how to see results in Microsoft SQL Server Management Studie and how to solve this issue

Posted: Tue 07 Jun 2011 07:27
by AndreyZ
You should perform the following steps:
- open Microsoft SQL Server Management Studio;
- find your stored procedure in the Programmability branch;
- right click on your stored procedure, and choose Execute Stored Procedure;
- enter parameter values and click OK.
In the bottom you will see the Results tabsheet. Repeat these steps for both SQL Server 2005 and 2008. Please post the results here, or (better) send screenshots with them to andreyz*devart*com.