An unexpected null value has been encountered while querying

baschroeder

An unexpected null value has been encountered while querying the database system tables. Please make sure the database is in a consistent state by running DBCC CheckDB. If the problems persist contact support

See this when running a compare between our QA and Production databases. Had the DBA check consistency and no errors.

Also note, this worked before I upgraded to the most recent version of SQL Compare. I am running 7.1.0.197 and I believe it worked when I was on 7.0.

Also happening with my Data Compare for the same database.

baschroeder

I downloaded the SQL Snapper production (nice, btw) and it shows an error on the same database at the point it is Reading Constraints from the database.

Double checked the SQL Compare and it also shows that message when the error occurs.

paulh

Funnily enough I encountered the same issue this morning - I believe it's a permission issue..

Ensure that the user you are using to do the comparison has sufficient permissions, not sure if they need owner or if some subset is enough, but when running with our normal user, the comparison failed in the way you described.

Update: reading another topic there is a permissions FAQ for SQL Compare at http://www.red-gate.com/supportcenter/C ... 000072.htm

baschroeder

Thanks for the response.

Yeah I was doing some other activity on the same server and found some other oddities. We discovered serveral stored procedure suddenly could not be viewed/executed and that all of the constraints (and only the constraints) were suddenly unavailable to be viewed. We found that all of the permissions were missing on these objects.

DBA is at a lost to how this happened, since a month ago I was working with the same objects with no problem. So it is permissions and they will need to at least grant the view definition rights. But scary how no one knows what caused the change in the first place, and it only happened on this one server.

From the other topic: Note that some SQL Server 2005 objects may have the VIEW DEFINITION permissions revoked for a user. It may be necessary to grant VIEW DEFINITION to your user for the database.