Hi,

I have an encrypted procedure, that is used inside my 'own' complex procedure as:

Begin try
Begin tran
Do a lot
insert into #tmp exec xp_encryptedSP
Do a lot more
commit tran
end try
begin catch
print ERROR_MESSAGE()
rollback tran
end catch

Some error occur on the insert into #tmp exec xp_encryptedSP row, and in this case it's correct that the error occur, but:
If I run only exec xp_encryptedSP in query analyzer, I can see the root cause error message,
but in my complex procedure all I get is:

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I have tried to enclose the insert into #tmp exec xp_encryptedSP in an own try block, but all I get is the message above.

Please help
/

>I have an encrypted procedure, that is used inside my 'own' complex procedure as:

Hi Magnus,

Can you just not use encrypted sp?

It can be decrypted easily just Google/Bing for it, so it provides only marginal protection.

Hi,

Thanks for your reply.
I'm not sure my supplier will support me on decrypting their sp.

Then I don't think the problem is that it's encrypted (correct me if I'm wrong). It's probably that it is nested somehow. As mentioned I can see the error when running it the sp itself, but I want my end users to be able to see the error, and that is provided by my 'own' sp, and using that I cannot get the root cause error message.

BR /Magnus

Do you get the same error if you only try

insert into #tmp exec myEncryptProc?

--------------

Also, just to verify if the problem is indeed in the fact the procedure is encrypted, I would try a non-encrypted version of this SP. I suspect that the problem is not in the fact that the procedure is encrypted, but you need to test this assumption.

Hi,

There is not something wrong with the code itself (99 times of 100 it runs), the problem is that if my supplier's sp returns an error (for instance if a business validation fails), the message I get is:

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

Instead of the root cause (e.g. the booking period is not open).

If I run just exec xp_encryptedSP, the error message will become the booking period is not open (example), but I want to be able to use my 'own' SP to run the supplier's SP, and to show the error message to the user.

BR /Magnus

I understand that somehow that error is "eaten".

The question is - do you get that same error if you 

use 

INSERT INTO #tmp EXEC ...

E.g. I suspect it's the INSERT part of the exec command that "eats" the actual error.

Can you test to confirm or deny this theory?

>I'm not sure my supplier will support me on decrypting their sp.

I would not buy 3rd party software with encrypted sp. You put your job on the line. What if something wrong and you cannot fix it quickly due to encryption?

BTW, I am not sure how to work around this problem.

Suppose, the SP is written this way:

if SomeCondition

    RAISERROR ...

    return

SELECT ...

--------------

If that condition is true, your insert statement is supposed to fall.

I would not buy 3rd party software with encrypted sp. You put your job on the line. What if something wrong and you cannot fix it quickly due to encryption?

Generally, if you have 3rd party software you are limited by the license agreement of what you can do and not. The obfuscation serves the same purpose as fence around your house: do not trespass here. Honest persons will respected. Less honest will not.

No, the error message has nothing to do with the fact that the procedure obfuscated. It is a limitation of INSERT-EXEC. A quite silly one, since a consequence of the error is that the transaction is rolled back...

There are better ways to share data between stored procedures, but most of them assumes that you can change the procedure you call, and obviously this is not possible since this is a vendor procedure.

So it looks like you are in for heavy artillery, that is the CLR. See here for an example:
http://www.sommarskog.se/share_data.html#CLR

I should warn you in advance that error handling when the CLR is involved is also a very difficult thing.

Thank you all, and Erland for interesting reading.

I guess I have to leave the procedures as they are, and accept that the end user cannot get the correct error message, giving me some more to do. We will upgrade the system later this year, when I can force the vendor to give us a better experience.

BR /Magnus