Solved

Microsoft SQL Server 2008

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

739 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