Home How to prevent collation affecting table names in SQL syntax?
Reply: 0

How to prevent collation affecting table names in SQL syntax?

user1608
1#
user1608 Published in July 17, 2018, 7:59 am

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?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.332 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO