Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

sql error

TITLE: Microsoft SQL Server Management Studio
------------------------------

There are uncommitted transactions. Do you wish to commit these transactions before closing the window?

------------------------------
BUTTONS:

&Yes
&No
Cancel
------------------------------

Code below:
USE [t21_maryland_dev]
GO
/****** Object:  StoredProcedure [dbo].[sp_MoveData_StateTransaction_rev2]    Script Date: 01/10/2013 16:29:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Sam Fecto>
-- Create date: <01/02/2013,,>
-- Description:      <Transfer data from Title App tables to State Transaction tables>
-- =============================================
--exec sp_MoveData_StateTransaction_rev2
ALTER PROCEDURE [dbo].[sp_MoveData_StateTransaction_rev2]
as
DECLARE @TitleAppData TABLE(row_Id int identity, title_app_id int)

INSERT INTO @TitleAppData          
      SELECT [title_app_id]
      FROM [t21_maryland_dev].[dbo].[title_app] ta
      where ta.[deal_status_id] = 3 -- finalized status
      and not exists (select 1 from [dbo].[State_Transactions] st where st.[title_app_id] = ta.[title_app_id] and st.transmit_status_id = 2) -- non-sent transactions
 
-- LOOP THRU THE DEALER TABLE
DECLARE @cnt INT = 0;
SET @cnt = (SELECT COUNT(1) FROM @TitleAppData);
DECLARE @rowNum int = 1;
DECLARE @newTransId int = 0;
WHILE (@rowNum <= @cnt)
BEGIN TRY
      BEGIN TRANSACTION
      -- add a transaction object      
      -- get the title_app_id from the temp dealer table...use the row_id to keep track of where you are in the loop
      DECLARE @title_app_id int = (SELECT title_app_id FROM @TitleAppData where row_Id = @rowNum);  
      INSERT INTO [dbo].[State_Transactions]
         ([title_app_id]            
         ,transmit_status_id
       ,transmit_datetime
            , location_id
            ,[operator_id]
            , record_type_code
            , state_trans_type_code
            ,[transaction_datetime]
            ,[effective_date]
            )
      SELECT [title_app_id]
                    ,1
                    ,null
                    ,'TEC'
                    ,'DLR'
                    , '10' -- this can be 10 or 15 (15 are from the Title_App_Lien table)
                    , 'T3' -- this can be various values
                    ,TransactionDate --,<transaction_datetime, datetime,>
              ,TransactionDate --,<effective_date, datetime,>
      FROM [t21_maryland_dev].[dbo].[title_app] ta
      where ta.title_app_id = @title_app_id
 
      SET @newTransId = @@IDENTITY;      
      print @newTransId

      -- build out the logic to get the rest of the tables
      INSERT INTO [dbo].[State_Transactions_Liens](trans_id)
      VALUES (@newTransId);
      
      INSERT INTO [dbo].[State_Transactions_Vehicles](trans_id, vehicle_vin_old)
      SELECT distinct @newTransId, tv.vin
      FROM [dbo].[title_app_vehicles] tv
      where tv.title_app_id = @title_app_id
      COMMIT TRANSACTION;
      SET @rowNum = @rowNum + 1
      END TRY        
      BEGIN CATCH
            IF @@TRANCOUNT > 0
                  ROLLBACK;
      END CATCH
0
Meinhoonaa
Asked:
Meinhoonaa
  • 3
  • 2
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
I do not see an error in your procedure code (at least immediately without being able to run and test it).  That kind of error often happens when you are closing the query window (a session to the database server) after trying to create or change something in SSMS and it begins a TRANSACTION but completely fails and does not get to your rollback.  Then when you fix the error and run it again, it begins ANOTHER TRANSACTION nested inside the first one and successful completes or rolls back, but the outer trans is still there.  Usually you just say no to the commit request and run everything again and it works just fine.
0
 
MeinhoonaaAuthor Commented:
USE [t21_maryland_dev]
GO
/****** Object:  StoredProcedure [dbo].[sp_MoveData_StateTransaction_rev2]    Script Date: 01/10/2013 17:35:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Sam Fecto>
-- Create date: <01/02/2013,,>
-- Description:      <Transfer data from Title App tables to State Transaction tables>
-- =============================================
--exec sp_MoveData_StateTransaction_rev2
ALTER PROCEDURE [dbo].[sp_MoveData_StateTransaction_rev2]
as
DECLARE @TitleAppData TABLE(row_Id int identity, title_app_id int)
print 'starting move data sp';
INSERT INTO @TitleAppData          
      SELECT [title_app_id]
      FROM [t21_maryland_dev].[dbo].[title_app] ta
      where ta.[deal_status_id] = 3 -- finalized status
      and not exists (select 1 from [dbo].[State_Transactions] st where st.[title_app_id] = ta.[title_app_id] and st.transmit_status_id = 2) -- non-sent transactions  
-- LOOP THRU THE DEALER TABLE
DECLARE @cnt INT = 0;
SET @cnt = (SELECT COUNT(1) FROM @TitleAppData);
print 'rec count = ' + cast(@cnt as varchar(10));
DECLARE @rowNum int = 1;
DECLARE @newTransId int = 0;
WHILE (@rowNum <= @cnt)
      BEGIN TRY
            print 'row ' + cast(@rownum as varchar(10)) + '  count ' + cast(@cnt as varchar(10))            
            BEGIN TRANSACTION
            -- add a transaction object      
            -- get the title_app_id from the temp dealer table...use the row_id to keep track of where you are in the loop
            DECLARE @title_app_id int = (SELECT title_app_id FROM @TitleAppData where row_Id = @rowNum);  
            print 'before insert state_transactions';
            INSERT INTO [dbo].[State_Transactions]
               ([title_app_id]            
               ,transmit_status_id
               ,transmit_datetime
                  , location_id
                  ,[operator_id]
                  , record_type_code
                  , state_trans_type_code
                  ,[transaction_datetime]
                  ,[effective_date]
                  )
            SELECT [title_app_id]
                          ,1
                          ,GetDate()
                          ,'TEC'
                          ,'DLR'
                          , '10' -- this can be 10 or 15 (15 are from the Title_App_Lien table)
                          , 'T3' -- this can be various values
                          ,TransactionDate --,<transaction_datetime, datetime,>
                          ,TransactionDate --,<effective_date, datetime,>
            FROM [t21_maryland_dev].[dbo].[title_app] ta
            where ta.title_app_id = @title_app_id             
            print 'after insert of state_transactions';       
            SET @newTransId = @@IDENTITY;      
            print @newTransId
            -- build out the logic to get the rest of the tables
            print 'before insert of liens';
            INSERT INTO [dbo].[State_Transactions_Liens](trans_id)
            VALUES (@newTransId);             
            INSERT INTO [dbo].[State_Transactions_Vehicles](trans_id, vehicle_vin_old)
            SELECT distinct @newTransId, tv.vin
            FROM [dbo].[title_app_vehicles] tv
            where tv.title_app_id = @title_app_id             
            print 'after insert of liens';            
            COMMIT TRANSACTION;            
            print 'after commit';            
      END TRY        
      BEGIN CATCH
            print 'exception caught';
            PRINT 'Error: ' + ERROR_MESSAGE();
            IF @@TRANCOUNT > 0
                  ROLLBACK;
      END CATCH      
      SET @rowNum = @rowNum + 1
0
 
MeinhoonaaAuthor Commented:
Though I replaced the null with a date but it still gives the same error
exception error
Error: Cannot insert the value NULL into column 'transaction_datetime', table 't21_maryland_dev.dbo.State_Transactions'; column does not allow nulls. INSERT fails.
row 1  count 3
before insert state_transactions
0
 
Chris LuttrellSenior Database ArchitectCommented:
It is the column 'transaction_datetime' that is throwing the error but you put your hard-coded GETDATE() on the column "transmit_datetime".   The value for 'transaction_datetime' looks to be coming from the TransactionDate column in your [t21_maryland_dev].[dbo].[title_app] table which must allow null values for that column.  Change your select to wrap TransactionDate with a COALESCE like this to supply a value if it is null.
COALESCE(TransactionDate,GETDATE())

Open in new window

0
 
MeinhoonaaAuthor Commented:
Thank you, good catch
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now