Solved

Microsoft SQL Server 2008

Posted on 2013-01-02
5
409 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

617 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