We are fairly new to Teamsite and Datadeploy and having an issue.
We are trying to get a unique ID from the database by calling a stored procedure which increments a column in the table and then selects the ID and returns it as New ID. We are running a perl script which calls a datadeploy deployment. I will attach the deployment xml. The output file is read into a hidden field in the form.
The stored procedure is listed below.
The strange problem is that the value gets incremented but doesnt get updated in the database. So there looks like a Commit issue of some kind. The user id has full permissions to insert/update.
Please help...if you can....thanks
ALTER PROCEDURE dbo.MakeID (
@TableName varchar(100))
AS
BEGIN
@trancount SET NOCOUNT ON
BEGIN TRAN T1
IF
@TableName IS NULL
SET
@TableName = Convert(varchar(50), getdate())
IF EXISTS (SELECT 1 FROM dbo.IDGenerator WHERE TableName =
@TableName) BEGIN
UPDATE dbo.IDGenerator
SET LastUsedID = LastUsedID + 1
WHERE TableName =
@TableName END
ELSE
BEGIN
INSERT INTO dbo.IDGenerator (TableName, LastUsedID)
VALUES (
@TableName, 1)
END
COMMIT TRAN T1
SET NOCOUNT OFF
SELECT LastUsedID AS NewID
FROM dbo.IDGenerator
WHERE TableName =
@TableName END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
