I am getting the following error The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. when I execute SQL that contains BEGIN TRANSACTION and COMMIT. The query executes without problems if I execute it directly on the database but if I use the same code in a module install package or run it in the SQL module in a DNN web I get the error. The query in the example below was generated automatically from SQL server management studio. Is there some way to work arround this problem?
Example of a query that results in the error:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Lists
DROP CONSTRAINT DF_Lists_ParentID
GO
ALTER TABLE dbo.Lists
DROP CONSTRAINT DF_Lists_Level
GO
ALTER TABLE dbo.Lists
DROP CONSTRAINT DF_Lists_SortOrder
GO
ALTER TABLE dbo.Lists
DROP CONSTRAINT DF_Lists_DefinitionID
GO
ALTER TABLE dbo.Lists
DROP CONSTRAINT DF_Lists_PortalID
GO
ALTER TABLE dbo.Lists
DROP CONSTRAINT DF_Lists_SystemList
GO
CREATE TABLE dbo.Tmp_Lists
(
EntryID int NOT NULL IDENTITY (1, 1),
ListName nvarchar(55) NOT NULL,
Value nvarchar(100) NOT NULL,
Text nvarchar(150) NOT NULL,
ParentID int NOT NULL,
[Level] int NOT NULL,
SortOrder int NOT NULL,
DefinitionID int NOT NULL,
Description nvarchar(500) NULL,
PortalID int NOT NULL,
SystemList bit NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Lists ADD CONSTRAINT
DF_Lists_ParentID DEFAULT ((0)) FOR ParentID
GO
ALTER TABLE dbo.Tmp_Lists ADD CONSTRAINT
DF_Lists_Level DEFAULT ((0)) FOR [Level]
GO
ALTER TABLE dbo.Tmp_Lists ADD CONSTRAINT
DF_Lists_SortOrder DEFAULT ((0)) FOR SortOrder
GO
ALTER TABLE dbo.Tmp_Lists ADD CONSTRAINT
DF_Lists_DefinitionID DEFAULT ((0)) FOR DefinitionID
GO
ALTER TABLE dbo.Tmp_Lists ADD CONSTRAINT
DF_Lists_PortalID DEFAULT ((-1)) FOR PortalID
GO
ALTER TABLE dbo.Tmp_Lists ADD CONSTRAINT
DF_Lists_SystemList DEFAULT ((0)) FOR SystemList
GO
SET IDENTITY_INSERT dbo.Tmp_Lists ON
GO
IF EXISTS(SELECT * FROM dbo.Lists)
EXEC('INSERT INTO dbo.Tmp_Lists (EntryID, ListName, Value, Text, ParentID, [Level], SortOrder, DefinitionID, Description, PortalID, SystemList)
SELECT EntryID, ListName, Value, Text, ParentID, [Level], SortOrder, DefinitionID, Description, PortalID, SystemList FROM dbo.Lists WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Lists OFF
GO
DROP TABLE dbo.Lists
GO
EXECUTE sp_rename N'dbo.Tmp_Lists', N'Lists', 'OBJECT'
GO
ALTER TABLE dbo.Lists ADD CONSTRAINT
PK_Lists PRIMARY KEY CLUSTERED
(
ListName,
Value,
Text,
ParentID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT