Copying data between databases with different collations

I have written a system which I now want to intergrate to some degree
with with Syspro accounting software, both run off MS SQL databases on
the same server.
I am writing a trigger on the syspro database,
I need to join across the 2 DBs, each have different collations
When I try:
Insert INTO DB1.dbo.Table1 (F1,F2.....)
Select (View1.Field1, View1.Field2.....)
>From DB2.dbo.View1 where [condition]
I get the following error "Cannot resolve collation conflict for
replace operation."
is there some way to convert the values from the view to allow them to
be inserted?
Syspro database is using : SQL_Latin1_General_CP437_BIN
And mine is using : SQL_Latin1_General_CP1_CI_ASHave a look at Collate database_default in SQL Books Online
Jump To URL :- tsqlref.chm::/ts_ca-co_5z55.htm
HTH. Ryan
<cameron.waldron@.gmail.com> wrote in message
news:1139489197.088750.109310@.g44g2000cwa.googlegroups.com...
>I have written a system which I now want to intergrate to some degree
> with with Syspro accounting software, both run off MS SQL databases on
> the same server.
> I am writing a trigger on the syspro database,
> I need to join across the 2 DBs, each have different collations
> When I try:
> Insert INTO DB1.dbo.Table1 (F1,F2.....)
> Select (View1.Field1, View1.Field2.....)
> I get the following error "Cannot resolve collation conflict for
> replace operation."
> is there some way to convert the values from the view to allow them to
> be inserted?
> Syspro database is using : SQL_Latin1_General_CP437_BIN
> And mine is using : SQL_Latin1_General_CP1_CI_AS
>|||Thanks, the problem was that I was in the view I was querying on my
database (it had a replace statement in it which was the problem, hence
the "Cannot resolve collation conflict for replace operation." Error) I
brought the view from my db to the syspro db and collated fields in the
view as I needed with collate database_default, problem fixed :-)