Execute a procedure within a procedure - Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Execute a procedure within a procedure - Error

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-05-04 : 09:07:53

HI i am trying to execute a procedure within a procedure. For this i am creating a temporary table and inserting the ouput of a stored procedure in it. The temporary table has the same number of columns as that of Stored procedure output. I used a Query like this Below -

Create table #Temp (Employee_NBR INT,Employee_NM VARCHAR(100),Leave_type_NBR INT,Leave_Type_NM VARCHAR(50),Available_Leave Decimal(4,2))
INSERT INTO #Temp (Employee_NBR,Employee_NM,Leave_Type_NBR,Leave_Type_NM,Available_Leave)
EXEC [dbo].[USP_Get_Employee_Available_Leave] @Employee_NBR= 11,
@Leave_Type_NBR=0,@User_NBR=11,@Page_NBR=40.

But when i execute it i get an error like this

Msg 3930, Level 16, State 1, Procedure USP_Insert_Error_Log, Line 42
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

The SP USP_Insert_Error_Log is been created to insert the errors that comes from application in to a error log table. I dont understand this error message. Can anyone help me out.

Thanks in advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-04 : 09:14:48

just try running the sp without the insert. I think theres an error inside the sp which you'll have to rollback to get rid of the error.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-04 : 09:18:03

Post the code of your SP USP_Get_Employee_Available_Leave & USP_Insert_Error_Log. It did not handle the error properly

KH
[spoiler]Time is always against us[/spoiler]

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2009-05-04 : 12:58:37

your query is correct, but the error is showing due to problem in inside code of either in
USP_Get_Employee_Available_Leave or in USP_Insert_Error_Log.

malay

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-05-05 : 01:47:09

quote:
Originally posted by malaytech2008

your query is correct, but the error is showing due to problem in inside code of either in
USP_Get_Employee_Available_Leave or in USP_Insert_Error_Log.

malay

employee_NBR Employee_NM                                                                                                                                                                                                                                                      Leave_Type_NBR Leave_Type_NM                                      Available_Leave
------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------- -------------------------------------------------- ---------------------------------------
11           Swathi Gardas                                                                                                                                                                                                                                                    1              Casual Leave                                       -1.5
11           Swathi Gardas                                                                                                                                                                                                                                                    2              Sick Leave                                         1.5
11           Swathi Gardas                                                                                                                                                                                                                                                    3              Privilege Leave                                    6.3
11           Swathi Gardas                                                                                                                                                                                                                                                    7              Compensatory Off                                   2.0
11           Swathi Gardas                                                                                                                                                                                                                                                    8              Bereavement Leave                                  NULL
11           Swathi Gardas                                                                                                                                                                                                                                                    10             CL                                                 364.0
11           Swathi Gardas                                                                                                                                                                                                                                                    11             sefs                                               0.0
11           Swathi Gardas                                                                                                                                                                                                                                                    12             b                                                  0.0
11           Swathi Gardas                                                                                                                                                                                                                                                    13             zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz          NULL
11           Swathi Gardas                                                                                                                                                                                                                                                    14             afasvfad                                           NULL
11           Swathi Gardas                                                                                                                                                                                                                                                    15             6646465464646                                      NULL
11           Swathi Gardas                                                                                                                                                                                                                                                    16             ##$$@$@@$@#$#$@#@#$ananon222222222fjajopoasjopjopa NULL
11           Swathi Gardas                                                                                                                                                                                                                                                    17             anfn                                               NULL
11           Swathi Gardas                                                                                                                                                                                                                                                    18             sadasd                                             NULL
11           Swathi Gardas                                                                                                                                                                                                                                                    19             Leave policy                                       NULL
11           Swathi Gardas                                                                                                                                                                                                                                                    20             TestPriti1                                         1.5

The above is the ouput that comes when i execute the USP_Get_Employee_Available_Leave like this - EXEC [dbo].[USP_Get_Employee_Available_Leave] @Employee_NBR= 11,
@Leave_Type_NBR=0,@User_NBR=11,@Page_NBR=40

The Code for USP_Unser_Error_Log is

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[USP_Insert_Error_Log]
@Error_MSG	VARCHAR(1000),
@Page_NBR	INT,
@Host_NM	VARCHAR(50),
@User_NBR	INT
AS

BEGIN

	SET NOCOUNT ON;
	
	DECLARE @User_NM VARCHAR(50)
	DECLARE @Page_NM VARCHAR(50)
	DECLARE @Error_NBR	INT
	--Get User Name
	SELECT @User_NM = User_NM FROM TEmployee WHERE Employee_NBR = @User_NBR

	--Get Page Name
	SELECT @Page_NM = Menu_NM FROM TApplication_Menu WHERE App_Menu_NBR = @Page_NBR
	
	--Insert record into TError_Log Table
	INSERT INTO TError_Log ( Date
							,Error
							,Module_NM
							,Host_NM
							,User_NM )
					VALUES ( GETDATE()
					        ,@Error_MSG
						    ,@Page_NM
						    ,@Host_NM
						    ,@User_NM )

	--Get Last inserted Error Messge Number
	SET @Error_NBR=SCOPE_IDENTITY()
	
	--Return Last inserted Error Messge Number
	SELECT @Error_NBR AS Record_NBR

END

i am getting proper output when i execute the stored procedure. But there is a problem in storing the output of stored procedure in #Temp Table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-05 : 01:50:12

Post the code of the other procedure, where you handle your transaction.

E 12°55'05.63"
N 56°04'39.26"

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-05-05 : 01:53:09

quote:
Originally posted by Peso

Post the code of the other procedure, where you handle your transaction.

E 12°55'05.63"
N 56°04'39.26"

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[USP_Get_Employee_Available_Leave]
@Employee_NBR	INT,
@Leave_Type_NBR	INT,
@User_NBR		INT,
@Page_NBR		INT
AS

BEGIN

	SET NOCOUNT ON;
	BEGIN TRY

		DECLARE @Error_NBR INT
		DECLARE @Year_NBR INT
		DECLARE @Current_DT DATETIME


		--Check permission
		SELECT @Error_NBR = [dbo].[FN_Check_Permission] (@User_NBR, @Page_NBR, 'READ')

		IF (@Error_NBR <> 0 )
		BEGIN 
			SELECT Message_NBR, Message_Value FROM TError_Message WHERE Message_NBR = @Error_NBR
			RETURN
		END

		--Get Current date and remove time
		SET @Current_DT = CONVERT(DATETIME,CONVERT(CHAR(12),GETDATE()),102)

		--Get Current Year
		SET @Year_NBR = DATEPART(yy,@current_DT)
	
		--Return Available Leaves
		SELECT e.employee_NBR
			,REPLACE(REPLACE(REPLACE(ISNULL(ec.Configuration_Val,e.First_NM + ',' + e.Last_NM),'First_Name',e.First_NM),'Last_Name',e.Last_NM),'Middle_Name',e.Middle_NM) AS Employee_NM
			,lt.Leave_Type_NBR
			,lt.Leave_Type_NM
			,CASE WHEN ((lt.Leave_Type_NM = 'Casual Leave') AND ((DATEADD(mm,lt.Eligible_NBR,e.Join_DT)) < = @current_DT) )--   ((DATEDIFF(mm,e.Join_DT,@current_DT)) >= lt.Eligible_NBR ))
					  THEN CASE WHEN (DATEPART(yy,e.Join_DT) = @Year_NBR ) 
									THEN CASE WHEN DATEPART(dd,e.Join_DT) > 15 
												  THEN ((DATEDIFF(mm,e.Join_DT,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0))
												  ELSE (((DATEDIFF(mm,e.Join_DT,@current_DT)+1) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0))
										 END
									ELSE ((DATEPART(mm,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0))
						   END
			 WHEN ((lt.Leave_Type_NM = 'Sick Leave') AND ((DATEADD(mm,lt.Eligible_NBR,e.Join_DT)) < = @current_DT) )--((DATEDIFF(mm,e.Join_DT,@current_DT)) >= lt.Eligible_NBR ))
					  THEN CASE WHEN (DATEPART(yy,e.Join_DT) = @Year_NBR ) 
									THEN CASE WHEN DATEPART(dd,e.Join_DT) > 15 
												  THEN ((DATEDIFF(mm,e.Join_DT,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0))
												  ELSE (((DATEDIFF(mm,e.Join_DT,@current_DT)+1) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0))
										 END
									ELSE ((DATEPART(mm,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0)) 
						   END
			 WHEN ((lt.Leave_Type_NM = 'Earn Leave') AND ((DATEDIFF(mm,e.Join_DT,@current_DT)) >= lt.Eligible_NBR ) AND ((DATEADD(mm,lt.Eligible_NBR,e.Join_DT)) < = @current_DT) )
					  THEN CASE WHEN (DATEPART(yy,DATEADD(mm,lt.Eligible_NBR,e.Join_DT)) = @Year_NBR ) 
									THEN (lt.Days_Per_Year  - ISNULL(yela.Cnt,0))
									ELSE ((ISNULL(elb.Leave_Balance,0) + lt.Days_Per_Year) - ISNULL(yela.Cnt,0))
						   END
			 WHEN (lt.Leave_Type_NM = 'Compensatory Off') 
					  THEN CASE WHEN (DATEPART(yy,e.Join_DT) = @Year_NBR ) 
									THEN ISNULL(ycoa.Cnt,0) - ISNULL(yela.Cnt,0)
									ELSE ISNULL(elb.Leave_Balance,0) + ISNULL(ycoa.Cnt,0) - ISNULL(yela.Cnt,0)
							END
			ELSE CASE WHEN lt.Leave_Type_NM NOT IN ('Casual Leave','Sick Leave','Earn Leave','Compensatory Off','Marriage Leave','Maternity Leave','Paternal Leave')
				  THEN	CASE WHEN ( ISNULL(lt.Days_Per_Year,0) != 0 AND lt.Carry_Forward_FG = 0 AND ((DATEADD(mm,lt.Eligible_NBR,e.Join_DT)) < = @current_DT) )--((DATEDIFF(mm,e.Join_DT,@current_DT)) >= lt.Eligible_NBR ))
							 THEN CASE WHEN e.Join_DT > lt.Effective_DT
									   THEN CASE WHEN (DATEPART(yy,e.Join_DT) = @Year_NBR ) 
												 THEN CASE WHEN DATEPART(dd,e.Join_DT) > 15 
														   THEN ((DATEDIFF(mm,e.Join_DT,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0))
													       ELSE (((DATEDIFF(mm,e.Join_DT,@current_DT) + 1) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0)) 
												      END
										         ELSE (DATEPART(mm,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0)
									         END
							          ELSE CASE WHEN (DATEPART(yy,lt.Effective_DT) = @Year_NBR ) 
									            THEN CASE WHEN DATEPART(dd,lt.Effective_DT) > 15 
											   		      THEN ((DATEDIFF(mm,lt.Effective_DT,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0))
													      ELSE (((DATEDIFF(mm,lt.Effective_DT,@current_DT) + 1) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0)) 
												      END
										         ELSE (DATEPART(mm,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0)
									       END
						
									END								
							 WHEN ( ISNULL(lt.Days_Per_Year,0) != 0 AND lt.Carry_Forward_FG = 1 AND ((DATEADD(mm,lt.Eligible_NBR,e.Join_DT)) < = @current_DT) )--((DATEDIFF(mm,e.Join_DT,@current_DT)) >= lt.Eligible_NBR ))
							 THEN CASE WHEN e.Join_DT > lt.Effective_DT			
									   THEN CASE WHEN (DATEPART(yy,e.Join_DT) = @Year_NBR ) 
											     THEN CASE WHEN DATEPART(dd,e.Join_DT) > 15 
														   THEN ((DATEDIFF(mm,e.Join_DT,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0))
														   ELSE (((DATEDIFF(mm,e.Join_DT,@current_DT) + 1) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0)) 
													   END
											      ELSE  ISNULL(elb.Leave_Balance,0) + (DATEPART(mm,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0)
										     END
									   ELSE CASE WHEN (DATEPART(yy,lt.Effective_DT) = @Year_NBR ) 
											     THEN CASE WHEN DATEPART(dd,lt.Effective_DT) > 15 
														   THEN ((DATEDIFF(mm,lt.Effective_DT,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0))
														   ELSE (((DATEDIFF(mm,lt.Effective_DT,@current_DT) + 1) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0)) 
													   END
											      ELSE  ISNULL(elb.Leave_Balance,0) + (DATEPART(mm,@current_DT) * (lt.Days_Per_Year/12.0)) - ISNULL(yela.Cnt,0)
										     END
								   END
							 WHEN ( ISNULL(lt.Days_Per_Year,0) = 0 AND lt.Carry_Forward_FG = 0 AND ((DATEADD(mm,lt.Eligible_NBR,e.Join_DT)) < = @current_DT) )--((DATEDIFF(mm,e.Join_DT,@current_DT)) >= lt.Eligible_NBR ))
							 THEN CASE WHEN e.Join_DT > lt.Effective_DT		
									   THEN CASE WHEN (DATEPART(yy,e.Join_DT) = @Year_NBR ) 
											     THEN (lt.Max_At_Time - ISNULL(ela.Cnt,0)) 
											     ELSE 0.00
										     END
									   ELSE CASE WHEN (DATEPART(yy,lt.Effective_DT) = @Year_NBR ) 
											     THEN (lt.Max_At_Time - ISNULL(ela.Cnt,0)) 
											     ELSE 0.00
										     END
								  END

							 WHEN ( ISNULL(lt.Days_Per_Year,0) = 0 AND lt.Carry_Forward_FG = 1 AND ((DATEADD(mm,lt.Eligible_NBR,e.Join_DT)) < = @current_DT) )--((DATEDIFF(mm,e.Join_DT,@current_DT)) >= lt.Eligible_NBR ))
							 THEN CASE WHEN e.Join_DT > lt.Effective_DT		
									   THEN CASE WHEN (DATEPART(yy,e.Join_DT) = @Year_NBR ) 
											     THEN (lt.Max_At_Time - ISNULL(ela.Cnt,0)) 
											     ELSE (ISNULL(elb.Leave_Balance,0) - ISNULL(ela.Cnt,0))
										     END
									   ELSE CASE WHEN (DATEPART(yy,lt.Effective_DT) = @Year_NBR ) 
											     THEN (lt.Max_At_Time - ISNULL(ela.Cnt,0)) 
											     ELSE (ISNULL(elb.Leave_Balance,0) - ISNULL(ela.Cnt,0))
										     END
								  END
					ELSE 0.00 END
					END 
				END Available_Leave
		FROM TEmployee (NOLOCK) e
		LEFT JOIN TEmployee_Info (NOLOCK) ei
			ON e.Employee_NBR = ei.Employee_NBR
		INNER JOIN TLeave_Type (NOLOCK) lt
			ON 1 = 1 AND
				@Current_DT > = lt.Effective_DT
		LEFT JOIN TEmployee_Leave_Balance (NOLOCK) elb
			ON  e.Employee_NBR   = elb.Employee_NBR   AND
			   lt.Leave_Type_NBR = elb.Leave_Type_NBR AND
			   @Year_NBR		 = elb.Year_NBR	
		LEFT JOIN (SELECT ela.Employee_NBR
						 ,ela.Leave_Type_NBR
						 ,DATEPART(yy,ela.From_DT) AS Year_NBR
						 ,SUM(ela.Days_NBR) AS Cnt
					FROM TEmployee_Leave_Application (NOLOCK) ela
					INNER JOIN TLeave_Type (NOLOCK) lt
						ON lt.Leave_Type_NBR = ela.Leave_Type_NBR
					INNER JOIN TStatus (NOLOCK) s
						ON ela.Status_NBR = s.Status_NBR
					WHERE ela.From_DT >= lt.Effective_DT 
--					  AND ISNULL(lt.Days_Per_Year,0) != 0
					  AND s.Status_NM IN ('Applied','Availed','Approved by Supervisor','Approved by HR')
					GROUP BY ela.Employee_NBR,ela.Leave_Type_NBR,DATEPART(yy,ela.From_DT)
				  )yela
			ON  e.Employee_NBR    = yela.Employee_NBR   AND
			   lt.Leave_Type_NBR  = yela.Leave_Type_NBR AND
			   @Year_NBR		  = yela.Year_NBR
		LEFT JOIN (SELECT ela.Employee_NBR
						 ,ela.Leave_Type_NBR
						 ,COUNT(1) AS Number
						 ,SUM(ela.Days_NBR)  AS Cnt
					FROM TEmployee_Leave_Application (NOLOCK) ela
					INNER JOIN TLeave_Type (NOLOCK) lt
						ON lt.Leave_Type_NBR = ela.Leave_Type_NBR
					INNER JOIN TStatus (NOLOCK) s
						ON ela.Status_NBR = s.Status_NBR
					WHERE ela.From_DT >= lt.Effective_DT 
--					  AND ISNULL(lt.Days_Per_Year,0) = 0
					  AND s.Status_NM IN ('Applied','Availed','Approved by Supervisor','Approved by HR')
					GROUP BY ela.Employee_NBR,ela.Leave_Type_NBR
				  )ela
			ON e.Employee_NBR     = ela.Employee_NBR AND
			   lt.Leave_Type_NBR  = ela.Leave_Type_NBR 
		LEFT JOIN (SELECT coa.Employee_NBR
						 ,lt.Leave_Type_NBR
						 ,DATEPART(yy,coa.Applied_DT) AS Year_NBR
						 ,SUM(CASE WHEN Halfday_FG = 1 THEN 0.5 ELSE 1.0 END) AS Cnt
					FROM TCompensation_Off_Application (NOLOCK) coa
					INNER JOIN TLeave_Type (NOLOCK) lt
						ON lt.Leave_Type_NM = 'Compensatory Off'
					INNER JOIN TStatus (NOLOCK) s
						ON coa.Status_NBR = s.Status_NBR
					WHERE coa.Applied_DT >= lt.Effective_DT 
--					  AND ISNULL(lt.Days_Per_Year,0) = 0
					  AND s.Status_NM IN ('Availed','Approved by HR')
					GROUP BY coa.Employee_NBR,lt.Leave_Type_NBR,DATEPART(yy,coa.Applied_DT)
				  )ycoa
			ON e.Employee_NBR    = ycoa.Employee_NBR AND
			   lt.Leave_Type_NBR  = ycoa.Leave_Type_NBR AND
			   @Year_NBR		   = ycoa.Year_NBR
		LEFT JOIN (SELECT cv.Configuration_Val
					FROM TConfiguration (NOLOCK) c
					INNER JOIN TConfiguration_VAlue (NOLOCK) cv
						ON c.Configuration_NBR = cv.Configuration_NBR 
					WHERE c.Configuration_NM = 'Employee Name' 
					  AND c.Active_FG = 0 AND cv.Active_FG = 0
				  )ec
			ON 1=1
		WHERE (e.Employee_NBR = @Employee_NBR OR @Employee_NBR = 0)
		  AND (lt.Leave_Type_NBR = @Leave_Type_NBR OR @Leave_Type_NBR = 0 )
		  AND (lt.Leave_Type_NM != 'Loss of Pay')
		  AND (lt.Active_FG = 0)

	END TRY
	BEGIN CATCH

		DECLARE @ErrorMessage VARCHAR(MAX)
		DECLARE @ProcedureName VARCHAR(100)
		DECLARE @Username VARCHAR (50)
		SET @ErrorMessage = 'Object: ' + Error_Procedure()
							+ 'Error Line Number ' + CONVERT(VARCHAR,ERROR_LINE()) + ': ' + ERROR_MESSAGE()		
		--Write into Error Log table
		EXEC [dbo].[USP_Insert_Error_Log] @ErrorMessage, @Page_NBR, 'LocalServer', @User_NBR
	
		--Return Error Message
		SELECT Message_NBR, Message_Value FROM TError_Message WHERE Message_NBR = 1

	END CATCH
END

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-05-05 : 02:01:19

This procedure calculates the available leaves of an employee at any point of time.

getjagdish
Starting Member

2 Posts

Posted - 2010-05-17 : 02:20:57

SQL Server 2005 has a transaction state: doomed. The doomed transaction is similar to a read-only transaction. You can continue to perform reads after a doomed transaction, but when you commit, write to the transaction log, it will fail with 3930.

Do you rollback any failed transactions before you commit?

Jagdish

getjagdish
Starting Member

2 Posts

Posted - 2010-05-17 : 03:24:47

Check this link.. this helps you better...

http://www.codeproject.com/KB/database/try_catch.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources