Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Setting return status from CLR procedure Expand / Collapse
Author
Message
Posted Thursday, July 16, 2009 6:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 17, 2012 4:21 PM
Points: 74, Visits: 316
I have a CLR C# DLL. It contains a try/catch loop. I am using the SqlContext.Pipe method to pipe the error messages back to the client. The messages are trapped and returned, but the status of the procedure always returns a 0. What can be done such that the CLR procedure will return a status of 1 or non-zero in case of error?

Kevin McGinn
Post #754570
Posted Thursday, July 16, 2009 9:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
Instead of defining it as a sub define it as a function with a return value of sqlint (I think thats how I did it)..

I did it in VB though..

CEWII
Post #754585
Posted Friday, July 17, 2009 10:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 17, 2012 4:21 PM
Points: 74, Visits: 316
Thankyou for the suggestion. I have only written CLR procedures in the past. I was of the impression that a CLR function would not allow file system I/O which is the fundamental purpose of the CLR. If I am incorrect and a Function CLR does in fact allow file system I/O then I will convert over to a Function type.
Post #754974
Posted Sunday, July 19, 2009 9:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
If it is added to SQL as a SAFE SQLCLR then it won't be able to, but if it is EXTERNAL_ACCESS or UNSAFE then it can.

CEWII
Post #755455
Posted Monday, July 20, 2009 10:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 17, 2012 4:21 PM
Points: 74, Visits: 316
The Function solution does work; thank you. However, the stored procedure approach, when implemented correctly, also will return the correct status. I realized that I had the CLR procedure defined as a 'public static void'. I changed that to 'public static int' and returned 1 or 0 depending upon whether errors were encountered and the status is properly returned.
Post #755975
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse