Find Invalid Object not working on SQL Express 2017

TheoLTheoL Posts: 17 Bronze 1
edited January 10, 2020 11:12PM in SQL Prompt
Running latest release (10.0.7.13230) in SSMS 17.9.1.
When using Find Invalid Objects on a database on local SQL Express 2017, it hangs indefinitely showing the progress bar.  Text below progress bar shows "Counting objects …" but get stuck at about 10%
Using "Find Invalid Objects" works fine on a local SQL Server 2016 instance.

Does anyone else have problems using Find invalid objects on SQL Express 2017?

Best Answer

  • TheoLTheoL Posts: 17 Bronze 1
    edited January 14, 2020 10:19AM Answer ✓
    @DanC I have found the cause:
    For performance, I use "Only load suggestions for certain databases/schemas" option.  My freshly installed SQL Express 2017 was not in the list.  The fact it was SQL Express 2017 was not related to the cause.

    It turns out the if the database is not included in the server/database list for connections, then the Find Invalid Objects does not work.

    It would be a sensible UI enhancement to notify the user that this is the case instead of allowing the attempt, then just showing a progress bar that appears to get stuck at about 10%.

Answers

  • Hi @TheoL could you possibly provide me with an example script you're using to find invalid objects on for me to test?

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • TheoLTheoL Posts: 17 Bronze 1
    Hi, @DanC
    I was attempting to use the "Find Invalid Objects" feature of SQL Prompt, by right clicking on a database in the SSMS object explorer, then selecting the "Find invalid objects" menu item.

    It is the resulting "SQL Prompt - Invalid Objects" window that hangs - there is no script involved.
  • Hi @TheoL unfortunately I'm unable to replicate the issue, so I'm using SQL Express 2017 and finding invalid objects on AdventureWorks2017 DB. Are you having issues with finding invalid objects on multiple databases in your problem instance?

    What you can also do is enable verbose logging and perform the action and send us the logs to review and see if that gives us any answers perhaps!

    Also if possible could you provide us with your Prompt settings as perhaps there's something occurring there too. 

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • TheoL said:
    @DanC I have found the cause:
    For performance, I use "Only load suggestions for certain databases/schemas" option.  My freshly installed SQL Express 2017 was not in the list.  The fact it was SQL Express 2017 was not related to the cause.

    It turns out the if the database is not included in the server/database list for connections, then the Find Invalid Objects does not work.

    It would be a sensible UI enhancement to notify the user that this is the case instead of allowing the attempt, then just showing a progress bar that appears to get stuck at about 10%.
    Hey Theo, I've passed all the details onto the development team and they've recognized this as a bug and so they will be reviewing it in due time. 

    Hope this helps!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

Sign In or Register to comment.