October 4, 2011 at 3:47 am
When I execute the following piece of code in Sql Server 2008 to create a table and a stored procedure, I get the error message "Msg 266, Level 16, State 2, Procedure usp_abc, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1." when the procedure executes. I've spent ages trying to figure this out, any suggestions would be greatly appreciated. Oh, I've replaced the real table name/column/syntax in the cursor for this example.
Thanks in advance..........
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U'))
DROP TABLE [dbo].[Table_1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_abc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_abc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[abc_ID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[usp_abc]
@abc_ID int =null
as
begin
set nocount on;
declare @LocalTransaction bit
if @@trancount = 0
begin
set @LocalTransaction = 1
begin transaction LocalTransaction
end
begin try
declare @Distinct_ID varchar(8000)
declare @Get_ID cursor
set @Get_ID = cursor for select distinct [abc_ID] from [dbo].[Table_1]
open @Get_ID
fetch next
from @Get_ID into @Distinct_ID
while @@fetch_status = 0
begin
declare @a varchar(1000)
set @a = 'select *
from [dbo].[Table_1]
where [abc_ID] = ' + @Distinct_ID + ' ' +
'and [abc_Version] in
(
select max([abc_Version]) from [dbo].[Table_1] where [abc_ID] = ' + @Distinct_ID + '
)'
print @a
fetch next
from @Get_ID into @Distinct_ID
end
close @Get_ID
deallocate @Get_ID
end try
begin catch
declare @ErrorMessagenvarchar(4000)
declare @ErrorSeverityint
declare @ErrorStateint
select @ErrorMessage= error_message(),
@ErrorSeverity= error_severity(),
@ErrorState= error_state()
if @LocalTransaction = 1 and xact_state() = 1
rollback transaction
if @LocalTransaction = 1
begin
insert into [dbo].[ErrorLog]
(
[DatabaseName],
[ExecutingUser],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage],
[ExecutionTimestamp]
)
select
db_name() [DatabaseName],
system_user [ExecutingUser],
error_number() [ErrorNumber],
error_severity() [ErrorSeverity],
error_state() [ErrorState],
error_procedure() [ErrorProcedure],
error_line() [ErrorLine],
error_message() [ErrorMessage],
getdate() [ExecutionTimestamp]
end
raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
end catch
end
GO
exec [dbo].[usp_abc]
GO
www.sqlAssociates.co.uk
October 4, 2011 at 4:15 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply