April 20, 2007 at 1:32 pm
I am trying to compare data between sql7 and Oracle db's and I am getting different results for 2 different sql servers that I believe are configured the same. There are some fields that I
am comparing that have special characters that do not seem to be translating the same between the two SQLServers. When I query the Oracle db using a linked server on SQLServerA, the data is returned as (Saint-Saëns). However, if I query the OracleDB using SQLServerB, the result is (Saint-Saens) without the umlaut. Is there a configuration setting that I have overlooked on the SQLServer B that can correct this issue? What could be the difference between my 2 SQLServer DB's?
SQLServers A & B
v.7
CP 1252
OS Win2000
NLS CodePage OEMCP 1252
Oracle client
AMERICAN_AMERICA.WE8MSWIN1252
Thanks,
Todd
April 28, 2007 at 10:38 am
The reason is little known fact about SQL Server 7.0 Unicode require reinstall of SQL Server and it ends on table level so search results cannot be compared to what you get in 2000/2005. If you can with the above name you need to upgrade the database to 2000 or 2005 so you will get more granular Unicode definitions, on database, table and column in 2000 and also in DML in 2005. In SQL Server 2000/2005 SQL Server Unicode is better than Oracle because Oracle and IBM supports both Unicode 8 and 16 while SQL Server supports UCS-2 which is similar to UTF16 and not UTF8. Post again if you have more questions. Hope this helps.
Kind regards,
Gift Peddie
April 28, 2007 at 7:03 pm
I appreciate the help. That suggestion probably would work, but for other reasons I need to keep the server at sql7.
Anyways, I was able to resolve the situation by looking further at my Oracle client installations and comparing them between sqlServerA and B. I noticed that on the new server I installed a few extra components than what I had set up on the first one. So I deinstalled the extra components on the second server to match what I had on the first, rebooted and problem fixed. One of the pieces that I deinstalled was the instant client ODBC, which may have been the culprit. It may have had different nls settings. not entirely sure.
April 28, 2007 at 7:15 pm
I am glad to see you have solved the problem.
Kind regards,
Gift Peddie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply