Invalid Object Error for Stored Procedures with OPTION (RECOMPILE)

mcole

I have noticed that when finding invalid opbjects , if the stored procedure being scanned has "OPTION (RECOMPILE)" within it, we get an invalid object returned with "A severe error occurred on the current command.  The results, if any, should be discarded."

I assume this is a bug.  Any ideas on a work around or a fix?

Tianjiao_Li

Hi @mcole

I couldn't reproduce it using the attached script in Prompt 9.2.6.6145. Can you upgrade your Prompt if not already? 

If problem persists for you, can you share a script to assist a reproduction?

Thanks.

mcole

Thanks for the reply.

Firstly, I am on the latest version, and i can also say that your script does not reproduce the issue.  I did try playing with the SP to try to replicate one of the SPs in my database, but was unable to get the error to reproduce.

I.m not quite sure where to go here.  My database is rather complex, as are many of the stored procs, so I cannot script enough of it out to enable a reproduction.  All I can say is that after running the invalid objects scan, several Stored Procs are listed with this error, and the only thing that they seem to have in common is the OPTION ( RECOMPILE ).  All other SPs are listed as fine, and all with this option get this error.  It may very well be a combination of things.

I have attached a screen shot of the dialog.  I have also attached a script of the stored proc listed first.  I realise that this will not allow you to duplicate the issue, but I don't seem to be able to duplicate it myself.  If anything jumps out at you as an issue (for SQL Prompt, not in coding style) please let me know.

Tianjiao_Li

Hi @mcole

As you already knew, I couldn't reproduce the problem with your script either. 

Can you send us your Prompt log in case we can find anything useful? I'll reach you by email.