Solved

sql error

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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