Entity ’ in SQL Server 2000 output

I have an application that queries a SQL Server 2000 db through an ODBC driver on my Win2000 machine.

The DB contains some french text e.g. ltranger stored in a nvarchar column.

When I query the DB the result contains the entity &# 8217; rather than the character itself. I don't want this and it doesn't happen in SQL Server 7.

Tried changing the collation from SQL_Latin1_General_Cp1_CI_AS but with no luck.

Does anyone have experience with this?

ThanksHi,
Did you change collation on the entire dB or on table and column ?
Which collation do you have as standard ?

Regards
Tommy|||Hi,

We've changed the collation of the individual dB/table, but not
the collation of the server (which is still SQL_Latin1_General_CP1_CI_AS), so as not to affect other dBs.

Is it likely to be a collation issue?|||Hi,

I'm not sure about that. I've tried to insert that text into a table with collation SQL_Latin1_General_CP1_CI_AS and the result in query analyzer is correct, have you tried select "column" from "table".
Is the result the same ?
Where did the dB data come from ?
You may check how it's inserted into the dB.

Let me know !

Tommy|||Hi,

Thanks for taking an interest.

The original data is probably generated in some Microsoft applications i.e. Word. I have no control over that.

Yes, the results look okay in query analyser, the problem occurs when I do SQL calls through an ODBC driver from a remote machine.
I don't think it's a driver problem because I get the correct characters when testing on SQL Server 7.

What has changed between 7 and 2000?

Help|||Ok,
That's a tricky one..
One of the things that changed in SQL 2000 is collations (the possibility to set collations on dB, tables and columns not just on the server).
and sometimes just change the collation on the table or columns or only the dB just ain't enough.

Some things you can do is:
Check the application, how/what does it when querying the dB, how do the question look like. (run SQL profiler) if you can't look in the code.

The collation on SQL 7 ?

How did you do the switch from SQL 7 to 2000 ?

Regional settings on the client. (? ? I'm not sure about this)

Anyone else have a succession ?

I can send you a proc that handles collations if you'll like.
Regards
Tommy|||Hi,

Basically we just send an SQLExecute command with a SELECT statement.

Of course I can parse out the & #8217 from the results but surely I shouldn't need to? (And also what about any other entities I may encounter?)

Anyway, thanks for the suggestions