Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Microsoft SQL Server 2008

Posted on 2013-01-02
5
Medium Priority
?
411 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 1000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

670 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