SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I thought my database was collation agnostic


I thought my database was collation agnostic

Author
Message
tromeo
tromeo
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 75
Comments posted to this topic are about the item I thought my database was collation agnostic

Tom Romeo
Practical Compliance Solutions Corp.
One Orient Way - Suite F156.
Rutherford, NJ 07070-2524
--------------------------------
www.complianceabc.com
email: tromeo@complianceabc.com
Phone: 201-728-8809
samot-dwarf
samot-dwarf
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 145
I doubt, that your real solution (specifying an explicit collation string at each char field) is really a real solution, because it is very error prone - you have to find EVERY code line with #temp tables (+ maybe @table-vars??? + every cross db access + access to some sys table/view joins as to sys.server_principals) and change it. Futuremore you have to rembember this every time you (or your new developer) writes a new procedure.

Personally I would have used my script to create the drop / create statements for the FKs, dropped them, changed collation and recreated the FKs. Should be much lesser work and is a one time action that solves the problem for the future.
Toreador
Toreador
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9978 Visits: 8535
samot-dwarf (1/14/2016)
I doubt, that your real solution (specifying an explicit collation string at each char field) is really a real solution, because it is very error prone - you have to find EVERY code line with #temp tables


It's what we do, and very rarely have problems. It's just one more thing we need to remember when coding, and if we forget it's almost always picked up (by code reviews or QA) before it hits production.
Dscheypie
Dscheypie
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 272
Hi, tromeo,

that was an interesting article for me: Up to now I did not know anything about the abbreviations "CI" and "AS". Of course I did not have too many problems with collations up to now, but now I am a bit more aware of possible complications.

Thank you!

JP

________________________________________________________
If you set out to do something, something else must be done first.
RWillsie
RWillsie
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 14
OK, I will now show the level of my ignorance. (someone has to do it.)

My first thought was "Ah, the characters 'CP1' exist in one instance and not in the other."

So, I looked carefully for some explanation of what 'CP1' stood for. If it is in the article I missed it despite re-reading several times.

Apparently completely unimportant as far as the article is concerned, but just what does the 'CP1' stand for?
tromeo
tromeo
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 75
I didn't supply an explicit collation string, the string"COLLATE DATABASE_DEFAULT", tells the temp table to use the default collation of the database that was restored no matter what the collation of the SQL server.

If the stored procedure had been written that way, I would never need to run your script at all. It would run naturally on any database server that is configured with any collation.

The actual application is sold world wide so I can receive a backup from any where in the world any collation. I recently received a database from South America. Keeping their database in their collation was important when trouble shooting.

I would rather diagnose the problem that the customer is experiencing as close to their native implementation as possible.

Thanks for your post. I'm glad people actually read it.

Actually a very recent post about scalar functions and their performance became the topic of a recent meeting here.

All good stuff

Tom Romeo
Practical Compliance Solutions Corp.
One Orient Way - Suite F156.
Rutherford, NJ 07070-2524
--------------------------------
www.complianceabc.com
email: tromeo@complianceabc.com
Phone: 201-728-8809
tromeo
tromeo
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 75
Sorry - it actually means "code page 1"

SQL_Latin1_General_CP1_CI_AS:- Latin1-General, case-insensitive,
accent-sensitive, kanatype-insensitive, width-insensitive for Unicode
Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

I never dreamed it could get this complicated.

Tom Romeo
Practical Compliance Solutions Corp.
One Orient Way - Suite F156.
Rutherford, NJ 07070-2524
--------------------------------
www.complianceabc.com
email: tromeo@complianceabc.com
Phone: 201-728-8809
jim.riedemann
jim.riedemann
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 203
sorry, double post.
jim.riedemann
jim.riedemann
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 203
samot-dwarf (1/14/2016)
I doubt, that your real solution (specifying an explicit collation string at each char field) is really a real solution, because it is very error prone - you have to find EVERY code line with #temp tables (+ maybe @table-vars??? + every cross db access + access to some sys table/view joins as to sys.server_principals) and change it. Futuremore you have to rembember this every time you (or your new developer) writes a new procedure.

Personally I would have used my script to create the drop / create statements for the FKs, dropped them, changed collation and recreated the FKs. Should be much lesser work and is a one time action that solves the problem for the future.


The solution is very real and works just fine. Been there, done that. 1000+stored procedures and functions across 3 active version branches. Adding the COLLATION DATABASE_DEFAULT to temp tables and table vars was easy, and maintaining/remembering it is a piece of cake. New developers seem to pick it up quickly, also

As for your solution, just dropping the FKs, changing the DB collation and recreating the FKs won't solve the problem.

The collation is stored with each and every string-based column (varchar, nvarchar, etc.) in every table. In order to truly change the collation of the database, you must also change the collation on each string column. In addition to dropping FKs, you must also drop any indexes, statistics, computed columns and check constraints that refer to the column in question. And, since you can only alter one column at a time, it would probably be easier to rename the table, create a new one, copy the data, and drop the old one.
tromeo
tromeo
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 75
yes you are correct

I actually devised a script to change the collation of every object, but then realized that the other constraints were blocking me.

It would have been a huge task

here are some of the SQL statements I attempted - Oh and I also failed with respect to nvarchar(max) - but that is a different story

Select convert(sysname, serverproperty(N'collation'))

select database_id, name from sys.databases


SET NOCOUNT ON
/*Find database default*/
DECLARE @name sysname,
@Collate VARCHAR(50)
SET @name =DB_NAME(7) -- The DataBase Number that you intend to use.
print @name


--SET @Collate =(SELECT CONVERT (sysname, DATABASEPROPERTYEX (@name,'Collation')))
SET @Collate =(Select convert(sysname, serverproperty(N'collation')))

print @Collate

SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+ ') ' + 'COLLATE ' + @Collate +
CASE IS_NULLABLE
WHEN 'NO' THEN' NOT NULL'
WHEN 'YES' THEN' NULL'
END
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME <>'dtproperties'
AND COLLATION_NAME NOT LIKE @Collate
ORDER BY COLUMN_NAME

Tom Romeo
Practical Compliance Solutions Corp.
One Orient Way - Suite F156.
Rutherford, NJ 07070-2524
--------------------------------
www.complianceabc.com
email: tromeo@complianceabc.com
Phone: 201-728-8809
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search