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.
>

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 happening.
I am very grateful for any helpful information.
TIA
ElizabetaElizabeta,
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 happening.
>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:
> 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 happening.
> >
> >I am very grateful for any helpful information.
> >
> >TIA
> >
> >Elizabeta
> >
> >
>

collation conflict

Hi, all
I am doing testing of my application, actualy one Store Procedure at the mo
ment.
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 cha
r columns.
When I change them to database default setting, the stored procedure works O
K.
My question is:
how can I avoid to have collation in new copied tables, although the databas
es have it the same. I tried to copy using CREATE TABLE scripts or DTS servi
ces, copy as objects, always the same problem. Is there any setting on SQL s
erver to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
ElizabetaElizabeta,
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 m
oment.
>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 ch
ar 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 ob
jects, always the same problem. Is there any setting on SQL server to avoid this hap
pen
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:

> 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 "scri
pt
> 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.[vbcol=seagreen]
>sqlsql

Collation

Hi,
I'm designing a new SQL 2000 DB and now I don't know what collation setting
to use.
The DB will store data for multiple languages ex. Japanese, English, Swedish
etc.
I'v looked at selecting SQL_Latin1_General_CP850_CI_AS, but is this the way
to go?
Any help on this would be appreciated.
Regards,
GoranGoran
Yes , it is ok.
"Goran Dzelovski" <info@.goran.se> wrote in message
news:umwROdDKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm designing a new SQL 2000 DB and now I don't know what collation
setting
> to use.
> The DB will store data for multiple languages ex. Japanese, English,
Swedish
> etc.
> I'v looked at selecting SQL_Latin1_General_CP850_CI_AS, but is this the
way
> to go?
> Any help on this would be appreciated.
> Regards,
> Goran
>|||Hi Goran,
Another way of implementing Multi Language databases is to
use as much as possible unicode columns, nchar, nvarchar &
ntext, which are language independant.
J
>--Original Message--
>Hi,
>I'm designing a new SQL 2000 DB and now I don't know what
collation setting
>to use.
>The DB will store data for multiple languages ex.
Japanese, English, Swedish
>etc.
>I'v looked at selecting SQL_Latin1_General_CP850_CI_AS,
but is this the way
>to go?
>Any help on this would be appreciated.
>Regards,
>Goran
>
>.
>|||In addition to the other posts:
The problem is with sorting. The data is stored with a collation, this is true for Unicode as well. A
collation specifies, among other things, sorting and comparison rules. These rules are language specific. If
you want the app to behave correctly for multiple languages for these things, you need to add a COLLATE clause
for your queries that matches the language that the user prefers.
More info:
http://msdn.microsoft.com/library/techart/intlfeaturesinsqlserver2000.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Goran Dzelovski" <info@.goran.se> wrote in message news:umwROdDKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm designing a new SQL 2000 DB and now I don't know what collation setting
> to use.
> The DB will store data for multiple languages ex. Japanese, English, Swedish
> etc.
> I'v looked at selecting SQL_Latin1_General_CP850_CI_AS, but is this the way
> to go?
> Any help on this would be appreciated.
> Regards,
> Goran
>|||Ok, thanks a lot for your help.
/Goran

Collation

Hi,
I'm designing a new SQL 2000 DB and now I don't know what collation setting
to use.
The DB will store data for multiple languages ex. Japanese, English, Swedish
etc.
I'v looked at selecting SQL_Latin1_General_CP850_CI_AS, but is this the way
to go?
Any help on this would be appreciated.
Regards,
Goran
Goran
Yes , it is ok.
"Goran Dzelovski" <info@.goran.se> wrote in message
news:umwROdDKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm designing a new SQL 2000 DB and now I don't know what collation
setting
> to use.
> The DB will store data for multiple languages ex. Japanese, English,
Swedish
> etc.
> I'v looked at selecting SQL_Latin1_General_CP850_CI_AS, but is this the
way
> to go?
> Any help on this would be appreciated.
> Regards,
> Goran
>
|||In addition to the other posts:
The problem is with sorting. The data is stored with a collation, this is true for Unicode as well. A
collation specifies, among other things, sorting and comparison rules. These rules are language specific. If
you want the app to behave correctly for multiple languages for these things, you need to add a COLLATE clause
for your queries that matches the language that the user prefers.
More info:
http://msdn.microsoft.com/library/te...server2000.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Goran Dzelovski" <info@.goran.se> wrote in message news:umwROdDKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm designing a new SQL 2000 DB and now I don't know what collation setting
> to use.
> The DB will store data for multiple languages ex. Japanese, English, Swedish
> etc.
> I'v looked at selecting SQL_Latin1_General_CP850_CI_AS, but is this the way
> to go?
> Any help on this would be appreciated.
> Regards,
> Goran
>
|||Ok, thanks a lot for your help.
/Goran
sqlsql

Collation

Hi,
I'm designing a new SQL 2000 DB and now I don't know what collation setting
to use.
The DB will store data for multiple languages ex. Japanese, English, Swedish
etc.
I'v looked at selecting SQL_Latin1_General_CP850_CI_AS, but is this the way
to go?
Any help on this would be appreciated.
Regards,
GoranGoran
Yes , it is ok.
"Goran Dzelovski" <info@.goran.se> wrote in message
news:umwROdDKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm designing a new SQL 2000 DB and now I don't know what collation
setting
> to use.
> The DB will store data for multiple languages ex. Japanese, English,
Swedish
> etc.
> I'v looked at selecting SQL_Latin1_General_CP850_CI_AS, but is this the
way
> to go?
> Any help on this would be appreciated.
> Regards,
> Goran
>|||In addition to the other posts:
The problem is with sorting. The data is stored with a collation, this is tr
ue for Unicode as well. A
collation specifies, among other things, sorting and comparison rules. These
rules are language specific. If
you want the app to behave correctly for multiple languages for these things
, you need to add a COLLATE clause
for your queries that matches the language that the user prefers.
More info:
http://msdn.microsoft.com/library/t...lserver2000.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Goran Dzelovski" <info@.goran.se> wrote in message news:umwROdDKEHA.1192@.TK2MSFTNGP11.phx.gb
l...
> Hi,
> I'm designing a new SQL 2000 DB and now I don't know what collation settin
g
> to use.
> The DB will store data for multiple languages ex. Japanese, English, Swedi
sh
> etc.
> I'v looked at selecting SQL_Latin1_General_CP850_CI_AS, but is this the wa
y
> to go?
> Any help on this would be appreciated.
> Regards,
> Goran
>|||Ok, thanks a lot for your help.
/Goran

Coalesce

Hi.

I have a piece of a store procedure I don't quite understand, as follows:

SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%'))

What exactly is the COALESCE function doing here with the parameter?Returns the first nonnull expression among its arguments.|||Ok,

What does that mean? it returns the first parameter that is null or it returns the first record that is null.|||Originally posted by SQLSlammer
Ok,

What does that mean? it returns the first parameter that is null or it returns the first record that is null.

declare @.id int,@.id2 int
set @.id=null
set @.id2=1
select COALESCE(@.id,@.id2)

It returns the first parameter that is not null...

WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%'))

In your query COALESCE(@.DealReference,'%')) means that if @.DealReference is null then COALESCE returns '%' and finally it is LIKE '%'.|||coalesce returns the first non-null value in its list

in your particular example, it's a sloppy stored proc

if the parameter @.DealReference is null, then the sql statement will actually look like this:

... where d.DealReference LIKE (COALESCE(null,'%'))

but in that case, since it's a stored proc, it should not generate a WHERE clause at all

furthermore, since it's a LIKE condition, the @.DealReference has better include the wildcard characters, otherwise it will function as an equality

again, the stored proc should tailor the sql specifically|||Hi,

Thanks for that,
so basically it will return all records if the parameter is null, what would be a beter way of handling a null parameter?|||what would be a better way of handling a null parameter?

don't generate the WHERE clause

that, too, will return all records|||Hi,

Ok, I should explain a little more, there is lot more to this sp than I have shown, there are 7 parameters for filtering and I really don't want it to return all records cos that would be over 7 million records into the temp table which is pointless:

The full SP is attached and yes I know its not very good (I did not write this).

Any advice would be appreciated very much|||Originally posted by SQLSlammer
Hi,

Ok, I should explain a little more, there is lot more to this sp than I have shown, there are 7 parameters for filtering and I really don't want it to return all records cos that would be over 7 million records into the temp table which is pointless:

The full SP is attached and yes I know its not very good (I did not write this).

Any advice would be appreciated very much

Well, there is bad with using coalesce in this proc. I guess you could change a little logic in your proc:

You are inserting more records in table @.Deals than late you selecting from it because in select you are using more filters then for insert. You could use all filters during inserting - it will be more complicated query but it is worth to try.

INSERT @.Deals (DealReference, DealId, IllustrationId, ContactId)\par
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId\par
FROM utDeal d WITH (NOLOCK)\par
\tab INNER JOIN utContactIllustration ci WITH (NOLOCK)\par
\tab\tab ON ci.IllustrationId = d.IllustrationId\par
WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%')) \par

SELECT DISTINCT * FROM @.deals \par
WHERE \tab\par
\tab DealReference LIKE (COALESCE(@.DealReference,'%')) \par
AND\par
\tab UserName LIKE (COALESCE(@.UserName, '%'))\par
AND\par
\tab LastAction LIKE (COALESCE(@.ActionType, '%'))\par
AND\par
\tab PostCode LIKE (COALESCE(@.PostCode, '%'))\par
AND\par
\tab CurrentStage LIKE (COALESCE(@.Stage, '%'))\par
AND\par
\tab Surname LIKE (COALESCE(@.Surname, '%'))\par
AND\par
\tab Telephone LIKE (COALESCE(@.Telephone, '%'))\par
ORDER BY DealId\par|||you know your data better than i do, but the relevant statement in your proc is

INSERT @.Deals (DealReference, DealId, IllustrationId, ContactId)
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%'))

so if @.DealReference is null, it will bring back all rows that satisfy that join, whether that's 7 million, i don't know, but it could be