New training course: Customizing SQL Prompt. Watch now.

All the sudden, snippets don't work. Message is PE001. Anyone seen this

I can't figure out what just happened. I can no longer run my snippets. I get a message PE001.

Answers

  • rdauncerdaunce Posts: 5 New member
    PE001 matches a SQL Prompt Code Analysis rule - Schema name for procedure is not specified

    I haven't seen a Code Analysis rule prevent execution of anything, though.  It normally just displays a warning.  There may be a setting that I'm not familiar with that can prevent a query from running if there is a Code Analysis issue.  Does the PE001 message in the Messages window as an error after trying to run the query or is it showing in a different way (tooltip, pop-up, etc.)?
  • challmanchallman Posts: 4 New member
    Hi rdaunce, I finally figured it out. But I couldn't figure out how to post back to this ticket.
    You may want to put this in your documentation. I did recall seeing a message box this morning saying something about the sql prompt cache being slow but really didn't pay much attention to it.
    But apparently, that was the culprit.

    Long story, short running dbcc freeproccache fixed whatever the problem was.
    Thanks for getting back to me. I'm all good now.
  • rdauncerdaunce Posts: 5 New member
    I'm just another forum user, so I can't update anything.  I'm sure someone at Red Gate reviews these, though.

    I haven't seen the SQL Prompt slow cache message recently, but I know what you are talking about.  SQL Prompt runs queries behind the scenes that query database schema and metadata.  The results of these queries are cached on your local computer and SQL Prompt uses that local cache when it needs any of that information.  If those initial queries are taking longer than expected then the error message is displayed.  If you were seeing that message prior to your issue then that might be a symptom of resource utilization or blocking issues on the SQL Server instance.  If your issue was resolved with the command you ran then it was likely caused by a bad (resource intensive) plan in plan cache that SQL Server maintains and stores on the SQL Server instance.  This is a different cache and is not related to the SQL Prompt cache on your local computer.

    The command you ran is not specific to SQL Prompt or Red Gate - it's a SQL Server command.  If you run it with no parameters that target a specific cached object then it will remove everything from the plan cache on the SQL Server instance you are connected to.  It should be used with caution, especially it in a production environment, unless you understand the what it does and the potential issues it might cause.  The best way to handle your situation would have been to identify the bad plan(s) and only remove the specific plan from the cache.
  • challmanchallman Posts: 4 New member
    Much appreciate you explanation. Makes a lot of sense. Thank you for the heads up.
Sign In or Register to comment.