Process to change collation on a database
I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.
So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:
Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
foreach-object
{ Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }
Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).
Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.
sql-server sql-server-2014 collation dbatools
asked Mar 1 at 20:30
Tony HinkleTony Hinkle
2,9251523
add a comment |
I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.
So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:
Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
foreach-object
{ Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }
Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).
Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.
sql-server sql-server-2014 collation dbatools
asked Mar 1 at 20:30
Tony HinkleTony Hinkle
2,9251523
add a comment |
I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.
So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:
Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
foreach-object
{ Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }
Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).
Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.
sql-server sql-server-2014 collation dbatools
asked Mar 1 at 20:30
Tony HinkleTony Hinkle
2,9251523
I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.
So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:
Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
foreach-object
{ Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }
Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).
Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.
sql-server sql-server-2014 collation dbatools
sql-server sql-server-2014 collation dbatools
asked Mar 1 at 20:30
Tony HinkleTony Hinkle
2,9251523
asked Mar 1 at 20:30
Tony HinkleTony Hinkle
2,9251523
edited Mar 1 at 21:20
Tony Hinkle
asked Mar 1 at 20:30
Tony HinkleTony Hinkle
2,9251523
asked Mar 1 at 20:30
Tony HinkleTony Hinkle
2,9251523
asked Mar 1 at 20:30
Tony HinkleTony Hinkle
2,9251523
2,9251523
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation
Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.
Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:
Collation error in Jira when the database and server/default collation do not match in SQL Server
Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:
The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.
whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).
My advice: don't change anything! You even said:
a database that has been in production for a few years
If it has been in production for years without running into problems, then there are likely no problems :-)
answered Mar 1 at 20:37
Solomon RutzkySolomon Rutzky
49.1k582179
2
Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!
– Tony Hinkle
Mar 1 at 20:51
2
@TonyHinkle please see my updated notes (bottom half of the answer)
– Solomon Rutzky
Mar 1 at 21:01
2
--what collation do you dream in? :)
– Tony Hinkle
Mar 1 at 21:50
3
Solomon dreams in every collation simultaneously.
– Max Vernon
Mar 1 at 21:58
1
+1 As someone who manages three large JIRA instances, i can tell you it is the app-side that complains about violations.The main concern is making sure it uses case insensitive which without may break some of the OP’s plugins. Also, @TonyHinkle, the instance can be whatever it wants, JIRA is like all other apps that use a user defined to a database. It won’t use the Instance collation.
– clifton_h
Mar 2 at 1:55
|
show 5 more comments
This is too long for a comment but I want to call out the potential for data loss in char/varchar columns when changing the code page. Data may be mapped to a different character than the source code page or a '?' when no character exists at all. Importantly, no errors will be reported during the migration if data loss occurs.
This script shows the mapping for non-ASCII characters going from the 1252 to 437 code page:
WITH
t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 127 AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
,CP1252_Characters AS (
SELECT
num AS CodePoint
, CHAR(num) COLLATE SQL_Latin1_General_CP1_CI_AS AS CP1252_Character
FROM t1k
WHERE num < 256
)
SELECT
CP1252_Characters.CodePoint AS CP1252_CodePoint
, CP1252_Characters.CP1252_Character
, CP1252_Characters.CP1252_Character COLLATE SQL_Latin1_General_CP437_CI_AI AS Converted_CP457Character
FROM CP1252_Characters;
Note the absence of the Euro symbol in the 437 code page, for example. I hope you didn't have any Jira tickets containing this currency symbol :-)
+------------------+------------------+--------------------------+
| CP1252_CodePoint | CP1252_Character | Converted_CP457Character |
+------------------+------------------+--------------------------+
| 128 | € | ? |
| 129 | | ? |
| 130 | ‚ | , |
| 131 | ƒ | ƒ |
| 132 | „ | , |
| 133 | … | . |
| 134 | † | + |
| 135 | ‡ | ╪ |
| 136 | ˆ | ^ |
| 137 | ‰ | % |
| 138 | Š | S |
| 139 | ‹ | < |
| 140 | Π| O |
| 141 | | ? |
| 142 | Ž | Z |
| 143 | | ? |
| 144 | | ? |
| 145 | ‘ | ` |
| 146 | ’ | ' |
| 147 | “ | " |
| 148 | ” | " |
| 149 | • | |
| 150 | – | - |
| 151 | — | - |
| 152 | ˜ | ~ |
| 153 | ™ | T |
| 154 | š | s |
| 155 | › | > |
| 156 | œ | o |
| 157 | | ? |
| 158 | ž | z |
| 159 | Ÿ | Y |
| 160 | | |
| 161 | ¡ | ¡ |
| 162 | ¢ | ¢ |
| 163 | £ | £ |
| 164 | ¤ | |
| 165 | ¥ | ¥ |
| 166 | ¦ | ▌ |
| 167 | § | |
| 168 | ¨ | " |
| 169 | © | c |
| 170 | ª | ª |
| 171 | « | « |
| 172 | ¬ | ¬ |
| 173 | | - |
| 174 | ® | r |
| 175 | ¯ | _ |
| 176 | ° | ° |
| 177 | ± | ± |
| 178 | ² | ² |
| 179 | ³ | 3 |
| 180 | ´ | ' |
| 181 | µ | µ |
| 182 | ¶ | |
| 183 | · | · |
| 184 | ¸ | , |
| 185 | ¹ | 1 |
| 186 | º | º |
| 187 | » | » |
| 188 | ¼ | ¼ |
| 189 | ½ | ½ |
| 190 | ¾ | _ |
| 191 | ¿ | ¿ |
| 192 | À | A |
| 193 | Á | A |
| 194 | Â | A |
| 195 | Ã | A |
| 196 | Ä | Ä |
| 197 | Å | Å |
| 198 | Æ | Æ |
| 199 | Ç | Ç |
| 200 | È | E |
| 201 | É | É |
| 202 | Ê | E |
| 203 | Ë | E |
| 204 | Ì | I |
| 205 | Í | I |
| 206 | Î | I |
| 207 | Ï | I |
| 208 | Ð | D |
| 209 | Ñ | Ñ |
| 210 | Ò | O |
| 211 | Ó | O |
| 212 | Ô | O |
| 213 | Õ | O |
| 214 | Ö | Ö |
| 215 | × | x |
| 216 | Ø | O |
| 217 | Ù | U |
| 218 | Ú | U |
| 219 | Û | U |
| 220 | Ü | Ü |
| 221 | Ý | Y |
| 222 | Þ | _ |
| 223 | ß | ß |
| 224 | à | à |
| 225 | á | á |
| 226 | â | â |
| 227 | ã | a |
| 228 | ä | ä |
| 229 | å | å |
| 230 | æ | æ |
| 231 | ç | ç |
| 232 | è | è |
| 233 | é | é |
| 234 | ê | ê |
| 235 | ë | ë |
| 236 | ì | ì |
| 237 | í | í |
| 238 | î | î |
| 239 | ï | ï |
| 240 | ð | d |
| 241 | ñ | ñ |
| 242 | ò | ò |
| 243 | ó | ó |
| 244 | ô | ô |
| 245 | õ | o |
| 246 | ö | ö |
| 247 | ÷ | ÷ |
| 248 | ø | o |
| 249 | ù | ù |
| 250 | ú | ú |
| 251 | û | û |
| 252 | ü | ü |
| 253 | ý | y |
| 254 | þ | _ |
| 255 | ÿ | ÿ |
+------------------+------------------+--------------------------+
answered Mar 2 at 12:53
Dan GuzmanDan Guzman
13.9k21735
OMG! Just think of all the euros we would lose! :) Thanks for this--very helpful.
– Tony Hinkle
Mar 2 at 13:23
@TonyHinkle and Dan: yes, this is definitely worth highlighting (and is covered in detail in Item #2 under "General Concepts" in Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, which is also linked in my answer). HOWEVER, with regards to Jira, it is unlikely to be an issue since the Atlassian-created Jira tables use onlyNVARCHARandNTEXT😿, so tickets are safe.
– Solomon Rutzky
Mar 4 at 14:43
@TonyHinkle and Dan: to be fair, there are two 3rd-party packages with tables in the Jira DB: "JQuartz" (scheduling) and something with all of its tables being prefixed with "AO_XX_name" where the "X"s are 6 hex digits. I think it's for sprint boards as some table names are:AOHIP_CHAT_USER,BOARDADMINS,CARDLAYOUT,ESTIMATESTATISTIC,SPRINTMARKER,SWIMLANE, etc. Those two packages contain bothNVARCHAR/NTEXT("AO" has someNVARCHAR(MAX)) andVARCHARcolumns. Butjiraissue,project,projectcategory,propertytext, etc tables useNTEXTfor "Description" columns.
– Solomon Rutzky
Mar 4 at 14:59
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231087%2fprocess-to-change-collation-on-a-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation
Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.
Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:
Collation error in Jira when the database and server/default collation do not match in SQL Server
Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:
The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.
whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).
My advice: don't change anything! You even said:
a database that has been in production for a few years
If it has been in production for years without running into problems, then there are likely no problems :-)
answered Mar 1 at 20:37
Solomon RutzkySolomon Rutzky
49.1k582179
2
Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!
– Tony Hinkle
Mar 1 at 20:51
2
@TonyHinkle please see my updated notes (bottom half of the answer)
– Solomon Rutzky
Mar 1 at 21:01
2
--what collation do you dream in? :)
– Tony Hinkle
Mar 1 at 21:50
3
Solomon dreams in every collation simultaneously.
– Max Vernon
Mar 1 at 21:58
1
+1 As someone who manages three large JIRA instances, i can tell you it is the app-side that complains about violations.The main concern is making sure it uses case insensitive which without may break some of the OP’s plugins. Also, @TonyHinkle, the instance can be whatever it wants, JIRA is like all other apps that use a user defined to a database. It won’t use the Instance collation.
– clifton_h
Mar 2 at 1:55
|
show 5 more comments
Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation
Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.
Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:
Collation error in Jira when the database and server/default collation do not match in SQL Server
Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:
The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.
whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).
My advice: don't change anything! You even said:
a database that has been in production for a few years
If it has been in production for years without running into problems, then there are likely no problems :-)
answered Mar 1 at 20:37
Solomon RutzkySolomon Rutzky
49.1k582179
2
Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!
– Tony Hinkle
Mar 1 at 20:51
2
@TonyHinkle please see my updated notes (bottom half of the answer)
– Solomon Rutzky
Mar 1 at 21:01
2
--what collation do you dream in? :)
– Tony Hinkle
Mar 1 at 21:50
3
Solomon dreams in every collation simultaneously.
– Max Vernon
Mar 1 at 21:58
1
+1 As someone who manages three large JIRA instances, i can tell you it is the app-side that complains about violations.The main concern is making sure it uses case insensitive which without may break some of the OP’s plugins. Also, @TonyHinkle, the instance can be whatever it wants, JIRA is like all other apps that use a user defined to a database. It won’t use the Instance collation.
– clifton_h
Mar 2 at 1:55
|
show 5 more comments
Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation
Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.
Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:
Collation error in Jira when the database and server/default collation do not match in SQL Server
Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:
The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.
whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).
My advice: don't change anything! You even said:
a database that has been in production for a few years
If it has been in production for years without running into problems, then there are likely no problems :-)
answered Mar 1 at 20:37
Solomon RutzkySolomon Rutzky
49.1k582179
Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation
Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.
Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:
Collation error in Jira when the database and server/default collation do not match in SQL Server
Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:
The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.
whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).
My advice: don't change anything! You even said:
a database that has been in production for a few years
If it has been in production for years without running into problems, then there are likely no problems :-)
answered Mar 1 at 20:37
Solomon RutzkySolomon Rutzky
49.1k582179
edited Mar 1 at 21:29
answered Mar 1 at 20:37
Solomon RutzkySolomon Rutzky
49.1k582179
answered Mar 1 at 20:37
Solomon RutzkySolomon Rutzky
49.1k582179
answered Mar 1 at 20:37
Solomon RutzkySolomon Rutzky
49.1k582179
49.1k582179
2
Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!
– Tony Hinkle
Mar 1 at 20:51
2
@TonyHinkle please see my updated notes (bottom half of the answer)
– Solomon Rutzky
Mar 1 at 21:01
2
--what collation do you dream in? :)
– Tony Hinkle
Mar 1 at 21:50
3
Solomon dreams in every collation simultaneously.
– Max Vernon
Mar 1 at 21:58
1
+1 As someone who manages three large JIRA instances, i can tell you it is the app-side that complains about violations.The main concern is making sure it uses case insensitive which without may break some of the OP’s plugins. Also, @TonyHinkle, the instance can be whatever it wants, JIRA is like all other apps that use a user defined to a database. It won’t use the Instance collation.
– clifton_h
Mar 2 at 1:55
|
show 5 more comments
2
Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!
– Tony Hinkle
Mar 1 at 20:51
2
@TonyHinkle please see my updated notes (bottom half of the answer)
– Solomon Rutzky
Mar 1 at 21:01
2
--what collation do you dream in? :)
– Tony Hinkle
Mar 1 at 21:50
3
Solomon dreams in every collation simultaneously.
– Max Vernon
Mar 1 at 21:58
1
+1 As someone who manages three large JIRA instances, i can tell you it is the app-side that complains about violations.The main concern is making sure it uses case insensitive which without may break some of the OP’s plugins. Also, @TonyHinkle, the instance can be whatever it wants, JIRA is like all other apps that use a user defined to a database. It won’t use the Instance collation.
– clifton_h
Mar 2 at 1:55
2
2
Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!
– Tony Hinkle
Mar 1 at 20:51
Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!
– Tony Hinkle
Mar 1 at 20:51
2
2
@TonyHinkle please see my updated notes (bottom half of the answer)
– Solomon Rutzky
Mar 1 at 21:01
@TonyHinkle please see my updated notes (bottom half of the answer)
– Solomon Rutzky
Mar 1 at 21:01
2
2
--what collation do you dream in? :)
– Tony Hinkle
Mar 1 at 21:50
--what collation do you dream in? :)
– Tony Hinkle
Mar 1 at 21:50
3
3
Solomon dreams in every collation simultaneously.
– Max Vernon
Mar 1 at 21:58
Solomon dreams in every collation simultaneously.
– Max Vernon
Mar 1 at 21:58
1
1
+1 As someone who manages three large JIRA instances, i can tell you it is the app-side that complains about violations.The main concern is making sure it uses case insensitive which without may break some of the OP’s plugins. Also, @TonyHinkle, the instance can be whatever it wants, JIRA is like all other apps that use a user defined to a database. It won’t use the Instance collation.
– clifton_h
Mar 2 at 1:55
+1 As someone who manages three large JIRA instances, i can tell you it is the app-side that complains about violations.The main concern is making sure it uses case insensitive which without may break some of the OP’s plugins. Also, @TonyHinkle, the instance can be whatever it wants, JIRA is like all other apps that use a user defined to a database. It won’t use the Instance collation.
– clifton_h
Mar 2 at 1:55
|
show 5 more comments
This is too long for a comment but I want to call out the potential for data loss in char/varchar columns when changing the code page. Data may be mapped to a different character than the source code page or a '?' when no character exists at all. Importantly, no errors will be reported during the migration if data loss occurs.
This script shows the mapping for non-ASCII characters going from the 1252 to 437 code page:
WITH
t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 127 AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
,CP1252_Characters AS (
SELECT
num AS CodePoint
, CHAR(num) COLLATE SQL_Latin1_General_CP1_CI_AS AS CP1252_Character
FROM t1k
WHERE num < 256
)
SELECT
CP1252_Characters.CodePoint AS CP1252_CodePoint
, CP1252_Characters.CP1252_Character
, CP1252_Characters.CP1252_Character COLLATE SQL_Latin1_General_CP437_CI_AI AS Converted_CP457Character
FROM CP1252_Characters;
Note the absence of the Euro symbol in the 437 code page, for example. I hope you didn't have any Jira tickets containing this currency symbol :-)
+------------------+------------------+--------------------------+
| CP1252_CodePoint | CP1252_Character | Converted_CP457Character |
+------------------+------------------+--------------------------+
| 128 | € | ? |
| 129 | | ? |
| 130 | ‚ | , |
| 131 | ƒ | ƒ |
| 132 | „ | , |
| 133 | … | . |
| 134 | † | + |
| 135 | ‡ | ╪ |
| 136 | ˆ | ^ |
| 137 | ‰ | % |
| 138 | Š | S |
| 139 | ‹ | < |
| 140 | Π| O |
| 141 | | ? |
| 142 | Ž | Z |
| 143 | | ? |
| 144 | | ? |
| 145 | ‘ | ` |
| 146 | ’ | ' |
| 147 | “ | " |
| 148 | ” | " |
| 149 | • | |
| 150 | – | - |
| 151 | — | - |
| 152 | ˜ | ~ |
| 153 | ™ | T |
| 154 | š | s |
| 155 | › | > |
| 156 | œ | o |
| 157 | | ? |
| 158 | ž | z |
| 159 | Ÿ | Y |
| 160 | | |
| 161 | ¡ | ¡ |
| 162 | ¢ | ¢ |
| 163 | £ | £ |
| 164 | ¤ | |
| 165 | ¥ | ¥ |
| 166 | ¦ | ▌ |
| 167 | § | |
| 168 | ¨ | " |
| 169 | © | c |
| 170 | ª | ª |
| 171 | « | « |
| 172 | ¬ | ¬ |
| 173 | | - |
| 174 | ® | r |
| 175 | ¯ | _ |
| 176 | ° | ° |
| 177 | ± | ± |
| 178 | ² | ² |
| 179 | ³ | 3 |
| 180 | ´ | ' |
| 181 | µ | µ |
| 182 | ¶ | |
| 183 | · | · |
| 184 | ¸ | , |
| 185 | ¹ | 1 |
| 186 | º | º |
| 187 | » | » |
| 188 | ¼ | ¼ |
| 189 | ½ | ½ |
| 190 | ¾ | _ |
| 191 | ¿ | ¿ |
| 192 | À | A |
| 193 | Á | A |
| 194 | Â | A |
| 195 | Ã | A |
| 196 | Ä | Ä |
| 197 | Å | Å |
| 198 | Æ | Æ |
| 199 | Ç | Ç |
| 200 | È | E |
| 201 | É | É |
| 202 | Ê | E |
| 203 | Ë | E |
| 204 | Ì | I |
| 205 | Í | I |
| 206 | Î | I |
| 207 | Ï | I |
| 208 | Ð | D |
| 209 | Ñ | Ñ |
| 210 | Ò | O |
| 211 | Ó | O |
| 212 | Ô | O |
| 213 | Õ | O |
| 214 | Ö | Ö |
| 215 | × | x |
| 216 | Ø | O |
| 217 | Ù | U |
| 218 | Ú | U |
| 219 | Û | U |
| 220 | Ü | Ü |
| 221 | Ý | Y |
| 222 | Þ | _ |
| 223 | ß | ß |
| 224 | à | à |
| 225 | á | á |
| 226 | â | â |
| 227 | ã | a |
| 228 | ä | ä |
| 229 | å | å |
| 230 | æ | æ |
| 231 | ç | ç |
| 232 | è | è |
| 233 | é | é |
| 234 | ê | ê |
| 235 | ë | ë |
| 236 | ì | ì |
| 237 | í | í |
| 238 | î | î |
| 239 | ï | ï |
| 240 | ð | d |
| 241 | ñ | ñ |
| 242 | ò | ò |
| 243 | ó | ó |
| 244 | ô | ô |
| 245 | õ | o |
| 246 | ö | ö |
| 247 | ÷ | ÷ |
| 248 | ø | o |
| 249 | ù | ù |
| 250 | ú | ú |
| 251 | û | û |
| 252 | ü | ü |
| 253 | ý | y |
| 254 | þ | _ |
| 255 | ÿ | ÿ |
+------------------+------------------+--------------------------+
answered Mar 2 at 12:53
Dan GuzmanDan Guzman
13.9k21735
OMG! Just think of all the euros we would lose! :) Thanks for this--very helpful.
– Tony Hinkle
Mar 2 at 13:23
@TonyHinkle and Dan: yes, this is definitely worth highlighting (and is covered in detail in Item #2 under "General Concepts" in Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, which is also linked in my answer). HOWEVER, with regards to Jira, it is unlikely to be an issue since the Atlassian-created Jira tables use onlyNVARCHARandNTEXT😿, so tickets are safe.
– Solomon Rutzky
Mar 4 at 14:43
@TonyHinkle and Dan: to be fair, there are two 3rd-party packages with tables in the Jira DB: "JQuartz" (scheduling) and something with all of its tables being prefixed with "AO_XX_name" where the "X"s are 6 hex digits. I think it's for sprint boards as some table names are:AOHIP_CHAT_USER,BOARDADMINS,CARDLAYOUT,ESTIMATESTATISTIC,SPRINTMARKER,SWIMLANE, etc. Those two packages contain bothNVARCHAR/NTEXT("AO" has someNVARCHAR(MAX)) andVARCHARcolumns. Butjiraissue,project,projectcategory,propertytext, etc tables useNTEXTfor "Description" columns.
– Solomon Rutzky
Mar 4 at 14:59
add a comment |
This is too long for a comment but I want to call out the potential for data loss in char/varchar columns when changing the code page. Data may be mapped to a different character than the source code page or a '?' when no character exists at all. Importantly, no errors will be reported during the migration if data loss occurs.
This script shows the mapping for non-ASCII characters going from the 1252 to 437 code page:
WITH
t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 127 AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
,CP1252_Characters AS (
SELECT
num AS CodePoint
, CHAR(num) COLLATE SQL_Latin1_General_CP1_CI_AS AS CP1252_Character
FROM t1k
WHERE num < 256
)
SELECT
CP1252_Characters.CodePoint AS CP1252_CodePoint
, CP1252_Characters.CP1252_Character
, CP1252_Characters.CP1252_Character COLLATE SQL_Latin1_General_CP437_CI_AI AS Converted_CP457Character
FROM CP1252_Characters;
Note the absence of the Euro symbol in the 437 code page, for example. I hope you didn't have any Jira tickets containing this currency symbol :-)
+------------------+------------------+--------------------------+
| CP1252_CodePoint | CP1252_Character | Converted_CP457Character |
+------------------+------------------+--------------------------+
| 128 | € | ? |
| 129 | | ? |
| 130 | ‚ | , |
| 131 | ƒ | ƒ |
| 132 | „ | , |
| 133 | … | . |
| 134 | † | + |
| 135 | ‡ | ╪ |
| 136 | ˆ | ^ |
| 137 | ‰ | % |
| 138 | Š | S |
| 139 | ‹ | < |
| 140 | Π| O |
| 141 | | ? |
| 142 | Ž | Z |
| 143 | | ? |
| 144 | | ? |
| 145 | ‘ | ` |
| 146 | ’ | ' |
| 147 | “ | " |
| 148 | ” | " |
| 149 | • | |
| 150 | – | - |
| 151 | — | - |
| 152 | ˜ | ~ |
| 153 | ™ | T |
| 154 | š | s |
| 155 | › | > |
| 156 | œ | o |
| 157 | | ? |
| 158 | ž | z |
| 159 | Ÿ | Y |
| 160 | | |
| 161 | ¡ | ¡ |
| 162 | ¢ | ¢ |
| 163 | £ | £ |
| 164 | ¤ | |
| 165 | ¥ | ¥ |
| 166 | ¦ | ▌ |
| 167 | § | |
| 168 | ¨ | " |
| 169 | © | c |
| 170 | ª | ª |
| 171 | « | « |
| 172 | ¬ | ¬ |
| 173 | | - |
| 174 | ® | r |
| 175 | ¯ | _ |
| 176 | ° | ° |
| 177 | ± | ± |
| 178 | ² | ² |
| 179 | ³ | 3 |
| 180 | ´ | ' |
| 181 | µ | µ |
| 182 | ¶ | |
| 183 | · | · |
| 184 | ¸ | , |
| 185 | ¹ | 1 |
| 186 | º | º |
| 187 | » | » |
| 188 | ¼ | ¼ |
| 189 | ½ | ½ |
| 190 | ¾ | _ |
| 191 | ¿ | ¿ |
| 192 | À | A |
| 193 | Á | A |
| 194 | Â | A |
| 195 | Ã | A |
| 196 | Ä | Ä |
| 197 | Å | Å |
| 198 | Æ | Æ |
| 199 | Ç | Ç |
| 200 | È | E |
| 201 | É | É |
| 202 | Ê | E |
| 203 | Ë | E |
| 204 | Ì | I |
| 205 | Í | I |
| 206 | Î | I |
| 207 | Ï | I |
| 208 | Ð | D |
| 209 | Ñ | Ñ |
| 210 | Ò | O |
| 211 | Ó | O |
| 212 | Ô | O |
| 213 | Õ | O |
| 214 | Ö | Ö |
| 215 | × | x |
| 216 | Ø | O |
| 217 | Ù | U |
| 218 | Ú | U |
| 219 | Û | U |
| 220 | Ü | Ü |
| 221 | Ý | Y |
| 222 | Þ | _ |
| 223 | ß | ß |
| 224 | à | à |
| 225 | á | á |
| 226 | â | â |
| 227 | ã | a |
| 228 | ä | ä |
| 229 | å | å |
| 230 | æ | æ |
| 231 | ç | ç |
| 232 | è | è |
| 233 | é | é |
| 234 | ê | ê |
| 235 | ë | ë |
| 236 | ì | ì |
| 237 | í | í |
| 238 | î | î |
| 239 | ï | ï |
| 240 | ð | d |
| 241 | ñ | ñ |
| 242 | ò | ò |
| 243 | ó | ó |
| 244 | ô | ô |
| 245 | õ | o |
| 246 | ö | ö |
| 247 | ÷ | ÷ |
| 248 | ø | o |
| 249 | ù | ù |
| 250 | ú | ú |
| 251 | û | û |
| 252 | ü | ü |
| 253 | ý | y |
| 254 | þ | _ |
| 255 | ÿ | ÿ |
+------------------+------------------+--------------------------+
answered Mar 2 at 12:53
Dan GuzmanDan Guzman
13.9k21735
OMG! Just think of all the euros we would lose! :) Thanks for this--very helpful.
– Tony Hinkle
Mar 2 at 13:23
@TonyHinkle and Dan: yes, this is definitely worth highlighting (and is covered in detail in Item #2 under "General Concepts" in Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, which is also linked in my answer). HOWEVER, with regards to Jira, it is unlikely to be an issue since the Atlassian-created Jira tables use onlyNVARCHARandNTEXT😿, so tickets are safe.
– Solomon Rutzky
Mar 4 at 14:43
@TonyHinkle and Dan: to be fair, there are two 3rd-party packages with tables in the Jira DB: "JQuartz" (scheduling) and something with all of its tables being prefixed with "AO_XX_name" where the "X"s are 6 hex digits. I think it's for sprint boards as some table names are:AOHIP_CHAT_USER,BOARDADMINS,CARDLAYOUT,ESTIMATESTATISTIC,SPRINTMARKER,SWIMLANE, etc. Those two packages contain bothNVARCHAR/NTEXT("AO" has someNVARCHAR(MAX)) andVARCHARcolumns. Butjiraissue,project,projectcategory,propertytext, etc tables useNTEXTfor "Description" columns.
– Solomon Rutzky
Mar 4 at 14:59
add a comment |
This is too long for a comment but I want to call out the potential for data loss in char/varchar columns when changing the code page. Data may be mapped to a different character than the source code page or a '?' when no character exists at all. Importantly, no errors will be reported during the migration if data loss occurs.
This script shows the mapping for non-ASCII characters going from the 1252 to 437 code page:
WITH
t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 127 AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
,CP1252_Characters AS (
SELECT
num AS CodePoint
, CHAR(num) COLLATE SQL_Latin1_General_CP1_CI_AS AS CP1252_Character
FROM t1k
WHERE num < 256
)
SELECT
CP1252_Characters.CodePoint AS CP1252_CodePoint
, CP1252_Characters.CP1252_Character
, CP1252_Characters.CP1252_Character COLLATE SQL_Latin1_General_CP437_CI_AI AS Converted_CP457Character
FROM CP1252_Characters;
Note the absence of the Euro symbol in the 437 code page, for example. I hope you didn't have any Jira tickets containing this currency symbol :-)
+------------------+------------------+--------------------------+
| CP1252_CodePoint | CP1252_Character | Converted_CP457Character |
+------------------+------------------+--------------------------+
| 128 | € | ? |
| 129 | | ? |
| 130 | ‚ | , |
| 131 | ƒ | ƒ |
| 132 | „ | , |
| 133 | … | . |
| 134 | † | + |
| 135 | ‡ | ╪ |
| 136 | ˆ | ^ |
| 137 | ‰ | % |
| 138 | Š | S |
| 139 | ‹ | < |
| 140 | Π| O |
| 141 | | ? |
| 142 | Ž | Z |
| 143 | | ? |
| 144 | | ? |
| 145 | ‘ | ` |
| 146 | ’ | ' |
| 147 | “ | " |
| 148 | ” | " |
| 149 | • | |
| 150 | – | - |
| 151 | — | - |
| 152 | ˜ | ~ |
| 153 | ™ | T |
| 154 | š | s |
| 155 | › | > |
| 156 | œ | o |
| 157 | | ? |
| 158 | ž | z |
| 159 | Ÿ | Y |
| 160 | | |
| 161 | ¡ | ¡ |
| 162 | ¢ | ¢ |
| 163 | £ | £ |
| 164 | ¤ | |
| 165 | ¥ | ¥ |
| 166 | ¦ | ▌ |
| 167 | § | |
| 168 | ¨ | " |
| 169 | © | c |
| 170 | ª | ª |
| 171 | « | « |
| 172 | ¬ | ¬ |
| 173 | | - |
| 174 | ® | r |
| 175 | ¯ | _ |
| 176 | ° | ° |
| 177 | ± | ± |
| 178 | ² | ² |
| 179 | ³ | 3 |
| 180 | ´ | ' |
| 181 | µ | µ |
| 182 | ¶ | |
| 183 | · | · |
| 184 | ¸ | , |
| 185 | ¹ | 1 |
| 186 | º | º |
| 187 | » | » |
| 188 | ¼ | ¼ |
| 189 | ½ | ½ |
| 190 | ¾ | _ |
| 191 | ¿ | ¿ |
| 192 | À | A |
| 193 | Á | A |
| 194 | Â | A |
| 195 | Ã | A |
| 196 | Ä | Ä |
| 197 | Å | Å |
| 198 | Æ | Æ |
| 199 | Ç | Ç |
| 200 | È | E |
| 201 | É | É |
| 202 | Ê | E |
| 203 | Ë | E |
| 204 | Ì | I |
| 205 | Í | I |
| 206 | Î | I |
| 207 | Ï | I |
| 208 | Ð | D |
| 209 | Ñ | Ñ |
| 210 | Ò | O |
| 211 | Ó | O |
| 212 | Ô | O |
| 213 | Õ | O |
| 214 | Ö | Ö |
| 215 | × | x |
| 216 | Ø | O |
| 217 | Ù | U |
| 218 | Ú | U |
| 219 | Û | U |
| 220 | Ü | Ü |
| 221 | Ý | Y |
| 222 | Þ | _ |
| 223 | ß | ß |
| 224 | à | à |
| 225 | á | á |
| 226 | â | â |
| 227 | ã | a |
| 228 | ä | ä |
| 229 | å | å |
| 230 | æ | æ |
| 231 | ç | ç |
| 232 | è | è |
| 233 | é | é |
| 234 | ê | ê |
| 235 | ë | ë |
| 236 | ì | ì |
| 237 | í | í |
| 238 | î | î |
| 239 | ï | ï |
| 240 | ð | d |
| 241 | ñ | ñ |
| 242 | ò | ò |
| 243 | ó | ó |
| 244 | ô | ô |
| 245 | õ | o |
| 246 | ö | ö |
| 247 | ÷ | ÷ |
| 248 | ø | o |
| 249 | ù | ù |
| 250 | ú | ú |
| 251 | û | û |
| 252 | ü | ü |
| 253 | ý | y |
| 254 | þ | _ |
| 255 | ÿ | ÿ |
+------------------+------------------+--------------------------+
answered Mar 2 at 12:53
Dan GuzmanDan Guzman
13.9k21735
This is too long for a comment but I want to call out the potential for data loss in char/varchar columns when changing the code page. Data may be mapped to a different character than the source code page or a '?' when no character exists at all. Importantly, no errors will be reported during the migration if data loss occurs.
This script shows the mapping for non-ASCII characters going from the 1252 to 437 code page:
WITH
t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 127 AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
,CP1252_Characters AS (
SELECT
num AS CodePoint
, CHAR(num) COLLATE SQL_Latin1_General_CP1_CI_AS AS CP1252_Character
FROM t1k
WHERE num < 256
)
SELECT
CP1252_Characters.CodePoint AS CP1252_CodePoint
, CP1252_Characters.CP1252_Character
, CP1252_Characters.CP1252_Character COLLATE SQL_Latin1_General_CP437_CI_AI AS Converted_CP457Character
FROM CP1252_Characters;
Note the absence of the Euro symbol in the 437 code page, for example. I hope you didn't have any Jira tickets containing this currency symbol :-)
+------------------+------------------+--------------------------+
| CP1252_CodePoint | CP1252_Character | Converted_CP457Character |
+------------------+------------------+--------------------------+
| 128 | € | ? |
| 129 | | ? |
| 130 | ‚ | , |
| 131 | ƒ | ƒ |
| 132 | „ | , |
| 133 | … | . |
| 134 | † | + |
| 135 | ‡ | ╪ |
| 136 | ˆ | ^ |
| 137 | ‰ | % |
| 138 | Š | S |
| 139 | ‹ | < |
| 140 | Π| O |
| 141 | | ? |
| 142 | Ž | Z |
| 143 | | ? |
| 144 | | ? |
| 145 | ‘ | ` |
| 146 | ’ | ' |
| 147 | “ | " |
| 148 | ” | " |
| 149 | • | |
| 150 | – | - |
| 151 | — | - |
| 152 | ˜ | ~ |
| 153 | ™ | T |
| 154 | š | s |
| 155 | › | > |
| 156 | œ | o |
| 157 | | ? |
| 158 | ž | z |
| 159 | Ÿ | Y |
| 160 | | |
| 161 | ¡ | ¡ |
| 162 | ¢ | ¢ |
| 163 | £ | £ |
| 164 | ¤ | |
| 165 | ¥ | ¥ |
| 166 | ¦ | ▌ |
| 167 | § | |
| 168 | ¨ | " |
| 169 | © | c |
| 170 | ª | ª |
| 171 | « | « |
| 172 | ¬ | ¬ |
| 173 | | - |
| 174 | ® | r |
| 175 | ¯ | _ |
| 176 | ° | ° |
| 177 | ± | ± |
| 178 | ² | ² |
| 179 | ³ | 3 |
| 180 | ´ | ' |
| 181 | µ | µ |
| 182 | ¶ | |
| 183 | · | · |
| 184 | ¸ | , |
| 185 | ¹ | 1 |
| 186 | º | º |
| 187 | » | » |
| 188 | ¼ | ¼ |
| 189 | ½ | ½ |
| 190 | ¾ | _ |
| 191 | ¿ | ¿ |
| 192 | À | A |
| 193 | Á | A |
| 194 | Â | A |
| 195 | Ã | A |
| 196 | Ä | Ä |
| 197 | Å | Å |
| 198 | Æ | Æ |
| 199 | Ç | Ç |
| 200 | È | E |
| 201 | É | É |
| 202 | Ê | E |
| 203 | Ë | E |
| 204 | Ì | I |
| 205 | Í | I |
| 206 | Î | I |
| 207 | Ï | I |
| 208 | Ð | D |
| 209 | Ñ | Ñ |
| 210 | Ò | O |
| 211 | Ó | O |
| 212 | Ô | O |
| 213 | Õ | O |
| 214 | Ö | Ö |
| 215 | × | x |
| 216 | Ø | O |
| 217 | Ù | U |
| 218 | Ú | U |
| 219 | Û | U |
| 220 | Ü | Ü |
| 221 | Ý | Y |
| 222 | Þ | _ |
| 223 | ß | ß |
| 224 | à | à |
| 225 | á | á |
| 226 | â | â |
| 227 | ã | a |
| 228 | ä | ä |
| 229 | å | å |
| 230 | æ | æ |
| 231 | ç | ç |
| 232 | è | è |
| 233 | é | é |
| 234 | ê | ê |
| 235 | ë | ë |
| 236 | ì | ì |
| 237 | í | í |
| 238 | î | î |
| 239 | ï | ï |
| 240 | ð | d |
| 241 | ñ | ñ |
| 242 | ò | ò |
| 243 | ó | ó |
| 244 | ô | ô |
| 245 | õ | o |
| 246 | ö | ö |
| 247 | ÷ | ÷ |
| 248 | ø | o |
| 249 | ù | ù |
| 250 | ú | ú |
| 251 | û | û |
| 252 | ü | ü |
| 253 | ý | y |
| 254 | þ | _ |
| 255 | ÿ | ÿ |
+------------------+------------------+--------------------------+
answered Mar 2 at 12:53
Dan GuzmanDan Guzman
13.9k21735
answered Mar 2 at 12:53
Dan GuzmanDan Guzman
13.9k21735
answered Mar 2 at 12:53
Dan GuzmanDan Guzman
13.9k21735
answered Mar 2 at 12:53
Dan GuzmanDan Guzman
13.9k21735
13.9k21735
OMG! Just think of all the euros we would lose! :) Thanks for this--very helpful.
– Tony Hinkle
Mar 2 at 13:23
@TonyHinkle and Dan: yes, this is definitely worth highlighting (and is covered in detail in Item #2 under "General Concepts" in Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, which is also linked in my answer). HOWEVER, with regards to Jira, it is unlikely to be an issue since the Atlassian-created Jira tables use onlyNVARCHARandNTEXT😿, so tickets are safe.
– Solomon Rutzky
Mar 4 at 14:43
@TonyHinkle and Dan: to be fair, there are two 3rd-party packages with tables in the Jira DB: "JQuartz" (scheduling) and something with all of its tables being prefixed with "AO_XX_name" where the "X"s are 6 hex digits. I think it's for sprint boards as some table names are:AOHIP_CHAT_USER,BOARDADMINS,CARDLAYOUT,ESTIMATESTATISTIC,SPRINTMARKER,SWIMLANE, etc. Those two packages contain bothNVARCHAR/NTEXT("AO" has someNVARCHAR(MAX)) andVARCHARcolumns. Butjiraissue,project,projectcategory,propertytext, etc tables useNTEXTfor "Description" columns.
– Solomon Rutzky
Mar 4 at 14:59
add a comment |
OMG! Just think of all the euros we would lose! :) Thanks for this--very helpful.
– Tony Hinkle
Mar 2 at 13:23
@TonyHinkle and Dan: yes, this is definitely worth highlighting (and is covered in detail in Item #2 under "General Concepts" in Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, which is also linked in my answer). HOWEVER, with regards to Jira, it is unlikely to be an issue since the Atlassian-created Jira tables use onlyNVARCHARandNTEXT😿, so tickets are safe.
– Solomon Rutzky
Mar 4 at 14:43
@TonyHinkle and Dan: to be fair, there are two 3rd-party packages with tables in the Jira DB: "JQuartz" (scheduling) and something with all of its tables being prefixed with "AO_XX_name" where the "X"s are 6 hex digits. I think it's for sprint boards as some table names are:AOHIP_CHAT_USER,BOARDADMINS,CARDLAYOUT,ESTIMATESTATISTIC,SPRINTMARKER,SWIMLANE, etc. Those two packages contain bothNVARCHAR/NTEXT("AO" has someNVARCHAR(MAX)) andVARCHARcolumns. Butjiraissue,project,projectcategory,propertytext, etc tables useNTEXTfor "Description" columns.
– Solomon Rutzky
Mar 4 at 14:59
OMG! Just think of all the euros we would lose! :) Thanks for this--very helpful.
– Tony Hinkle
Mar 2 at 13:23
OMG! Just think of all the euros we would lose! :) Thanks for this--very helpful.
– Tony Hinkle
Mar 2 at 13:23
@TonyHinkle and Dan: yes, this is definitely worth highlighting (and is covered in detail in Item #2 under "General Concepts" in Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, which is also linked in my answer). HOWEVER, with regards to Jira, it is unlikely to be an issue since the Atlassian-created Jira tables use only
NVARCHAR and NTEXT 😿, so tickets are safe.– Solomon Rutzky
Mar 4 at 14:43
@TonyHinkle and Dan: yes, this is definitely worth highlighting (and is covered in detail in Item #2 under "General Concepts" in Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, which is also linked in my answer). HOWEVER, with regards to Jira, it is unlikely to be an issue since the Atlassian-created Jira tables use only
NVARCHAR and NTEXT 😿, so tickets are safe.– Solomon Rutzky
Mar 4 at 14:43
@TonyHinkle and Dan: to be fair, there are two 3rd-party packages with tables in the Jira DB: "JQuartz" (scheduling) and something with all of its tables being prefixed with "AO_XX_name" where the "X"s are 6 hex digits. I think it's for sprint boards as some table names are:
AOHIP_CHAT_USER, BOARDADMINS, CARDLAYOUT, ESTIMATESTATISTIC, SPRINTMARKER, SWIMLANE, etc. Those two packages contain both NVARCHAR / NTEXT ("AO" has some NVARCHAR(MAX)) and VARCHAR columns. But jiraissue, project, projectcategory, propertytext, etc tables use NTEXT for "Description" columns.– Solomon Rutzky
Mar 4 at 14:59
@TonyHinkle and Dan: to be fair, there are two 3rd-party packages with tables in the Jira DB: "JQuartz" (scheduling) and something with all of its tables being prefixed with "AO_XX_name" where the "X"s are 6 hex digits. I think it's for sprint boards as some table names are:
AOHIP_CHAT_USER, BOARDADMINS, CARDLAYOUT, ESTIMATESTATISTIC, SPRINTMARKER, SWIMLANE, etc. Those two packages contain both NVARCHAR / NTEXT ("AO" has some NVARCHAR(MAX)) and VARCHAR columns. But jiraissue, project, projectcategory, propertytext, etc tables use NTEXT for "Description" columns.– Solomon Rutzky
Mar 4 at 14:59
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231087%2fprocess-to-change-collation-on-a-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
