collation conflict

Hi, all
I am doing testing of my application, actualy one Store Procedure at the moment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in char columns.
When I change them to database default setting, the stored procedure works OK.
My question is:
how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
Elizabeta
Elizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:

>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the moment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in char columns.
>When I change them to database default setting, the stored procedure works OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>
|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
[vbcol=seagreen]
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "script
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
ening.
>