Home > IT Answers > Development > ADO not receiving errors raised in sub procedure
Palloquin
95 pts.
 ADO not receiving errors raised in sub procedure
ActiveX, ActiveX Data Objects, ADO, ASP, SQL Server 2008, Stored Procedures, VBScript
Hi,
I'v known for a while that ado has it's limits when it comes to dealing with errors... but this one hurts:
I have a StoredProcedure (SP) that calls a sub procedure.
In the sub procedure an error is raised.
The error does not reach ADO for some reason.
After some research I find that if the sub procedure returns a result set before it raises the error this problem occurs, else, the error reaches ADO just fine.
Example code:
main procedure:




CREATE	PROCEDURE sp_test


AS




	EXEC sp_error












sub  procedure:








CREATE PROCEDURE [dbo].[sp_error]


AS


	/* IF THIS LINE IS HERE, ERROR DO NOT SHOW UP IN ADO */


	SELECT 'test' AS value




	RAISERROR ('this is an error', 16, 1)


	RETURN












VBSCript (ASP) code:






dim oConn, oRS


	


Set oConn = Server.CreateObject("ADODB.Connection")


oConn.ConnectionString = ConnString


oConn.CommandTimeout = 180


oConn.Open 




Set oRS = Server.CreateObject("ADODB.recordset")


oRS.activeconnection = oConn


oRS.cursortype = 3


oRS.locktype = 4


oRS.CursorLocation = 3




oRS.source = "EXEC test.dbo.sp_error"


oRS.open




oRS.close


	


oConn.close


	


SET oRS = Nothing


SET oConn = Nothing










Now, I understand that I could work around this, by testing for the error in the main procedure etc... BUT THIS SHOULD JUST WORK!!!




Any thoughts?
						

Software/Hardware used:
 SQL Server 2008, ADO,					
 
					
					
ASKED:  
						August 31, 2010  11:42 AM 
					
 
											
UPDATED:  
							September 16, 2010  11:05 AM 
						
						
									
 
				
 
			
 

		

		
	
			

				
			

		

		
		
		

			

								
RELATED QUESTIONS

				

					
				

							

		

		
		


	

	






 
	

	
 
		
 
			
	
			
 
			
 
			

			
	
		

	
 
	
 


 


	
 
		
 
		
	
			
 
				
 
				
 
					
 
						
 
											
 
							
 
								
Answer Wiki:
 
								
			
									
 
		 
		 
		 
	
		
		
 
			
		
 
	
							
								
 
								

									
 
										Last Wiki Answer Submitted:  
																					Be the first to answer this question.
																			
 
										

											All Answer Wiki Contributors: 
									Be the first to answer this question.										

										
To see all answers submitted to the Answer Wiki: View Answer History.
										
 
								


								
  
								

 
							
															
						


					

				
 
				
 
			
 
		

		
		


	
 






			


	



	







	


	

		
RSS - Discussions Help

		
Discuss This Question:

		
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

		

						

					
					
					
					
				
			











	

	

		


	
	
	
	
	
	
	
	
	

	

 

	
	
	


	
	

		
	


 








				

		

		

	

			
 
			
 
				
 
					
 


	

 
 


	 	
 

 
	
 
		
 
			
 
			 
		
 
		
It seems to be slightly different…


Is I generate any resultsets before the error is raised, ADO does not seem to trap it.


I do not need sub procedures for this, even this code works:


CREATE  PROCEDURE [dbo].[sp_error]
AS
	/* IF THIS LINE IS HERE, ERROR DO NOT SHOW UP IN ADO */
	SELECT 'test' AS value

	RAISERROR ('this is an error', 16, 1)
	RETURN




 
	
	

 
	
	
	
Palloquin
 
	
 95 pts.