How to prevent collation affecting table names in SQL syntax?

Question

Can someone explain how to protect table names from being subject to the collation settings? I'm currently getting the error message:

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Dataarchive'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'MyDatabase.dbo.Dataarchive'.

From this SQL:

USE master;  
CREATE DATABASE MyDatabase COLLATE Danish_Norwegian_CI_AS;
GO

USE MyDatabase
CREATE TABLE DataArchive (id INT);
GO

SELECT * FROM DataArchive; -- succeeds
SELECT * FROM dataArcHIVE; -- succeeds
SELECT * FROM [MyDatabase].[dbo].[DataArchive]; -- succeeds
GO
SELECT * FROM Dataarchive; -- fails - interprets aa as special A character.
GO
SELECT * FROM [MyDatabase].[dbo].[Dataarchive]; -- fails
GO

USE MASTER;
DROP DATABASE MyDatabase;
GO

I expected collation to apply to sorting my data, not to table names themselves.

Background

This situation has arisen because the customer was responsible for installing the SQL Server, and set the server collation to Danish_Norwegian_CI_AS, thus any database, by default has this collation (and we do not specifically set the collation of the Database when creating a new database through code/script). In this situation, I still did not expect that our table names would be interpreted differently at all. Meaning our only option is to force latin collation on the database and users can specify per-column collation if they want something different?

Answer 1

I think SQL Server before execute a query, check or compile it, for example it checks for table validation like this:

select *
from sys.objects
where name = N'Dataarchive'

That will have no result. instead for other modes that will return a result.
Because of this it will raise:

Invalid object name 'Dataarchive'.

But you can check over sys.object with another COLLATION like this:

select *
from sys.objects
where name COLLATE latin1_General_CI_AI = N'Dataarchive'

That will have a result, AFAIK, You can't force SQL Server DBMS to do its check or compile like this.

---

BTW, You can get data of a table in that case - with using Dynamic SQL - like this:

declare @tablename nvarchar(255) = 'Dataarchive';
declare @sql nvarchar(255) = 
    N'SELECT * FROM '+ (
        select name 
        from sys.tables 
        where name = @tablename COLLATE Latin1_General_CI_AI);
exec sp_sqlexec @sql;