Solved

Since changing collation, MSSQL now requires case sensitive statements

Posted on 2016-08-10
29
59 Views
Last Modified: 2016-08-16
We have a SQL 2014 instance that is dedicated to one application database.  While upgrading the application we learned there were several collation conflicts, causing the upgrade to fail.

SQL instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  Latin1_General_CI_AS
  • 800 columns:  SQL_Latin1_General_CP1_CS_AS
  • 200 columns:  Latin1_General_CI_AS

We took the path of least resistance and corrected collation on the SQL instance, database, and 200 columns to match the collation of the 800 columns in our database - everything is now SQL_Latin1_General_CP1_CS_AS.  Performance is noticeably better and the application upgrade succeeded.  

However, now when running SQL statements against the database, objects must be referenced with case sensitivity.  I knew this would happen because we're using a case-sensitive collation, but I didn't realize the inconvenience.  

None of my reports are working unless I convert all tables/columns in my query to the proper case.  We have a lot of reports that are completely lower-case and they no longer run.

Given our current scenario, is there anything we can do so SQL accepts our lower-case statements?  Is it because we changed collation of the SQL instance or was it collation of the database that caused this behavior?

Thanks for your expertise!
0
Comment
Question by:bmsande
  • 12
  • 7
  • 6
  • +2
29 Comments
 
LVL 68

Accepted Solution

by:
Qlemo earned 250 total points
ID: 41750792
The collation of the database determines the treatment of its metadata. SQL Server instance collation determines treatment of database names, SQL logins aso (not specific to a database).

If you change the DB collation, nothing changes inside of the tables, but the metadata of course. New varchar columns are using the new collation by default.

So your choices are
  • reset the DB collation, and make sure varchar columns are getting the correct collation different from the db one.
  • rename all objects in your DB to fit to the case you are using, and keep that exact notation everywhere
  • change usage to proper case.
0
 

Author Comment

by:bmsande
ID: 41750890
@Qlemo, thanks for your response.  Sounds like changing our reports to use proper case is the best option for us.

Is there a tool that I can ingest my SQL statements into and it will fix the case sensitivity?  Didn't know if I can automate this with SQL Mgmt Studio somehow?  We have tons of reports that are all lowercase and need to be corrected.  

Thanks!
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41750996
To state the obvious CS and CI abbreviate case sensitive vs case insensitive, so you could have seen what you did before.

The easiest option would be to revert to the CI collation, wouldn't it?

Bye, Olaf.
1
 
LVL 68

Expert Comment

by:Qlemo
ID: 41751031
Sorry, I don't know of such a tool. Text literals have to stay as-is, or be converted if used as a key. The app can't decide that,  just to show one issue.
1
 

Author Comment

by:bmsande
ID: 41751046
@Olaf, if we revert the 200 columns to CI collation, then we would continue to face performance problems.  Or are you saying revert the SQL instance to CI collation?  

I thought tempdb and the application database should have the same collation for best performance - is this true?
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 125 total points
ID: 41751058
Next to every setting or comparing expression you can add the collation to be used:

Select * from table where column=@variable COLLATE SQL_Latin1_General_CP1_CI_AS

select case when column = 'abcd' COLLATE SQL_Latin1_General_CP1_CI_AS then something else something_else end as val from table where column='xyz' COLLATE SQL_Latin1_General_CP1_CI_AS

obviously you will use the CI , case insensitive, collation in order to ignore the cases.
0
 

Author Comment

by:bmsande
ID: 41751077
Would this config work?  Or is this against best practice?  I'm not a dba so I don't know but I was thinking, was if you have several database's on a SQL instance - must all db's have the same collation as the SQL instance?

SQL instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  SQL_Latin1_General_CP1_CS_AS
ALL columns:  SQL_Latin1_General_CP1_CS_AS
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41751086
The problem is at the server level. All system databases will have the server collation, including tempdb. SO if you have a database of different collation even operations with temporary tables could fail because when you create a temp table it will use by default the tempdb collation, which in turn was created with the server collation determined at the installation moment. You can NOT change a server collation but you could change a database collation.:

http://sqlmag.com/database-performance-tuning/seven-step-process-changing-database-collation
0
 

Author Comment

by:bmsande
ID: 41751171
@Zberteoc, I followed the suggested article to align the database collation across instance>database>tables>objects.  Everything is now the same collation.  It took a lot of work to arrive at this point and everything is working great, but we'd like to be able to use lower-case syntax in our SQL statements.  We have hundreds of reports that would need to be corrected with case sensitivity.  

Sounds like we must stick to the collation we landed on because going through and changing collation within the database could take months.  Arriving at this point took several weeks alone.  It's an enormous db which is why we corrected the 200 columns to match the other 800 columns - path of least resistance.  Case-sensitive sql queries stink, though.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41751179
You make me wonder, why this is an issue at all, because if this is some standard software and upgrades fails, it'd be an issue to solve by the vendor, if it's a self-written application then you make me wonder about how you mastered this so far.

I see you have better performance now, but a working application is more important than a fast and not working application, isn't it? You came from somewhere and changed collations, you can change all that back. As Qlemo said:
If you change the DB collation, nothing changes inside of the tables

I don't know how much you fiddled with everything, but it should be possible to revert maybe even while still keeping the new version of the software, then tackle that problem without the pressure of keeping things operative.

Such staging of a new upgrade should rather be done sandboxed in a test staging area.

Bye, Olaf.
0
 

Author Comment

by:bmsande
ID: 41751211
We are in a test environment.  Performance and a working application are equally important.  This is standard software that the vendor would not support because collation conflicts were too numerous.  The customer does not have a DBA and everyone in IT has access to SQL.  Too many hands in the cookie jar and not enough database expertise.

Since aligning collation across the instance>database>objects, the app upgraded successfully and the performance gains are extremely noticeable.

I was hoping there was a way to preserve our legacy lower-case reports but that does not seem to be possible unless we run with mixed collation.  We don't want to sacrifice the perf gains that we've uncovered.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41751948
I really can't tell about the effects on tempdb and having different collations. But as already has been said, if you only change the DB collation back, content will not change, but you get case-insensitive metadata usage back. You need to care about new varchar columns getting the proper (non-db) collation, but that is all I know of as a con.
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41752139
If you want to leave it case-sensitive, perhaps Zberteoc's comment about adding to collation to the end of the affected queries is the best way to go.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41752724
If I understood correctly, the SQLs to benefit from the collation boost are inside of an application the OP has no control about.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 41752769
Please explain this, an example would help:
but we'd like to be able to use lower-case syntax in our SQL statements.
0
 

Author Comment

by:bmsande
ID: 41756547
Sorry for the wall of text.  Any and all expertise is greatly appreciated!

@Zberteoc, we have hundreds of reports that were developed with lower-case syntax.  Lazy man's approach, yes.  We can not add collation to the end of the queries as some of these are internal to the application and can not be modified.  

Here's a small example of a common report we run:

SELECT dt.name, dv.datasize, ki.inivalue, pd.providerdata
FROM dversdata dv, dtree dt, providerdata pd, kini ki
WHERE dv.docid=dt.dataid
AND dv.providerid=pd.providerid

Open in new window


The statement above is no longer functional since resolving collation conflicts.  Corrected statement:

SELECT dt.Name, dv.DataSize, ki.IniValue, pd.ProviderData
FROM DVersData dv, DTree dt, ProviderData pd, KIni ki
WHERE dv.DocID=dt.DataID
AND dv.ProviderID=pd.ProviderID

Open in new window


RECAP:
We learned of collation conflicts during our application upgrade because it kept failing with collation errors.  We uncovered the following:

SQL 2008 instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  Latin1_General_CI_AS
  • 800 columns:  SQL_Latin1_General_CP1_CS_AS
  • 200 columns:  Latin1_General_CI_AS

We corrected collation conflicts with the following high level process:
1. Change database collation
2. For columns to be altered, determine dependencies and relationships
3. Drop column dependencies/relationships (objects)
4. Alter column collation
5. Recreate dropped objects
6. Backup database
7. Uninstall/reinstall SQL with desired collation
8. Import database

We changed everything to SQL_Latin1_General_CP1_CS_AS because a majority of the database objects were already confg'd as such, and we have limited time in our project.  Current config:

SQL 2008 instance:  SQL_Latin1_General_CP1_CS_AS
Application database:  SQL_Latin1_General_CP1_CS_AS
  • 1000 columns:  SQL_Latin1_General_CP1_CS_AS

SUMMARY:
Pro: The application upgrade completed successfully!
Con: SQL queries/reports must be case sensitive,  Existing lower-case reports are no longer functional.

QUESTIONS:
1.  If I reinstall MSSQL with SQL_Latin1_General_CP1_CI_AS, will lower-case queries be acceptable again?  Or is it the database collation that enforces case-sensitive queries?

2. Are there any available resources that state tempdb must be confg'd with the same collation as the application database?

Thanks again, everyone!
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41756617
Again: Server (instance) collation determines names above the DB level, but including the DB names.
DB collation determines the meta-data and default data collation.

So no, reinstalling MSSQL does not help.
0
 

Author Comment

by:bmsande
ID: 41756650
I think I understand......

Prior to our changes, lower case (case-insensitive) statements ran successfully because the database collation was CI... it had nothing to with the SQL instance collation - correct?

Collation prior to corrections:
SQL 2008 instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  Latin1_General_CI_AS
  • 800 columns:  SQL_Latin1_General_CP1_CS_AS
  • 200 columns:  Latin1_General_CI_AS

Do you see any problems with the collation scenario below?  It's similar to the mess we were originally in, which is going on 8+ years with 5k daily users hitting the application -- and no issues to report during that time.

SQL 2008 instance:  SQL_Latin1_General_CP1_CS_AS
Application database:  SQL_Latin1_General_CP1_CI_AS
  • 1000 columns:  SQL_Latin1_General_CP1_CS_AS
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41756659
I don't like the SQL_ collations, and always use Latin1_General_CI_AS. But your suggestion should work fine.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756661
statements ran successfully because the database collation was CI... it had nothing to with the SQL instance collation - correct?

I would say no.

If a name like [inivalue] now only works as [IniValue], then this only has to do with master DB (more precisly it contains the INFORMATION_SCHEMA views but anyway system collation makes names case sensitive or not), with the collation applied to it, as it contains the names of any database. So it has to do with the server collation.

Bye, Olaf.
0
 

Author Comment

by:bmsande
ID: 41756731
@Olaf,
What if I change the collation of the master DB?  Is the following scenario possible.... would lower-case syntax be accepted when querying the application DB?

  • SQL 2008 instance:
  •  SQL_Latin1_General_CP1_CI_AS

  • master, msdb, tempdb, model:
  •  SQL_Latin1_General_CP1_CI_AS

  • Application database and ALL columns within:
  • SQL_Latin1_General_CP1_CS_AS
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756941
Yes, that should work. You may try it this way: https://msdn.microsoft.com/en-us/library/dd207003(v=sql.105).aspx

System databases must be rebuilt to ... or to modify the default server-level collation

Bye, Olaf.
0
 

Author Comment

by:bmsande
ID: 41756946
Good to know that collation scenario should be okay to run with.  I'm in a test environment so reinstalling SQL is easier than making any changes.  After I reinstall with SQL_Latin1_General_CP1_CI_AS collation, I will migrate the database with SQL_Latin1_General_CP1_CS_AS collation.  Hopefully case sensitivity is not an issue for my SQL queries. Finding out soon!
0
 

Author Comment

by:bmsande
ID: 41757129
Ok, I reinstalled SQL 2014 with collation SQL_Latin1_General_CP1_CI_AS

I migrated my application database to the SQL instance, DB name is contoso.

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) as "SQL Instance"
SELECT DATABASEPROPERTYEX('master', 'Collation') as "master"
SELECT DATABASEPROPERTYEX('model', 'Collation') as "model"
SELECT DATABASEPROPERTYEX('msdb', 'Collation') as "msdb"
SELECT DATABASEPROPERTYEX('tempdb', 'Collation') as "tempdb"
SELECT DATABASEPROPERTYEX('contoso', 'Collation') as "contoso"

Open in new window


Results:
SQL Instance:  SQL_Latin1_General_CP1_CI_AS
master:  SQL_Latin1_General_CP1_CI_AS
model:  SQL_Latin1_General_CP1_CI_AS
msdb:  SQL_Latin1_General_CP1_CI_AS
tempdb:  SQL_Latin1_General_CP1_CI_AS
contoso:  SQL_Latin1_General_CP1_CS_AS

I have a table in contoso named [KIni].  

select * from kini - FAILS
select * from KIni - WORKS

From our dialog I was under the impression that using case-insensitivity on my system DB's would allow for case-insensitive queries against my application DB.

Any idea why it's not working... or is it not supposed to work?

I keep harping on this because case-insensitive queries used to work with the following collation:

SQL 2008 instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  Latin1_General_CI_AS
  • 800 columns:  SQL_Latin1_General_CP1_CS_AS
  • 200 columns:  Latin1_General_CI_AS

This leads me to believe the database collation property is what allowed case-insensitive queries, and not the SQL instance or system DB's.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41757427
That is where Olaf and I gave contradicting info. You proofed that I'm correct.
0
 

Author Comment

by:bmsande
ID: 41757992
::sigh:: well I guess I learned something, and you were right @Qlemo.  

I just changed collation at the database level;

/****** Set single-user mode & change collation ******/
USE contoso;
GO
ALTER DATABASE contoso SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE contoso COLLATE SQL_Latin1_General_CP1_CI_AS;

/****** Add users again ******/
ALTER DATABASE contoso SET MULTI_USER
GO

Open in new window


Now my lowercase sql queries are accepted.  But all objects within the database are _CP1_CS_AS so I'm back to a collation conflict.  At least the collation corrections got us through the application upgrade, but I hate putting us back to a collation conflict.

Current config which accepts lowercase queries as desired:

SQL instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  SQL_Latin1_General_CP1_CI_AS
ALL database objects:  SQL_Latin1_General_CP1_CS_AS

With the current config anything new added to the db will receive CP1_CI_AS collation, which conflicts with the objects in the db.  Now I have a new mess on my hands :)
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 125 total points
ID: 41758137
If you changed the same way, that's not how you do it. You merely put another label on the jar. But if you label a peanut butter jar to be honey, it still contains peanut butter.
It seems you did and now reversed that and so things work again, as they should.

I think I don't follow you on the problems you face adding new objects to the db. Maybe you didn't yet understood the context. If you want your data to be case insensitive, that's fine, that means your data is case insnsiive, nothing speaks against that, a query for name='smith' will find the same data as name='Smith'. Case sensitivity requires exact match, and that's also true for names of tables and columns, because such names also are just data, data of the system master db, not of your user/application db. So I don't see a problem.

Bye, Olaf.
0
 

Author Comment

by:bmsande
ID: 41758155
@Olaf not sure what you mean.  I created a new mess but it satisfies my current objective and will revisit later with more time.  What do your mean that's not how you do it ?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41758195
Well, if you really want to change collation, that requires more than just ALTER DATABASE ... COLLATE ...
that does not change the collation of already existing data, therefore it's just relabelling a jar, it doesn't change content.

Bye, Olaf.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now