Solved

SQL Server Collation Problem

Posted on 2010-11-22
9
637 Views
Last Modified: 2012-05-10
Hello experts,

I'm facing the following  problem:
I have a website wich uses a SQL Server DB. I had it in a server with SQL_Latin1_General_CP1253_CI_AS collation. I recently transferred it in another server with SQL_Latin1_General_CP1_CI_AS collation. In first I had problems with Greek text, but with changing collations I managed to see right data in SQL Server environment. The problem is when I see in browser Greek text coming from temporary tables results.
What I mean is that when I browse the data of my SQL DB, all are right. When I browse data from tempdb I have ????? instead of Greek text.

Any ideas??

Thank Sotiris
0
Comment
Question by:Soter13
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 250 total points
ID: 34186564
Within SQL Server the collation are applicable in 2 place.

The database and the instance. The instance itself has a collation (Master,model,msdb) If you create a new database with a different collation then when you build temp tables they will always be in the instance collation.

One option to fix this would be to specify the collation with the create table statements so that it is created correctly. The other would be to reinstall the instance with the correct collation.
0
 

Author Comment

by:Soter13
ID: 34186585
I' ve changed the collation within the table columns and the problem fixed when I see the results in the Management Studio environment.
The problem persists when I browse the data on IE or other browsers
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34186620
are you using codepage=1253 in your page

and CHARSET
<META HTTP-EQUIV="Content-Type"
  CONTENT="text/html; CHARSET=windows-1253">
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34186623
Is the instance and the database the same collation now?
0
 

Author Comment

by:Soter13
ID: 34186639
To AmmarR: I use windows-1253

To EvilPostIt: No, I just changed the collation on table columns. The instance collation is dificult to change because I host the site in a shared hosting
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34186649
are you storing the values in an Nvarchar field
0
 

Author Comment

by:Soter13
ID: 34186666
I 've tried that too, but internet browsers insists on ?????
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34186695
Ok so you have
your field in Nvarchar
your table collation 1253
Your Database Collation 1253
your Code page 1253
your CHARSET = windows-1253
.

and you still getting ????

i suggest try removing one record and after having all the above set correctly insert a single record in your table using the N
and test it in you browser

like example

Insert into Table (Greekfield) Values (N'Greekvalue')

and try view this record.
0
 
LVL 6

Assisted Solution

by:subhashpunia
subhashpunia earned 250 total points
ID: 34187270
There is a precedence order of collation that I have explained here:

http://sqlreality.com/blog/ms-sql-server-2008/collation-precedence-rules/
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question