February 24, 2011 at 11:36 am
Application is publishing to the database as
Najwyższy Rycerz Przewodniczył Dyskusji Panelowej Organizacji Narodów Zjednoczonych
ending up in the Database as
Najwyzszy Rycerz Przewodniczyl Dyskusji Panelowej Organizacji Narodów Zjednoczonych
The Server (and Database) Collation is SQL_Latin1_General_CP1_CI_AS and the column that they are publishing the data to is NTEXT(max)
I am seeing other special characters and even Polish characters in the Database.. but they say they cannot publish the polish characters
seeing this in the DB in the title column:
'Los Caballeros muertos en Irak y Afganistán son elegibles para el beneficio por muerte'
'Rycerze łączą się w modlitwie z katolikami w Ziemi Świętej podczas wizyty Ojca Świętego Benedykta'
'Przemówienie Najwyższego Rycerza podczas Najwyższej Konwencji 2006'
I really don't know where to go with this.. Can anyone give some insight??
February 24, 2011 at 12:27 pm
Latin collation will not allow Polish characters. It sounds like you need to change the database collation. Look up "collations [SQL Server], about collations" in Books Online. (without the quotes).
And read this link: http://msdn.microsoft.com/en-us/library/ms144250.aspx
EDIT: I believe you can change column collation and perhaps table collation too, but that gets messy if you don't take the time to maintain it properly.
February 24, 2011 at 12:43 pm
I did some poking around and created a temp table using the DDL from the original table.
I inserted Najwyzszy Rycerz Przewodniczyl Dyskusji Panelowej Organizacji Narodów Zjednoczonych
into a column of one row in the table using a simple update statement.
When I select against my newly inserted row using (a 3rd party query tool) , the column displays correctly:
Najwyzszy Rycerz Przewodniczyl Dyskusji Panelowej Organizacji Narodów Zjednoczonych
When I select against my newly inserted row using SQL MGT STUDIO Q , the column displays
INcorrectly:
Najwyzszy Rycerz Przewodniczyl Dyskusji Panelowej Organizacji Narodów Zjednoczonych
when I check the Database collation for my original Database and the temp copy table, they are the same
SELECT DATABASEPROPERTYEX('databasename', 'Collation') SQLCollation;
SQL_Latin1_General_CP1_CI_AS
now I'm really confused.
February 25, 2011 at 4:25 am
Did you use a #Temp table or a regular table that you deleted afterword?
February 25, 2011 at 5:21 am
Just a regular table.
February 25, 2011 at 5:30 am
Hm. That is odd. What OS collation are you using?
How is the data being inserted? Via a client front end, SSIS, Bulk Insert, etc.? Has that changed recently?
Is the data coming from another table on the same database server?
February 25, 2011 at 6:25 am
What OS collation are you using?
I do not know how to determine the OS collation. :blush:
How is the data being inserted? Via a client front end, SSIS, Bulk Insert, etc.? Has that changed recently? Normally, the data is inserted from a front end application which is an Application used for deploying website content.
for my test........
I updated one column in one row like this:
UPDATE table SET title = 'Najwyzszy Rycerz Przewodniczyl Dyskusji Panelowej Organizacji Narodów Zjednoczonych' WHERE id = 873
When I display the newly updated row in MGT studio using Select * from table where ID = '873' - it DOES NOT show the Polish Characters. but displays like this:
Najwyzszy Rycerz Przewodniczyl Dyskusji Panelowej Organizacji Narodów Zjednoczonych
Then when I display the same row using a 3rd party Query tool, it displays properly like this:
Najwyzszy Rycerz Przewodniczyl Dyskusji Panelowej Organizacji Narodów Zjednoczonych
February 25, 2011 at 6:36 am
Jpotucek (2/25/2011)
What OS collation are you using?I do not know how to determine the OS collation. :blush:
Try Control Panel under Regional and Language Options (I'm looking at this in XP, so your server OS may have a slightly different name for this). From the the Regional Options tab, get Standard and Location. From the Advanced tab, get the language & code page conversion tables. That will help us determine the Windows collation.
Also, if this data is coming in via a client, where does the client sit? On the client's PC? On a Citrix terminal server? On a web server? We should verify the collations on all boxes that are touched, so whichever it is, check that box to see if it's the same as the SQL Server.
Lastly, check Teamsite itself to see if it's doing any conversions of the string that might have lost those characters. If it's not using Unicode, then chances are that's where the special characters turn into regular characters.
I don't want to sound mean, but your test is not "valid" if you are using just TOAD on one machine to do your testing. You'll need to do more troubleshooting than that to verify the issue. Move to someone else's machine, test in SSMS. Then use a test version of Teamsite to test the same thing. That will give you more details on where the issue is, though I'm betting it's either the app or the app's home box.
February 25, 2011 at 6:49 am
Try Control Panel under Regional and Language Options (I'm looking at this in XP, so your server OS may have a slightly different name for this). From the the Regional Options tab, get Standard and Location. From the Advanced tab, get the language & code page conversion tables. That will help us determine the Windows collation.
OS collation: under Regional Options Tab, Standard is English and Location is united states
under Advanced language is English and ALL code page conversion tables are selected.
I understand what you are saying about my test not being valid, but I tried it from several different machines - including from the Server hosting the Database - and got the same results each time.
what I think I did prove was that I CAN insert Polish characters into the Database.
I believe is is either the Application doing some kind of conversion or a wrong collation on the server hosting the front end application - but unfortunetly I have access to neither.
February 25, 2011 at 7:30 am
Jpotucek (2/25/2011)
I understand what you are saying about my test not being valid, but I tried it from several different machines - including from the Server hosting the Database - and got the same results each time.what I think I did prove was that I CAN insert Polish characters into the Database.
I believe is is either the Application doing some kind of conversion or a wrong collation on the server hosting the front end application - but unfortunetly I have access to neither.
Then you've done everything you can to identify and resolve the issue. You need to put this back in the lap of the Application Team.
February 25, 2011 at 8:24 am
Thank you!
February 25, 2011 at 8:27 am
Always glad to help... Even if I didn't do much. @=)
February 25, 2011 at 8:34 am
Sometimes it's just nice to have someone to bounce things off of : )
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply