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.