?
Solved

sql error

Posted on 2013-01-10
5
Medium Priority
?
704 Views
Last Modified: 2013-01-11
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
Comment
Question by:Meinhoonaa
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38765262
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
 

Author Comment

by:Meinhoonaa
ID: 38765309
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
 

Author Comment

by:Meinhoonaa
ID: 38765312
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
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 38765806
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
 

Author Closing Comment

by:Meinhoonaa
ID: 38767611
Thank you, good catch
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 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