New features added to SQL Prompt Watch now.

Invalid Object Error for Stored Procedures with OPTION (RECOMPILE)

mcolemcole Posts: 3 New member
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?
Tagged:

Answers

  • 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.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • mcolemcole Posts: 3 New member
    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.




  • 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.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.