472,958 Members | 2,372 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

Special characters in MSSQL.

Hi,

I have a MSSQL Server communicating with an Oracle database through a
MSSQL linked server using a MS ODBC connection.

If I query the Oracle database through the Oracle ODBC 32Bit Test, the
result is fine:

select addrsurname from address where addrnr = 6666;
H┼KANSSON

If I do the same query within the SQL Query Analyzer (using the linked
server), I get:

select * from openquery(TESTSW, 'select addrsurname from address where
addrnr = 6666');
HĆKANSSON

I have tried to both check and uncheck the Automatic ANSI to OEM
conversion, but the result remains the same.

Does anyone know what to do to make the result display the special
characters in SQL Query Analyzer?
Thanks,
Kenneth

Mar 7 '06 #1
4 5911
Kenneth,

obviously your SQL server and the Oracle DB use a different collation.
In your linked server properties on the tab server_options you can
change the option "use remote collation" from True to False. That
should solve your problem.

Another option would be to convert the character values to
your(preferred) collation like this:
SELECT CAST(addrsurname AS varchar(25)) COLLATE French_CS_AS -- Fill in
your collation here
Good luck
Markus

Mar 7 '06 #2
Hi Markus, and thanks for your response.

I tried both your suggestions without any luck. Do I need to restart
the MSSQL Server service or something?

Thanks,
Kenneth

Mar 7 '06 #3
(ke***************@telenor.com) writes:
I have a MSSQL Server communicating with an Oracle database through a
MSSQL linked server using a MS ODBC connection.

If I query the Oracle database through the Oracle ODBC 32Bit Test, the
result is fine:

select addrsurname from address where addrnr = 6666;
H┼KANSSON

If I do the same query within the SQL Query Analyzer (using the linked
server), I get:

select * from openquery(TESTSW, 'select addrsurname from address where
addrnr = 6666');
H?KANSSON

I have tried to both check and uncheck the Automatic ANSI to OEM
conversion, but the result remains the same.

Does anyone know what to do to make the result display the special
characters in SQL Query Analyzer?


Obviously there is a collation clash of some sort between Oracle and
SQL Server.

I don't know Oracle, but what data type is addrsurname? Is it varchar
or nvarchar (or whatever they may be called on the Oracle side)? If
it is some 8-bit data type, what is the code page for that column?

If I am to guess, I hold the ODBC driver as the prime suspect. Define
the linked server with the MSDAORA provider instead, or even better
Oracle's own OLE DB provider. (MSDAORA does not support Oracle 9 and
10, I believe.)

There are a few options to set for linked servers in SQL Server, but
I don't really think thees are the knobs to pull here.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 7 '06 #4
Hi,

Creating the linked server with Microsoft OLE DB Provider for ODBC
Drivers solved the problem. Thanks for your help.

Cheers,
Kenneth

Mar 8 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Roy W. Andersen | last post by:
I've been searching google about this for days but can't find anything, so I'm hoping someone here can help me out. I'm trying to create zip-files without needing the zip-file extension in PHP,...
3
by: Barry Olly | last post by:
Hi, I'm working on a mini content management system and need help with dealing with special characters. The input are taken from html form which are then stored into a varchar column in...
4
by: Ram | last post by:
We are trying to load text tab delimited files into SQL server using informatica 5.1 version. The text files are FTPed to a local server and then files are moved to applicable folder before...
4
by: Kivanc Toker | last post by:
Hi, I've got a problem with displaying texts with turkish characters. There is a website administration system, which is developed using ASP (VBScript) and ADO. This system is being used for...
5
by: Sakharam Phapale | last post by:
Hi All, I am using an API function, which takes file path as an input. When file path contains special characters (@,#,$,%,&,^, etc), API function gives an error as "Unable to open input file"....
8
by: eugenio | last post by:
Hi...not sure if this is the right group for this posting, but i'm don't know where else to post. I've got a simple problem...I have a linux box running apache 2.0 and php5. I'm trying to use the...
1
by: sonald | last post by:
Dear All, I am working on a module that validates the provided CSV data in a text format, which must be in a predefined format. We check for the : 1. Number of fields provided in the text file,...
3
KevinADC
by: KevinADC | last post by:
Purpose The purpose of this article is to discuss the difference between characters inside a character class and outside a character class and some special characters inside a character class....
3
by: Klaus Herzberg | last post by:
Hi, I come from the "dark side" php/mysql and there often problems with character sets (utf-8, latin...) and storing data in datebase. Exists in the world of dot.net and ms-sql-server similiar...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.