none
Cannot drop the table because it does not exist in the system catalog. Transaction context in use by another session

    Question

  • Hi Every one,

    An error has occurred during report processing. (rsProcessingAborted) Get Online Help

    Query execution failed for data set 'NonFinTran'. (rsErrorExecutingCommand) Get Online Help

    Cannot drop the table '#NonFinTran', because it does not exist in the system catalog. Cannot drop the table '#MultipleNonFinTran', because it does not exist in the system catalog. Transaction context in use by another session.

    NOTENonFinTran & MultipleNonFinTran are the Temp table in my storedPoc.

    Please any help me to solve this issue. 

    Thanks & Regards,

    Anil Kumar


    Anil Kumar

    Monday, December 23, 2013 6:23 AM

Answers

All replies

  • Hi Anil,

    From description it look like you are using Temporary Table in stored procedure.

    Are they get created in the stored procedure based on some scope for example inside if else, you need to check on that condition.

    Before dropping you add code to check whether table exists or not. Like this :

     if object_id('tempdb..#NonFinTran') is not null
    	   drop table #NonFinTran

    If would be really good in case your are able to post your stored procedure code.


    Regards Harsh

    Monday, December 23, 2013 6:40 AM
  • Hi Harsh,

             Thank you for your answer, I am already checking for the table exist or not before dropping the table.

    As follows.

    if exists(Select * From tempdb.dbo.SysObjects Where Name Like '#NonFinTran%')     

    DROP TABLE #NonFinTran    

       
    Regards,

    Anil Kumar


    Anil Kumar

    Monday, December 23, 2013 6:56 AM
  • Hi Anil, 

    I also tried creating the temp table inside stored procedure using condition you specified and i am not able to reproduce it. 

    If possible can you please paste your code.

    OR 

    since temp table is create inside the stored procedure, It scope also lie in the stored procedure, so your table get drop after the scope ends and if you are not using the temp table afterward, you can safely remove the drop script part from stored procedure for temp table.


    Regards Harsh

    Monday, December 23, 2013 7:33 AM
  • Hi Anil,

    Found a interesting blog from Pinal dev, similar to your problem. Here it is - > http://blog.sqlauthority.com/2009/03/29/sql-server-fix-error-msg-2714-level-16-state-6-there-is-already-an-object-named-temp-in-the-database/

    since you are using the 

    if exists(Select * From tempdb.dbo.SysObjects Where Name Like '#NonFinTran%')     

    like operator with % is used to check existence of the table and multiple temp table are created for multiple session for some hex value at the end, you if exists code is failing ,,that might where the problem lies,

    Use simple script as mentioned by me in first answer or just remove the drop code,

    if object_id('tempdb..#NonFinTran') is not null
    	   drop table #NonFinTran

    Hope this is able to help you...


    Regards Harsh

    Monday, December 23, 2013 7:40 AM
  • Check this link for similar post:- http://stackoverflow.com/questions/11986552/how-to-check-correctly-if-a-temporary-table-exists-in-sql-server-2005

    IF OBJECT_ID('tempdb.dbo.#tmpTable1') IS NOT NULL  

    OR

    IF OBJECT_ID('tempdb..#tmpTable1') IS NOT NULL  
                        ** 
                        |
                      use two dots here!

    Basically, this is saying: check in the tempDB and I don't care what schema the table is in





    Mark ANSWER if this reply resolves your query, If helpful then VOTE HELPFUL
    INSQLSERVER.COM Mohammad Nizamuddin


    Monday, December 23, 2013 8:00 AM
  • Hi Harsh,

     Below is my Stored Proc

    SELECT @ServerName=datasource from master.dbo.sysservers WHERE catalog='Voyager'    
     SELECT @ServerName3=datasource from master.dbo.sysservers WHERE catalog='AuditLog'    
     SELECT @ServerName2=datasource from master.dbo.sysservers WHERE catalog='Portal'    
        
     IF @ServerName IS NOT NULL SET @ServerName='[' + @ServerName + '].' ELSE SET @ServerName=''    
     IF @ServerName3 IS NOT NULL SET @ServerName3='[' + @ServerName3 + '].' ELSE SET @ServerName3=''    
     IF @ServerName2 IS NOT NULL SET @ServerName2='[' + @ServerName2 + '].' ELSE SET @ServerName2=''    
        
     IF Exists(Select * From tempdb.dbo.SysObjects Where Name Like '#NonFinTran%')     
      DROP TABLE #NonFinTran    
        
     IF Exists(Select * From tempdb.dbo.SysObjects Where Name Like '#MultipleNonFinTran%')     
      DROP TABLE #MultipleNonFinTran    
        
        
     CREATE TABLE #NonFinTran (FirstName VARCHAR(40), TaxId VARCHAR(40), TrxID VARCHAR(40), Status VARCHAR(255), Field1 VARCHAR(255), Field2 VARCHAR(255),    
       Field3 VARCHAR(255), Field4 VARCHAR(255), Field5 VARCHAR(255), Field6 VARCHAR(255), DateTime DATETIME,     
       BranchID CHAR(3), BankID CHAR(1), FromAccountID VARCHAR(255), FromAccountType VARCHAR(255))    
        
     CREATE TABLE #MultipleNonFinTran (FirstName VARCHAR(40), TaxId VARCHAR(40), TrxID VARCHAR(40), Status VARCHAR(255), Field1 VARCHAR(255), Field2 VARCHAR(255),    
       Field3 VARCHAR(255), Field4 VARCHAR(255), Field5 VARCHAR(255), Field6 VARCHAR(255), DateTime DATETIME,     
       BranchID CHAR(3), BankID CHAR(1), FromAccountID VARCHAR(255), FromAccountType VARCHAR(255))     
        

    INSERT #NonFinTran    
     EXEC('SELECT FirstName, TaxID,     
      TrxID, Status, TrxField1, TrxField2, TrxField3, TrxField4, TrxField5, TrxField6, DateTime, '''', '''', '''', ''''    
     FROM ' + @ServerName3 + 'AuditLog.dbo.CCAuditLogEntryView AS Audit, ' + @ServerName + 'Voyager.dbo.CCUser AS CCUser    
     WHERE CCUser.UserID = Audit.UserID     
      AND Audit.Succeeded = 1     
      AND Audit.TrxID IN (''ChangeBillPayDefaultAccountEdit'',''ChangeExpiryUserPassword'',''ChangePasswordEdit'',    
       ''ChangeUserPassword'',''ManageAddressMaint'',''ManageContactMaint'',''ManageSecretQuestionAnswerEdit'',    
       ''ManageTransLimitMaint'',''OtherBankAccountMaintAdd'',''OtherBankAccountMaintDelete'',''OtherBankAccountMaintEdit'',    
       ''WithinAmBankAccountMaintAdd'',''WithinAmBankAccountMaintDelete'',''WithinAmBankAccountMaintEdit'',    
       ''SetAccountMaskPreferenceAudit'',''ChangeLoginIdAudit'')     
      AND DATEDIFF(DAY, CONVERT(DATETIME, CONVERT(VARCHAR(10), ''' + @StartDate + '''), 103), Audit.DateTime) >= 0     
      AND DATEDIFF(DAY, CONVERT(DATETIME, CONVERT(VARCHAR(10), ''' + @EndDate + '''), 103), Audit.DateTime) <= 0 ')    
        
        
         
     INSERT #MultipleNonFinTran    
     EXEC('SELECT DISTINCT FirstName, TaxID,     
      TrxID, Status, TrxField1, TrxField2, TrxField3, TrxField4, TrxField5, TrxField6, Audit.DateTime as AuditDateTime,    
       (SELECT DISTINCT SUBSTRING(A.BranchCode,3,3)  FROM ' + @ServerName + 'AuditLog.dbo.CCAuditLogEntryView X INNER JOIN ' + @ServerName + 'Voyager.dbo.CCuser U
    ON X.UserId = U.UserId INNER JOIN ' + @ServerName + 'Voyager.dbo.AMHZ_CustomerProfile P ON P.EnrolId = U.TAXID 
    INNER JOIN ' + @ServerName + 'Voyager.dbo.AMHZ_AccountListing A ON A.CIFNO = P.CIFNO
    WHERE X.UserId = Audit.UserId AND A.AccountNo = SUBSTRING(Audit.TrxField1,11,16) AND P.CIFNO = A.CIFNO
    AND (SUBSTRING(A.BranchCode,3,3) <> NULL OR SUBSTRING(A.BranchCode,3,3) <> '''')
    AND CHARINDEX(''AccountID='', Audit.TrxField1, 1) > 0),
      SUBSTRING(TrxField1,14,1), CASE WHEN CHARINDEX(''AccountID='', TrxField1, 1) > 0 THEN     
      SUBSTRING(TrxField1,11,16) ELSE '''' END, CASE WHEN CHARINDEX(''AccountType='', TrxField2, 1) > 0 THEN SUBSTRING(TrxField2,13,3) ELSE '''' END    
     FROM ' + @ServerName3 + 'AuditLog.dbo.CCAuditLogEntryView AS Audit, ' + @ServerName + 'Voyager.dbo.CCUser AS CCUser    
     WHERE CCUser.UserID = Audit.UserID     
      AND Audit.Succeeded = 1     
      AND Audit.TrxID IN (''SetAccountAttributesAudit'',''SetAccountFriendlyNameAudit'',    
      ''AccountProfileMaintULDelete'',''AccountProfileMaintLHAAdd'',''AccountProfileMaintLSCAdd'')     
      AND DATEDIFF(DAY, CONVERT(DATETIME, CONVERT(VARCHAR(10), ''' + @StartDate + '''), 103), Audit.DateTime) >= 0     
      AND DATEDIFF(DAY, CONVERT(DATETIME, CONVERT(VARCHAR(10), ''' + @EndDate + '''), 103), Audit.DateTime) <= 0 ')  


      SET @stmt = '    
     SELECT * FROM    
     (    
     SELECT BranchName,
     CASE WHEN SUBSTRING(FromAccountID,1,6) IN (''519901'',''559409'')  THEN ''DC''  
    ELSE       
     CASE FromAccountType WHEN ''01'' THEN ''SA '' WHEN ''02'' THEN ''CA '' WHEN ''03'' THEN ''FD ''     
        WHEN ''SA'' THEN ''SA '' WHEN ''CA'' THEN ''CA '' WHEN ''FD'' THEN ''FD '' ELSE FromAccountType + '' ''    
     END 
     END +     
      
    case when Len(FromAccountID) =16 Then  
       CASE FromAccountType WHEN ''VC'' THEN   
       SUBSTRING(FromAccountID,1,6)+''******''+SUBSTRING(FromAccountID,13,4)      
        WHEN ''MC'' THEN SUBSTRING(FromAccountID,1,6)+''******''+SUBSTRING(FromAccountID,13,4)  END  
         when Len(FromAccountID) =15 Then 
    CASE FromAccountType WHEN ''VC'' THEN   
       SUBSTRING(FromAccountID,1,6)+''******''+SUBSTRING(FromAccountID,13,3)      
        WHEN ''MC'' THEN SUBSTRING(FromAccountID,1,6)+''******''+SUBSTRING(FromAccountID,13,3)  END  
         ELSE FromAccountID   
    ENd  
      
      
     AS FromAcctNo,    
     CASE TrxId  
     WHEN ''AccountProfileMaintLHAAdd'' THEN ''Link Account/Card''    
     WHEN ''AccountProfileMaintLSCAdd'' THEN ''Link Account/Card''    
     WHEN ''APMFamilyFirstAdd'' THEN ''Link Family First Account''    
     WHEN ''AccountProfileMaintULDelete'' THEN ''Unlink Account/Card''    
     WHEN ''BalInqFD'' THEN CASE  WHEN Field3 IN (''APMLink=SUCCESS'') THEN ''APMLink Success'' ELSE ''Fixed Deposit Balance Inquiry'' END  
     WHEN ''BalInqCASA'' THEN CASE  WHEN Field3 IN (''APMLink=SUCCESS'') THEN ''APMLink Success'' ELSE
         CASE WHEN FromAccountType IN (''SA'',''01'') THEN ''Savings Account Balance Inquiry'' 
        ELSE ''Current Account Balance Inquiry'' 
       END 
      END    
     WHEN ''StopCheck'' THEN ''Stop Cheque Request''    
     WHEN ''CheckReorder'' THEN ''Order Your Cheque''    
     WHEN ''CheckInquiry'' THEN ''Cheque Inquiry''    
     WHEN ''TransHistFD'' THEN ''Fixed Deposit Transaction History''    
     WHEN ''TransHistCASA'' THEN    
      CASE WHEN FromAccountType IN (''SA'',''01'') THEN ''Savings Account Transaction History'' ELSE ''Current Account Transaction History'' END    
     WHEN ''StmtInqCC'' THEN    
      CASE WHEN FromAccountType IN (''DR'',''03'') THEN ''Debit Card Statement Inquiry'' ELSE ''Credit Card Statement Inquiry'' END    
     WHEN ''StmtInqDA'' THEN    
      CASE WHEN FromAccountType IN (''SA'',''01'') THEN ''Savings Account Statement Inquiry'' ELSE ''Current Account Statement Inquiry'' END    
     WHEN ''StmtReq'' THEN ''Printed Statement Request''    
     WHEN ''StmtInqIAMSTAR'' THEN ''E-AMSTAR Statement Inquiry''    
     WHEN ''Repayment/Transfer Inquiry'' THEN ''Repayment/Transfer Inquiry''    
     WHEN ''Account Inquiry'' THEN ''Account Inquiry''    
     WHEN ''Payment Inquiry'' THEN ''Payment Inquiry''    
     END AS TransType,    
     FirstName AS CustomerName,    
     TaxId, CONVERT(VARCHAR, DateTime, 103) AS Date, CONVERT(VARCHAR, DateTime, 108) AS Time    
     FROM #NonFinTran, ' + @ServerName3 + 'Portal.dbo.TB_Branch AS TB_Branch    
     WHERE     
     (    
     BranchId = TB_Branch.BranchCode     
     AND (TB_Branch.InstCode IN (''00001'', ''00003'',''001'',''002''))    
     AND (FromAccountType IN (''SA'', ''CA'', ''FD'', ''01'', ''02'', ''03'')))'    
        
    EXEC (@stmt)

    IF Exists(Select * From tempdb.dbo.SysObjects Where Name Like '#NonFinTran%')     
     DROP TABLE #NonFinTran    
        
     IF Exists(Select * From tempdb.dbo.SysObjects Where Name Like '#MultipleNonFinTran%')     
      DROP TABLE #MultipleNonFinTran    
         


    Anil Kumar

    Monday, December 23, 2013 8:02 AM
  • Hi Anil,

    As such your query look fine:

    Instead of using :

    IF Exists(Select * From tempdb.dbo.SysObjects Where Name Like '#NonFinTran%')     

      DROP TABLE #NonFinTran   

    Just change it to 

    if object_id('tempdb..#NonFinTran') is not null
    	   drop table #NonFinTran

    same to other drop statements also. 

    For complete description, please refer this blog - >  http://blog.sqlauthority.com/2009/03/29/sql-server-fix-error-msg-2714-level-16-state-6-there-is-already-an-object-named-temp-in-the-database/

    Already explain in above post.


    Regards Harsh

    Monday, December 23, 2013 8:50 AM