Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Changing table collation? Expand / Collapse
Author
Message
Posted Tuesday, December 30, 2008 10:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:59 PM
Points: 177, Visits: 549
I have been searching for the syntax to change the collation on a table that I am inserting into another DB for example:

SELECT * INTO dbCurrent..My_new_table FROM dbOld..Old_collated_table

So now I want the My_new_table to be collated to SQL_Latin1_General_CP1_CI_AS what is the correct syntax to make this change.
Post #627484
Posted Tuesday, December 30, 2008 11:33 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
You can use COLLATE clause but it will have to be applied to each character column.

By the way "tables" do not have collation, columns do!



* Noel
Post #627509
Posted Tuesday, December 30, 2008 12:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:59 PM
Points: 177, Visits: 549
So let me get a little more specific here is my code:

update A
set A.f1= B.f1
from db1..table1 A
join db2..table2 B
on A.My_field= B.My_field


The offending field is B.My_field which appears to be causing the collation error (failed with the following error: "Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.)

How do I change the collation on the fly -- please post code?
Post #627539
Posted Tuesday, December 30, 2008 12:53 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
Mark Fyffe (12/30/2008)
So let me get a little more specific here is my code:

update A
set A.f1= B.f1
from db1..table1 A
join db2..table2 B
on A.My_field= B.My_field


The offending field is B.My_field which appears to be causing the collation error (failed with the following error: "Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.)

How do I change the collation on the fly -- please post code?


update A
set A.f1= B.f1
from db1..table1 A
join db2..table2 B
on A.My_field= B.My_field COLLATE SQL_Latin1_General_CP1_CI_AS

Note that "f1" could also need the "fix" if it is a character column with different collation attributes.

Hope it helps,




* Noel
Post #627564
Posted Tuesday, December 30, 2008 1:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:59 PM
Points: 177, Visits: 549
Great help -- thanks
Post #627575
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse