December 30, 2008 at 10:49 am
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.
December 30, 2008 at 11:33 am
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
December 30, 2008 at 12:24 pm
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?
December 30, 2008 at 12:53 pm
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
December 30, 2008 at 1:06 pm
Great help -- thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply