When caching a database in SQL Prompt, you may be presented with one of the following errors - 

"Unable to cast object of type System.dbnull to type System.String."

"An unexpected null value has been encountered while querying the database system tables. Please make sure that the database is in a consistent state by running DBCC CheckDB."

Possible causes

Often these errors are reported because SQL Prompt is unable to retrieve certain metadata from the server using the account that SQL Prompt is logged in as. 

How to fix

 You will need to first check the account permissions and make sure that the logon account meets the minimum requirements. Here is an article outlining the account permissions needed:

 Viewing encrypted objects

 If the account is not the problem, then there may be an issue with the underlying schema. It is possible that there is some corruption or misconfiguration of an object or meta-data that SQL Prompt doesn't know how to handle. For example, we've seen this happen before when certain objects have been found to be without an owner. You may be able to find out more information if you use SQL Compare and compare the problem database with a non problematic database (SQL Prompt uses the SQL Compare engine to register the database). Hopefully SQL Compare should give you a better error message or point you towards the specific object that is causing the problem.

If SQL Compare doesn't present you with any extra information regarding the cause of the error, then you can do some further troubleshooting.

1. Start a default SQL Server Profiler Trace (http://msdn.microsoft.com/en-us/library/ms187929(SQL.90).aspx) on the server that is hosting the problem database.
2. Re-run the SQL Compare comparison to reproduce the error.
3. Stop the SQL Server Profiler trace and look through the results to find the last query run by the application "Red Gate Software - SQL Tools".
4. Copy this query into a new Management Studio query window opened against the original database, and execute it.
5. Have a look through the returned results for NULLs that exist in fields that shouldn't allow them. This should, hopefully, allow you to track down the object that is causing the issue. T
6. he results can often be hard to analyze and knowing where NULLs are allowed and where they're not sometimes comes down to experience. If you do require assistance analyzing the data then if you could send the query that you ran and your results set to support@red-gate.com, we'll look into it for you.