Solved

Microsoft SQL Server 2008

Posted on 2013-01-02
5
398 Views
Last Modified: 2013-01-23
Hi Experts,
Iam getting following error can you plz help.Thanks.

spcreatehisttbl_v2
Msg 50000, Level 16, State 1, Procedure spcreatehisttbl_v2, Line 93
spcreatehisttbl_v2:raised exception
Msg 266, Level 16, State 2, Procedure spcreatehisttbl_v2, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

(1 row(s) affected)
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.


GO
/****** Object:  StoredProcedure [dbo].[spcreatehisttbl_v2]    Script Date: 01/03/2013 11:48:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO







/*  This Stored procedure will accept 7 parameters as input. p1 Source databasename, p2 Source Schemaname,
p3 Source tablename, p4 Target databasename,p5 Target schemaname,p6 Target tablename,p7 what action to be done when target table already exists
- values to be passed
*/
ALTER PROCEDURE [dbo].[spcreatehisttbl_v2]
      @p1_SourceDbname nvarchar(128),
      @p2_SourceSchemaname nvarchar(128),
      @p3_SourceTablename nvarchar(128),
      @p4_TargetDbname nvarchar(128),
      @p5_TargetSchemaname nvarchar(128),
      @p6_TargetTablename nvarchar(128),
      @p7_Targetoption char(1)
as
Begin
set nocount on

raiserror('spcreatehisttbl_v2',10,1)

/* Vars */
declare @Srccnt int
declare @Tgtcnt int
declare @Sqlstmt nvarchar(4000)
declare @Srcrowcnt nvarchar(500)
declare @Tgtrowcnt nvarchar(500)
declare @SrcTotal bigint
declare @TgtTotal bigint
declare @error int
set @error = 0
BEGIN try
/* Test parameters */

If (@p1_SourceDbname='' or @p2_SourceSchemaname='' or @p3_SourceTablename='' or
    @p4_TargetDbname='' or @p5_TargetSchemaname='' or @p6_TargetTablename='' )
      Begin
                  print ('Invalid parameters passed')
                  return
      end



                  
if object_id(@p4_TargetDbname + '.' + @p5_TargetSchemaname + '.' + @p6_TargetTablename) is  null
      Begin
      -- Copy data from source to target by creating target table
            set @Sqlstmt= 'Select top 0 * INTO ' + @p4_TargetDbname + '.' + @p5_TargetSchemaname + '.' + @p6_TargetTablename +
                  ' FROM '+  @p1_SourceDbname + '.' + @p2_SourceSchemaname + '.' + @p3_SourceTablename
                  --print(@sqlstmt)
                  EXECUTE sp_ExecuteSql @Sqlstmt
                  
      --exec drop_staging_table @objectname = @stagingname
                  EXEC @error=spCloneTableStructure_v1 @SourceDatabase = @p1_SourceDbname,
                                                            @SourceSchema = @p2_SourceSchemaname,
                                                            @SourceTable = @p3_SourceTablename,
                                                            @DestinationDatabase = @p4_TargetDbname,
                                                            @DestinationSchema = @p5_TargetSchemaname,
                                                            @DestinationTable = @p6_TargetTablename,
                                                            @RecreateIfExists  = 0
                  IF @error <> 0
                        return @error
                        
                  
                        
                        ---Replicate existing views of Source table on Target table
                                    

                        EXEC @error =[spViewsV5] @InputDatabase = @p1_SourceDbname,
                                          @InputSchema = @p2_SourceSchemaname,
                                          @InputTable = @p3_SourceTablename,
                                          @OutputDatabase = @p4_TargetDbname,
                                          @OutputSchema = @p5_TargetSchemaname,
                                          @OutputTable=@p6_TargetTablename
                        IF @error <> 0
                              return @error
                        
                        
                        
      End      
       set nocount off
       return 0
      
END try
BEGIN catch
          
      RAISERROR('spcreatehisttbl_v2:raised exception', 16, 1)
end catch
End
0
Comment
Question by:Sandeepiii
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38739143
Check your SP's  
spCloneTableStructure_v1
[spViewsV5]


issue must be there ...
0
 
LVL 15

Accepted Solution

by:
Anuj earned 500 total points
ID: 38739358
The error indicates that you have incorrect number of begin and commit transactions, check the code inside nested stored procedure for extra commit\rollblack or begin tran.
0
 

Author Comment

by:Sandeepiii
ID: 38743556
Hi iam able to copy 60 tables but error comes only after 60 tables.I have 80 tables ,so just wondering if error is there it should be for 1 table why it is coming after 60 tables.Thanks.
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38743578
You might be using transactions in trigger .. so also check the trigger on that table...
another point just add a print to figure out the faulty table..
0
 

Author Closing Comment

by:Sandeepiii
ID: 38809057
Answe is fine.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now