Sounds like you may have code page X data in a code page Y database. This
typically happens when an application blindly shoves data into varchar
columns after disabling automatic code page translation. Storing code page
X data in a code page Y database isn't tested or supported and can cause
all sorts of problems. One possible solution is to move the data to a new
database with a collation that matches the data's actual code page:

A) Determine what code page is actually used to encode the data.
Presumably your data is not clean 1252 data, or you wouldn't have these
problems.
B) Script out your database objects (tables/etc) using EM
C) Edit the script and search/replace all of the collations to
whatever you want your new collation to be (you want a collation that
matches the collation of your data -- see the "Windows Collation Name"
topic in BOL for help finding a collation that matches this code page).
D) Apply the script to a new database using Query Analyzer
E) BCP out the source data using a "-CRAW" command line parameter for
BCP (example: bcp sourcedb.dbo.tbl1 out c:\tbl1.bcp -T -CRAW -n). A
"-CRAW" will tell BCP not to skip all code page translation.
F) BCP the data into the new database using -CRAW. Example: bcp
destdb.dbo.tbl1 in c:\tbl1.bcp -T -CRAW -n

------------
Bart Duncan
Microsoft SQL Server Support

Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| Thread-Topic: COLLATION PROBLEM CAN ANY ONE HELP
| thread-index: AcUWScmaNqF71+vmT3WS7Gapc1B/Xw==
| X-WBNR-Posting-Host: 195.229.241.182
| From: "=?Utf-8?B?UmFuaQ==?=" <Rani@discussions.microsoft.com>
| References: <C459D539-898F-4727-8FC4-641FADB8444E@microsoft.com>
<OqM1UxdFFHA.2156@TK2MSFTNGP10.phx.gbl>
<773B1733-68EE-4C15-8422-EBA3ABC424F0@microsoft.com>
<e4vXdRfFFHA.1740@TK2MSFTNGP09.phx.gbl>
| Subject: Re: COLLATION PROBLEM CAN ANY ONE HELP
| Date: Fri, 18 Feb 2005 22:11:02 -0800
| Lines: 26
| Message-ID: <FF8524F3-A22B-4856-8A44-F2402FD8C548@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datamining
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA
03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.datamining:6009
| X-Tomcat-NG: microsoft.public.sqlserver.datamining
|
| Hi adam :
|
| Yes i'm using DTS no am not gettign any error the data is not comming
probaly
|
| thank u
|
[quoted text, click to view]
| > >
| > > Now i have a new database structure to apply and it's in another
collation
| > > so Ican't import the data in the correct shape :((
| >
| > Are you using DTS? Some other tool? And are you getting an error,
or
| > is the data just not coming over properly?
| >
| >
| > --
| > Adam Machanic
| > SQL Server MVP
| > http://www.sqljunkies.com/weblog/amachanic
| > --
| >
| >
| >
|