January 30, 2018 at 7:31 am
I am updating an existing SP to point to a different database created outside of my control. Nearly every join in the code brings back a collation conflict error so I have changed the joins to COLLATE DATABASE_DEFAULT but now the code is running ridiculously slow and filling up TEMPDB - can you suggest any other way of resolving the conflicts?
January 30, 2018 at 8:05 am
it actually performs the collation translation, and materializes that data in tempdb, so .... yes.
The only decent way around it, is to create a new db and its object in the "common" collation and convert the data. Then reconnect your sp.
Contact the one who created the db outside of your control and as WHY !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 30, 2018 at 8:09 am
Several years ago I found a package on Codeplex, or somewhere like that, that allows you to change the collation of every single character column in the database. It handled foreign keys and indexes and things like that as well. I can't remember where the code came from now, but it may be worth your while searching for it.
John
February 5, 2018 at 1:40 am
carolyn.foster - Tuesday, January 30, 2018 7:31 AMI am updating an existing SP to point to a different database created outside of my control. Nearly every join in the code brings back a collation conflict error so I have changed the joins to COLLATE DATABASE_DEFAULT but now the code is running ridiculously slow and filling up TEMPDB - can you suggest any other way of resolving the conflicts?
Yep, Collation could sometimes cause some misreads by the application. Due to this unnecessary table / index scans would happen which then result into performance issues. Also when working with different collations, any objects stored in the tempdb database can cause issues.
We can choose a collation which we can use when setting up SQL server, so that the same can be used for all your your databases and char columns. Again easier said than done...😀😀😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply