I have an extended stored proc which uses sqlexecdirect to execute about 4 lines of sql including a call to sp_addrolemember.
The problem I am getting is that if the called code fails and calls Raiserror ("msg",16,1), it is still passed back to the calling XP as SQL_SUCCESS_WITH_INFO and not SQL_ERROR as I expected.
As far as I can see, any raiserror with a severity above 10 should be returned as an error.
Also, any suggestions as to how error handling can be added around the call to sp_addrolemember ? It uses Raiserror("msg",-1,-1) for every msg ie errors and success. There is a return status but Im not sure if this is reliable once the raiserror has been called.