?
Solved

sql error

Posted on 2013-01-10
5
Medium Priority
?
696 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
[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
  • 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

719 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