4

I'm experiencing an annoying problem with a table named SysMessages in one of our databases. When I run a select statement I don't get the contents of this table, but of the sys.sysmessages view in the master database. A use [DBNAME] doesn't help, nor select * from [DBNAME].dbo.SysMessages.

I'm in the middle of a migration to a newer SQL Server version. In SQL Server 2008 R2 I didn't encounter this problem with this database, but in SQL Server 2016 I'm not able to query my own SysMessages table.

Any help would be greatly appreciated.

5

This is the expected behavior for system compatibility views from SQL Server 2012+:

When referenced in a user database, system tables which were announced as deprecated in SQL Server 2000 (such as syslanguages or syscacheobjects), are now bound to the back-compatibility view in the sys schema. Since the SQL Server 2000 system tables have been deprecated for multiple versions, this change is not considered a breaking change.

Example: If a user creates a user-table called syslanguages in a user-database, in SQL Server 2008, the statement SELECT * from dbo.syslanguages; in that database would return the values from the user table. Beginning in SQL Server 2012, this practice will return data from the system view sys.syslanguages.

You're probably going to have to fix your database to not use the same names as deprecated system tables (now system views in the sys schema).

1

One way you could do this (but you definitely should not do this) is by changing the database collation to be case sensitive. This only works if your table is in fact written as dbo.SysMessages.

Consider this example

CREATE DATABASE TEMP2 

USE TEMP2
CREATE TABLE [dbo].[SysMessages](
    [id] [int] NULL,
    [message] [varchar](255) NULL
) ON [PRIMARY]
GO

ALTER DATABASE TEMP2 COLLATE Latin1_General_CS_AS;

Query with case sensitive collation

SELECT * FROM [dbo].[SysMessages]

Result

id  message

Query with case insensitive collation

ALTER DATABASE TEMP2 COLLATE Latin1_General_CI_AS;
GO
SELECT * FROM [dbo].[SysMessages];

Result

error   severity    dlevel  description msglangid
21  20  0   Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator.    1033
101 15  0   Query not allowed in Waitfor.   1033
102 15  0   Incorrect syntax near '%.*ls'.  1033
...

Again, this is only to make the case that you could, but you definitely should not do this.

Trying to change the collation can give you errors like this one:

The statistics '...' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

I tried some other stuff like synonyms and denying select on the sysmessages dmv to my login but these did not work.

  • 1
    Thank you for the research and I'll definitely remember the don't. :-) – Bruno Piqueur Nov 21 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.