Solved

sql error

Posted on 2013-01-10
5
687 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 26

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 26

Accepted Solution

by:
Chris Luttrell earned 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now