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.
Error while running store procedure on SQL Server 2012
Re: Error while running store procedure on SQL Server 2012
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
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