How to prevent matching affecting table names in SQL syntax?

Question

Can someone explain how to protect table names from sorting settings? I am 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 matching to be used to sort my data, not the table names themselves.

Background

This situation arose because the client was responsible for installing SQL Server and set up the Danish_Norwegian_CI_AS server, so any database has this sorting by default (and we do not specifically set the database mapping when creating a new database using the / script code ) In this situation, I still did not expect the names of our tables to be interpreted differently. The value of our only option is to force the Latin sort in the database, and can users specify a column mapping if they want something else?

Answer 1

I think SQL Server before executing the query, check or compile it, for example, it checks the correctness of the table as follows:

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

It will not have a result. instead for other modes that return the result.
Because of this, it will rise:

Invalid object name 'Dataarchive'.

But you can check sys.object another COLLATION as follows:

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

This will produce the result, AFAIK, you cannot force the SQL Server DBMS to check it or compile it as follows.

---

BTW, you can get the table data in this case - 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;