Sunday, May 12, 2013

[T-SQL] Whats wrong with it, Transaction mismatch.. Please help.

[T-SQL] Whats wrong with it, Transaction mismatch.. Please help.

Whats wrong with it, Transaction mismatch.. Please help.

Posted: 12 May 2013 12:01 AM PDT

IF OBJECT_ID('test_parent_table') IS NOT NULLDROP TABLE test_parent_tableCREATE TABLE test_parent_table( id INT, name VARCHAR(30))GOIF OBJECT_ID('DataError') IS NOT NULLDROP TABLE DataErrorCREATE TABLE DataError( ErrorNo INT, ErrorDesc VARCHAR(1000), ErrorProc VARCHAR(100), ErrorLineNumber INT, ErrorDateTime DATETIME2(7))GOIF OBJECT_ID('ThrowError') IS NOT NULLDROP PROCEDURE ThrowErrorGOCREATE PROCEDURE ThrowErrorASBEGIN DECLARE @errorNo INT, @errorDesc VARCHAR(1000), @errorProc VARCHAR(100), @errorLineNumber INT, @errorDateTime DATETIME2(7) SELECT @errorNo = ERROR_NUMBER(), @errorDesc = ERROR_MESSAGE(), @errorProc = ERROR_PROCEDURE(), @errorLineNumber = ERROR_LINE(), @errorDateTime = GETDATE() DECLARE @id INT = XACT_STATE() IF( @errorNo < 50000) BEGIN IF XACT_STATE() > 0 ROLLBACK INSERT INTO DataError SELECT @errorNo, @errorDesc, @errorProc, @errorLineNumber, @errorDateTime --RAISERROR(@errorDesc,16,1); --RETURN 1 END ELSE BEGIN RAISERROR(@errorDesc,16,1); ENDEND;GOIF OBJECT_ID('test_child') IS NOT NULLDROP PROCEDURE test_childGOCREATE PROCEDURE test_child( @id INT, @name VARCHAR(50))ASBEGIN BEGIN TRY INSERT INTO test_parent_table SELECT @id, @name SELECT 1/0 RETURN 0 END TRY BEGIN CATCH EXEC ThrowError RETURN 1 END CATCHENDGOIF OBJECT_ID('test_parent') IS NOT NULLDROP PROCEDURE test_parentGOCREATE PROCEDURE test_parent( @id INT, @name VARCHAR(50))ASBEGIN BEGIN TRY BEGIN TRAN INSERT INTO test_parent_table VALUES( 1,'Harry') EXEC test_child 2,'Kristena' SELECT 1/0 INSERT INTO test_parent_table VALUES( 3,'Chris') IF( @@TRANCOUNT > 0) COMMIT RETURN 0 END TRY BEGIN CATCH EXEC ThrowError END CATCHENDGO /* Test casesEXEC test_parent 0,'test'SELECT * FROM test_parent_table SELECT * FROM DataError*/WHEn I run the test case above , the output in DataError is given below:-ErrorDesc1 Row :- Divide by zero error encountered.2 Row :- Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.Here second row is not supposed to come, this is sucking. Please help!RegardsAshok

Performance Tuning

Posted: 11 May 2013 03:39 AM PDT

Hi, I am working on tuning an sp . I tried to remove the cursor and get the values in a temp table, but the performance did not improve. The first WHILE loop is causing performance issues. Is there any way I can make it more usable ?ALTER PROCEDURE [dbo].[spreport] @pSiteSeqId VARCHAR(MAX), @pStartDate DATETIME2, @pEndDate DATETIME2 ASBEGINSET NOCOUNT ON;IF @pSiteSeqId LIKE '%<Filter>%'BEGIN DECLARE @tSiteSeqID VARCHAR(MAX) DECLARE @FilterXML XML = CAST(@pSiteSeqId AS XML) DECLARE @tmpSites TABLE (SiteSeqID INT) INSERT INTO @tmpSites (SiteSeqID) SELECT Sites.ID.value('@Value[1]', 'INT') FROM @FilterXML.nodes('/Filter/Site_Seq_Id') as Sites(ID) SELECT @tSiteSeqID = COALESCE(@tSiteSeqID + ',', '') + CAST(SiteSeqID AS VARCHAR(10)) FROM @tmpSites SET @pSiteSeqId = @tSiteSeqIDEND DECLARE @Sensor_State varchar(3) DECLARE @Sensor_State2 varchar(29) DECLARE @Tank_Config_Seq_Id int DECLARE @Site_Seq_Id int DECLARE @Site_Name varchar(30) DECLARE @Address_Line_1 varchar(30) DECLARE @Address_Line_2 varchar(30) DECLARE @City varchar(20) DECLARE @State varchar(2) DECLARE @Zip_Code varchar(9) DECLARE @Tank_Config_Nbr varchar(12) DECLARE @Tank_Number smallint DECLARE @Fuel_Type varchar(8) DECLARE @Poll_Date datetime DECLARE @Water_Gallons int DECLARE @PollDate varchar(30) DECLARE @Water_Inches real DECLARE @Fuel_Inches real DECLARE @Temperature real DECLARE @Gallons int DECLARE @Deliveries float DECLARE @InventDisp float DECLARE @SensorStatus varchar(30) DECLARE @ATGLeakTest varchar(30) DECLARE @RateResult varchar(23) DECLARE @NextDayInventory int DECLARE @Test_Passed_Cd varchar(25) DECLARE @Leak_Test_Type smallint DECLARE @Compliance int DECLARE @Company_Name varchar(50) DECLARE @Tank_Material varchar(20) DECLARE @Compliance_Method varchar(50) DECLARE @Capacity_qty int DECLARE @LOOPCOUNT int DECLARE @EndingInvent int DECLARE @TotalInvt float DECLARE @TotalDel float DECLARE @DailyAvg float DECLARE @StartInvent int DECLARE @CustID VARCHAR(25) DECLARE @Equipment_Name VARCHAR(200) DECLARE @Periodic_Status VARCHAR(10) Declare @CompTemp as table ( Sensor_State varchar(29), Tank_Config_Seq_Id int, Site_Seq_Id int, Site_Name varchar(30), Address_Line_1 varchar(30), Address_Line_2 varchar(30), City varchar(20), State varchar(2), Zip_Code varchar(9), Tank_Config_Nbr varchar(12), Tank_Number smallint, Fuel_Type varchar(20), Poll_Date datetime, Water_Gallons int, PollDate varchar(30), Water_Inches real, Fuel_Inches real, Temperature real, Gallons int, Deliveries float, InventDisp float, SensorStatus varchar(30), ATGLeakTest varchar(30), RateResult varchar(23), Compliance int, EndingInvent int, TotalInvtDisp int, TotalDeliveries float, DailyAvg float, Company_Name varchar(50), Tank_Material varchar(20), Compliance_Method varchar(50), Capacity_Qty int , CustID VARCHAR(25) ) --- parse site id list into temp table declare @TempList as table ( recid INT IDENTITY(1,1), SiteID int, Tank_Number int ) DECLARE @SiteID varchar(10), @Pos int INSERT INTO @TempList (SiteID,Tank_Number) SELECT a.Site_Seq_Id, a.Tank_Number FROM dbo.Tanks a INNER JOIN dbo.fn_CreateTableFromCSV(@pSiteSeqId) T ON A.Site_Seq_Id = T.value SET @LOOPCOUNT = 1 WHILE exists(Select 1 from @TempList where recid = @LOOPCOUNT) Begin Select @SiteID=SiteID,@Tank_Number=Tank_Number from @TempList where recid = @LOOPCOUNT Set @Poll_Date=@pstartDate While (datediff(day,@Poll_Date,@pendDate)>=0) Begin if exists( select * FROM FN_vwComplianceFinal tmp WHERE Site_Seq_Id=@SiteID and Tank_Number=@Tank_Number and datediff (day,Poll_Date,@Poll_Date )=0 ) Begin DECLARE @Cursor1 CURSOR SET @Cursor1 = CURSOR FAST_FORWARD FOR SELECT Top 1 Sensor_State, Sensor_State2, tmp.Tank_Config_Seq_Id, Site_Seq_Id, Site_Name, Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr, Tank_Number, Fuel_Type, Water_Gallons, PollDate, Water_Inches, Fuel_Inches, Temperature, Gallons, Deliveries, InventDisp, SensorStatus, ATGLeakTest, RateResult, Company_Name,tank_material,Compliance_Method,Capacity_Qty, Test_Passed_Cd, Leak_Test_Type,Cust_Site_ID ,Equipment_Name,Periodic_Status FROM vwComplianceFinal tmp left join vwCompliance_LeakTest cl on tmp.Tank_Config_Seq_Id =cl.Tank_Config_Seq_Id AND cl.TestStartDate = tmp.PollDate and cl.Leak_Test_type != 2 WHERE Site_Seq_Id=@SiteID and Tank_Number=@Tank_Number and datediff (day,Poll_Date,@Poll_Date )=0 order by convert(time,Polling_date) desc OPEN @Cursor1 FETCH NEXT FROM @Cursor1 INTO @Sensor_State, @Sensor_State2, @Tank_Config_Seq_Id, @Site_Seq_Id, @Site_Name, @Address_Line_1, @Address_Line_2, @City, @State, @Zip_Code, @Tank_Config_Nbr, @Tank_Number, @Fuel_Type, @Water_Gallons, @PollDate, @Water_Inches, @Fuel_Inches, @Temperature, @Gallons, @Deliveries, @InventDisp, @SensorStatus, @ATGLeakTest, @RateResult,@Company_Name,@tank_material,@Compliance_Method,@Capacity_Qty, @Test_Passed_Cd, @Leak_Test_Type,@custID,@Equipment_Name,@Periodic_Status WHILE @@FETCH_STATUS = 0 BEGIN Set @ATGLeakTest= CASE WHEN @Equipment_name LIKE '%VEEDER%' THEN case when @Test_Passed_Cd like '%CSLD%' then @Test_Passed_Cd else @Periodic_Status + ' 0.2' END ELSE case when @Test_Passed_Cd like '%CSLD%' then @Test_Passed_Cd else case when @Leak_Test_Type is null then CAST(@Test_Passed_Cd AS varchar(30)) else case when @Leak_Test_Type = 0 then CAST(@Test_Passed_Cd As varchar(10)) + ' 0.2' when @Leak_Test_Type = 1 then CAST(@Test_Passed_Cd As varchar(10)) + ' 0.1' when @Leak_Test_Type = 2 then CAST(@Test_Passed_Cd As varchar(10)) + ' Gross' End End END END SELECT @NextDayInventory=Gallons FROM FN_vwComplianceFinal WHERE Site_Seq_Id = @Site_Seq_Id AND Tank_Config_Seq_Id = @Tank_Config_Seq_Id AND PollDate = CONVERT(VARCHAR, DATEADD(day, 1, @Poll_Date), 101) IF @Gallons IS NOT NULL AND @NextDayInventory IS NOT NULL --AND @Deliveries IS NOT NULL BEGIN if @Deliveries is not null Begin SET @InventDisp = ((CAST(@Gallons AS float) + @Deliveries) - CAST(@NextDayInventory As float)) end else begin SET @InventDisp = ((CAST(@Gallons AS float)) - CAST(@NextDayInventory As float)) end END select @EndingInvent = SUM(Gallons) FROM FN_vwComplianceFinal WHERE Site_Seq_Id = @Site_Seq_Id and Tank_Number = @Tank_Number and PollDate = CONVERT(VARCHAR, DATEADD(day, 1, @pendDate), 101) Group By Site_seq_id, Tank_Number select @Compliance=Compliance from sites where Site_Seq_Id = @Site_Seq_Id INSERT INTO @CompTemp (Sensor_State, Tank_Config_Seq_Id, Site_Seq_Id, Site_Name, Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr, Tank_Number, Fuel_Type, Poll_Date, Water_Gallons, PollDate, Water_Inches, Fuel_Inches, Temperature, Gallons, Deliveries, InventDisp, SensorStatus, ATGLeakTest, RateResult, Compliance, EndingInvent, TotalInvtDisp, TotalDeliveries, DailyAvg,Company_Name,Tank_Material,Compliance_Method,Capacity_Qty,CustID) VALUES (@Sensor_State, @Tank_Config_Seq_Id, @Site_Seq_Id, @Site_Name, @Address_Line_1, @Address_Line_2, @City, @State, @Zip_Code, @Tank_Config_Nbr, @Tank_Number, @Fuel_Type, @Poll_Date, @Water_Gallons, @PollDate, @Water_Inches, @Fuel_Inches, @Temperature, @Gallons, @Deliveries, @InventDisp, @SensorStatus, @ATGLeakTest, @RateResult, @Compliance, @EndingInvent, @TotalInvt, @TotalDel, @DailyAvg,@Company_Name,@Tank_Material,@Compliance_Method,@Capacity_qty,@CustID) FETCH NEXT FROM @Cursor1 INTO @Sensor_State, @Sensor_State2, @Tank_Config_Seq_Id, @Site_Seq_Id, @Site_Name, @Address_Line_1, @Address_Line_2, @City, @State, @Zip_Code, @Tank_Config_Nbr, @Tank_Number, @Fuel_Type, @Water_Gallons, @PollDate, @Water_Inches, @Fuel_Inches, @Temperature, @Gallons, @Deliveries, @InventDisp, @SensorStatus, @ATGLeakTest, @RateResult,@Company_Name,@tank_material,@Compliance_Method,@Capacity_Qty, @Test_Passed_Cd, @Leak_Test_Type,@custID,@Equipment_Name,@Periodic_Status END CLOSE @Cursor1 DEALLOCATE @Cursor1 End Else Begin INSERT INTO @CompTemp (Tank_Config_Seq_Id, Site_Seq_Id, Site_Name, Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr, Tank_Number, Fuel_Type, Poll_Date, PollDate, Compliance,Company_Name,Capacity_Qty,CustID ) Select t.Tank_Config_Seq_Id, s.Site_Seq_Id, s.Site_Name, s.Address_Line_1, s.Address_Line_2, s.City, s.State, s.Zip_Code, Tank_Config_Nbr, @Tank_Number, Fuel_Type, @Poll_Date, CONVERT(VARCHAR, @Poll_Date, 101), s.Compliance,c.Company_Name,Tank_Models.Capacity_Qty,s.Cust_Site_ID From Sites s inner join Tanks t on s.Site_Seq_Id =t.Site_Seq_Id join Tank_Models ON t.Tank_Model_Seq_Id = Tank_Models.Tank_Model_Seq_Id join Companies c on c.Company_Id=s.Company_Id where s.Site_Seq_Id=@SiteID and t.Tank_Number=@Tank_Number End Set @Poll_Date=dateadd(day, 1,@Poll_Date) --Select @Poll_Date End Set @LOOPCOUNT +=1 --looping to next --select @LOOPCOUNT End SELECT Sensor_State, Tank_Config_Seq_Id, Site_Seq_Id, Site_Name, Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr, Tank_Number, Fuel_Type, Poll_Date, Water_Gallons, PollDate, Water_Inches, Fuel_Inches, Temperature, Gallons, Deliveries, InventDisp, SensorStatus, ATGLeakTest, RateResult, Compliance, EndingInvent, TotalInvtDisp, TotalDeliveries, DailyAvg,Company_Name,Tank_Material,Compliance_Method,Capacity_Qty,CustID FROM @CompTemp Order by Site_Seq_Id, Tank_Number, Poll_Date ENDGO
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog