Unexpected null value has been encountered

moff

Version 3.9.0.43 Pro

User was getting following error

Unable to refresh or cache database [<SERVER>].[<DB>] due to the following error: 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. If the problem persists, please contact our support..

 

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException: Unable to refresh or cache database [<SERVER>].[<DB>] due to the following error: 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. If the problem persists, please contact our support.. ---> RedGate.SQLCompare.Engine.SqlCompareException: 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. If the problem persists, please contact our support. ---> System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.

   at o.z()

   at o.a(n )

   --- End of inner exception stack trace ---

   at o.a(n )

   at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt(ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects, Boolean includeDependencies)

   at cy.b(IAuthenticationTokenProvider )

   --- End of inner exception stack trace ---

   at cy.b(IAuthenticationTokenProvider )

   at cy.a(IAuthenticationTokenProvider )

   at cy.GetMetaData(IAuthenticationTokenProvider provider)

   at M.b(G , IAuthenticationTokenProvider , Boolean , EventHandler`1 , EventHandler`1 , EventHandler`1 , EventHandler`1 )

Profiler showed SQL Prompt running the following query:

SELECT so.name AS TableName,
sys.schemas.name AS TableOwner, 
cc.name AS ConstraintName,
cc.object_id AS Constraint_id,
cc.is_not_for_replication AS NotForReplication,
cc.is_not_trusted AS WithNoCheck,
cc.is_disabled AS Disabled,
sc.name AS ColumnName,
cc.parent_column_id AS colid, 
cc.definition AS Text
FROM sys.check_constraints cc WITH (NOLOCK) 
INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id=cc.parent_object_id 
LEFT JOIN sys.schemas WITH (NOLOCK) ON sys.schemas.schema_id=so.schema_id
LEFT JOIN sys.columns sc  WITH (NOLOCK) ON sc.column_id=cc.parent_column_id AND sc.object_id=cc.parent_object_id 
ORDER BY so.name, cc.name

The user didn't have permission to view the text of a constraint, so the final column in the query ('Text') returned NULL.

I issued a GRANT VIEW DEFINITION ON SCHEMA::dbo TO [<user>] (as we don't mind them having this right) and SQL Prompt can now cache the DB.

Per the error message, please can SQL Prompt be fixed so that it isn't surprised if this particular column returns NULL?

Brian Donahue

Thanks for your post. We are aware that the error handling in Prompt isn't complete when the system tables can't be read and are working to improve it. In the meantime, we have information available in our knowledge base in case users encounter the same problem.

Thanks for pointing this out!