Error while running store procedure on SQL Server 2012

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
galit
Posts: 18
Joined: Mon 12 Feb 2007 11:07

Error while running store procedure on SQL Server 2012

Post by galit » Sun 02 Feb 2014 12:09

Hello,

We get an error "Exception message: 'Exception of class ''EMSError'' has occured.
Exception Message: The metadata could not be determined because statement ''exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout'' in procedure ''sp_getapplock'' invokes an extended stored procedure." while calling to store procedure.

This error occurs on SQL Server 2012 only

we use SDAC 6.9.17 for Delphi 7.

see bellow the code of our stored procedure
There is no problem to execute this sp using dbExpress driver

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

I really appreciate any help you can provide.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Error while running store procedure on SQL Server 2012

Post by AlexP » Tue 04 Feb 2014 12:35

Hello,

We cannot reproduce the problem on the 2012 server. This problem can be related to the one described at microsoft http://blogs.msdn.com/b/sqlagent/archiv ... error.aspx

Post Reply