collation name

Is there any way, I can query across all objects in a given database for what is the current collation name for each column in an object?select [Object_Name]=object_name(id),
[Column Name] = name,
[Collation] = collation
from syscolumns
where objectproperty(id, 'ismsshipped') = 0
order by object_name(id), colid

Collation Name

Is it possible to find out when the collation was changed on a database?
Thanks in advance
Hi,
No we cant. SQL Server will not store those information.
Thanks
Hari
SQL Server MVP
"david" wrote:

> Is it possible to find out when the collation was changed on a database?
> Thanks in advance
|||Hari
Thanks for the fast repsonce
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> No we cant. SQL Server will not store those information.
> Thanks
> Hari
> SQL Server MVP
> "david" wrote:

Collation Name

Is it possible to find out when the collation was changed on a database?
Thanks in advanceHi,
No we cant. SQL Server will not store those information.
Thanks
Hari
SQL Server MVP
"david" wrote:

> Is it possible to find out when the collation was changed on a database?
> Thanks in advance|||Hari
Thanks for the fast repsonce
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> No we cant. SQL Server will not store those information.
> Thanks
> Hari
> SQL Server MVP
> "david" wrote:
>

Collation Name

Is it possible to find out when the collation was changed on a database?
Thanks in advanceHi,
No we cant. SQL Server will not store those information.
Thanks
Hari
SQL Server MVP
"david" wrote:
> Is it possible to find out when the collation was changed on a database?
> Thanks in advance|||Hari
Thanks for the fast repsonce
"Hari Prasad" wrote:
> Hi,
> No we cant. SQL Server will not store those information.
> Thanks
> Hari
> SQL Server MVP
> "david" wrote:
> > Is it possible to find out when the collation was changed on a database?
> >
> > Thanks in advance

Collation matter

Dear all,
We've got one db allocated in a SQL SERVER 7.0 and another one with
compatibility with 6.5 and now we want to move it to SQL SERVER 2000.
Change collation at server/db is easy, but problem is for all the columns.
Does anyone have any script for do that automatically?
Thanks a lot for any comment,See if this post is of any help:
http://groups.google.co.uk/group/mi...l=en&lr=&rnum=2
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:0487C7B1-398D-4A9A-824A-88B5A12DA542@.microsoft.com...
Dear all,
We've got one db allocated in a SQL SERVER 7.0 and another one with
compatibility with 6.5 and now we want to move it to SQL SERVER 2000.
Change collation at server/db is easy, but problem is for all the columns.
Does anyone have any script for do that automatically?
Thanks a lot for any comment,sqlsql

collation issues

hello,
i have a sql script/batch that i run against a db every day on my
workstation w/o a problem. recently i tried running it on my laptop
(replication of usual db)
i get an error on one select statement. the statement is a join b/t two
tables, on a field of type varchar(256) . i've tried both like and = as the
operator. one of the fields may actually be of type char(16) -- regardless,
this join always works on my desktop.
on my laptop, the text of the error message is:
Cannot resolve collation conflict for equal to operation
(or when i use the like operator)
Cannot resolve collation conflict for like operation
guessing this has to do w/ some collation setting for my sql server instance
on my laptop, but don't know. also, the sql server on my laptop is
development edition, while on my desktop it's enterprise edition -- don't
know if that matters
thanks for any help
matthewIt sounds as if the collation on the two columns is different. Use QA to scr
ipt
the Create Table statement to the clipboard and paste it into a message. The
re
is a way to coerce one collation into another if you know the collations on
the
columns.
Thomas
"matthew c. harad" <matthewcharad@.discussions.microsoft.com> wrote in messag
e
news:51C9A1FA-0DD3-44C8-AD49-309B5637CC24@.microsoft.com...
> hello,
> i have a sql script/batch that i run against a db every day on my
> workstation w/o a problem. recently i tried running it on my laptop
> (replication of usual db)
> i get an error on one select statement. the statement is a join b/t two
> tables, on a field of type varchar(256) . i've tried both like and = as t
he
> operator. one of the fields may actually be of type char(16) -- regardles
s,
> this join always works on my desktop.
> on my laptop, the text of the error message is:
> Cannot resolve collation conflict for equal to operation
> (or when i use the like operator)
> Cannot resolve collation conflict for like operation
> guessing this has to do w/ some collation setting for my sql server instan
ce
> on my laptop, but don't know. also, the sql server on my laptop is
> development edition, while on my desktop it's enterprise edition -- don't
> know if that matters
> thanks for any help
> matthew|||Check the collation_name of both columns from information_schema.columns and
use COLLATE to force the collations to be the same.
Example:
use northwind
go
create table t1 (
c1 char(10) collate SQL_Latin1_General_CP1_CI_AS
)
go
create table t2 (
c1 char(10) collate SQL_Latin1_General_CP1_CS_AS
)
go
insert into t1 values('microsoft')
insert into t2 values('Microsoft')
go
-- will give an error
select
*
from
t1 inner join t2
on t1.c1 = t2.c1
go
select
*
from
t1 inner join t2
on t1.c1 = t2.c1 collate SQL_Latin1_General_CP1_CI_AS
go
drop table t1, t2
go
AMB
"matthew c. harad" wrote:

> hello,
> i have a sql script/batch that i run against a db every day on my
> workstation w/o a problem. recently i tried running it on my laptop
> (replication of usual db)
> i get an error on one select statement. the statement is a join b/t two
> tables, on a field of type varchar(256) . i've tried both like and = as t
he
> operator. one of the fields may actually be of type char(16) -- regardles
s,
> this join always works on my desktop.
> on my laptop, the text of the error message is:
> Cannot resolve collation conflict for equal to operation
> (or when i use the like operator)
> Cannot resolve collation conflict for like operation
> guessing this has to do w/ some collation setting for my sql server instan
ce
> on my laptop, but don't know. also, the sql server on my laptop is
> development edition, while on my desktop it's enterprise edition -- don't
> know if that matters
> thanks for any help
> matthew

Collation issue?

Hi,
I am having problems querying some czech letters (or maybe it is
inserting that fails?).
Here is the deal:
We have a web application that supports different languages. We have
stored the different messages to be displayed into a nvarchar column
with the SQL_Danish_Pref_CP1_CI_AS collation.
resource_ID
int
langID
int
data nvarchar(4000 )
Then I update the following row into the table:
update usr_data set data='DotaznXk je vyplněn!' where langid=5 and
resource_id=7
When querying the updated value I get
resource_ID langID data
7 5 DotaznXk je vyplnen!
See the missing upside-down ^ over the letter e in the last word.
I have also tested this on collation Latin1_General_CI_AS and
Czech_CI_AS with the same result.
I created a new table with a nvarchar column with the above collation
and inserted the same text, but the result was the same.
What am I missing here?
Thanks,
> Then I update the following row into the table:
> update usr_data set data='DotaznXk je vyplněn!' where langid=5 and
> resource_id=7
For a Unicode constant, prefix the literal with N:
UPDATE dbo.usr_data
SET data = N'DotaznXk je vyplněn!'
WHERE
langid = 5 AND
resource_id = 7
Note that Unicode allows all Unicode characters to be stored. The Unicode
collation affects only sorting and comparison.
Hope this helps.
Dan Guzman
SQL Server MVP
"gurbao" <audunj@.gmail.com> wrote in message
news:1181645527.452234.163220@.x35g2000prf.googlegr oups.com...
Hi,
I am having problems querying some czech letters (or maybe it is
inserting that fails?).
Here is the deal:
We have a web application that supports different languages. We have
stored the different messages to be displayed into a nvarchar column
with the SQL_Danish_Pref_CP1_CI_AS collation.
resource_ID
int
langID
int
data nvarchar(4000 )
Then I update the following row into the table:
update usr_data set data='DotaznXk je vyplněn!' where langid=5 and
resource_id=7
When querying the updated value I get
resource_ID langID data
7 5 DotaznXk je vyplnen!
See the missing upside-down ^ over the letter e in the last word.
I have also tested this on collation Latin1_General_CI_AS and
Czech_CI_AS with the same result.
I created a new table with a nvarchar column with the above collation
and inserted the same text, but the result was the same.
What am I missing here?
Thanks,
|||Thanks a lot!
That did the trick.
Only thing I wonder now, is how I could forget :-)
Regards,
On Jun 12, 1:02Xpm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> For a Unicode constant, prefix the literal with N:
> UPDATE dbo.usr_data
> SET data = N'DotaznXk je vyplněn!'
> WHERE
> X X langid = 5 AND
> X X resource_id = 7
> Note that Unicode allows all Unicode characters to be stored. XThe Unicode
> collation affects only sorting and comparison.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "gurbao" <aud...@.gmail.com> wrote in message
> news:1181645527.452234.163220@.x35g2000prf.googlegr oups.com...
> Hi,
> I am having problems querying some czech letters (or maybe it is
> inserting that fails?).
> Here is the deal:
> We have a web application that supports different languages. We have
> stored the different messages to be displayed into a nvarchar column
> with the SQL_Danish_Pref_CP1_CI_AS collation.
> resource_ID
> int
> langID
> int
> data X X X X X X nvarchar(4000 )
> Then I update the following row into the table:
> update usr_data set data='DotaznXk je vyplněn!' where langid=5 and
> resource_id=7
> When querying the updated value I get
> resource_ID langID X X Xdata
> X X X X X 7 X X X X X 5DotaznXk je vyplnen!
> See the missing upside-down ^ over the letter e in the last word.
> I have also tested this on collation Latin1_General_CI_AS and
> Czech_CI_AS with the same result.
> I created a new table with a nvarchar column with the above collation
> and inserted the same text, but the result was the same.
> What am I missing here?
> Thanks,
|||> Only thing I wonder now, is how I could forget :-)
I'm glad I was able to help. I think you'll remember the 'N' the next time
;-)
Dan Guzman
SQL Server MVP
"gurbao" <audunj@.gmail.com> wrote in message
news:1181646750.111306.272120@.z28g2000prd.googlegr oups.com...
Thanks a lot!
That did the trick.
Only thing I wonder now, is how I could forget :-)
Regards,
On Jun 12, 1:02 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> For a Unicode constant, prefix the literal with N:
> UPDATE dbo.usr_data
> SET data = N'DotaznXk je vyplněn!'
> WHERE
> langid = 5 AND
> resource_id = 7
> Note that Unicode allows all Unicode characters to be stored. The Unicode
> collation affects only sorting and comparison.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "gurbao" <aud...@.gmail.com> wrote in message
> news:1181645527.452234.163220@.x35g2000prf.googlegr oups.com...
> Hi,
> I am having problems querying some czech letters (or maybe it is
> inserting that fails?).
> Here is the deal:
> We have a web application that supports different languages. We have
> stored the different messages to be displayed into a nvarchar column
> with the SQL_Danish_Pref_CP1_CI_AS collation.
> resource_ID
> int
> langID
> int
> data nvarchar(4000 )
> Then I update the following row into the table:
> update usr_data set data='DotaznXk je vyplněn!' where langid=5 and
> resource_id=7
> When querying the updated value I get
> resource_ID langID data
> 7 5 DotaznXk je vyplnen!
> See the missing upside-down ^ over the letter e in the last word.
> I have also tested this on collation Latin1_General_CI_AS and
> Czech_CI_AS with the same result.
> I created a new table with a nvarchar column with the above collation
> and inserted the same text, but the result was the same.
> What am I missing here?
> Thanks,