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

Entities Disappear in Ad Hoc Reports

I have a single data source view which contains 3 tables. Each table is a
completely denormalized (like a spreadsheet) dataset from a particular
business system. The tables themselves don't contain any foreign key
relationships, but within the data source view editor I created primary keys
on each table and created relationships between each table. However, when I
try to create a report in report builder, as soon as add an entity from one
table to my report, the other 2 tables disappear. I have found a few other
posts about this problem and the resolutions offered were (1) add a "refer",
but I don't know what that is; and (2) create or add relationships, but I
believe I have done that. What do I have to do to prevent the tables from
disappearing? Thanks.
Also, is there a good resource for reading up on Ad-Hoc reporting? The
books I have seen only cover it briefly.I figured this out. I need to add a role to each table in the Report Model
Designer. It is all in Books Online.
"Bodhicitta" wrote:
> I have a single data source view which contains 3 tables. Each table is a
> completely denormalized (like a spreadsheet) dataset from a particular
> business system. The tables themselves don't contain any foreign key
> relationships, but within the data source view editor I created primary keys
> on each table and created relationships between each table. However, when I
> try to create a report in report builder, as soon as add an entity from one
> table to my report, the other 2 tables disappear. I have found a few other
> posts about this problem and the resolutions offered were (1) add a "refer",
> but I don't know what that is; and (2) create or add relationships, but I
> believe I have done that. What do I have to do to prevent the tables from
> disappearing? Thanks.
> Also, is there a good resource for reading up on Ad-Hoc reporting? The
> books I have seen only cover it briefly.
>