Solved

Microsoft SQL Server 2008

Posted on 2013-01-02
5
401 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

895 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

14 Experts available now in Live!

Get 1:1 Help Now