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

Enterprise Managers "current activity":How in SQL?

Hi All !
Can someone show, how to obtain that, what Enterprise manager calls "current
activity", in SQL? I really would like to see the same output, not that,
what sp_who, sp_lock etc give.
I just have currently not the right knowledge to make all the neccessary
relationships from sp_who/sp_lock/sysprocesses etc
Any help would be great.
Thanks so far and
best regards,
Manfred BraunManfred Braun wrote:
> Hi All !
> Can someone show, how to obtain that, what Enterprise manager calls "current
> activity", in SQL? I really would like to see the same output, not that,
> what sp_who, sp_lock etc give.
> I just have currently not the right knowledge to make all the neccessary
> relationships from sp_who/sp_lock/sysprocesses etc
> Any help would be great.
> Thanks so far and
> best regards,
> Manfred Braun
>
Hi Manfred
You could try to run a trace while you open "Current activity". This
would most likely reveal which sql statement EM is running to view that
info.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Also, take a look at this procedure by Erland:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
HTH
Kalen Delaney, SQL Server MVP
"Steen Persson (DK)" <steen@.asavaengetREMOVETHIS.dk> wrote in message
news:OllyWgkjGHA.836@.TK2MSFTNGP02.phx.gbl...
> Manfred Braun wrote:
>> Hi All !
>> Can someone show, how to obtain that, what Enterprise manager calls
>> "current activity", in SQL? I really would like to see the same output,
>> not that, what sp_who, sp_lock etc give.
>> I just have currently not the right knowledge to make all the neccessary
>> relationships from sp_who/sp_lock/sysprocesses etc
>> Any help would be great.
>> Thanks so far and
>> best regards,
>> Manfred Braun
>>
> Hi Manfred
> You could try to run a trace while you open "Current activity". This would
> most likely reveal which sql statement EM is running to view that info.
>
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator