Check collations

Hello,
How can i check server collation an if any database table
or column as a different collation?
Best regardsHi
Look at INFORMATION_SCHEMA.COLUMNS view to retrieve information about
collation.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8fea01c47888$8e764ee0$a501280a@.phx.gbl...
> Hello,
> How can i check server collation an if any database table
> or column as a different collation?
> Best regards|||And to check the default collation property for the server
select serverproperty('collation')
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8fea01c47888$8e764ee0$a501280a@.phx.gbl...
> Hello,
> How can i check server collation an if any database table
> or column as a different collation?
> Best regards|||I'm not 100% sure what you're asking.
SERVERPROPERTY ( propertyname )
DATABASEPROPERTYEX( database , property )
and for columns...
sp_help [ [ @.objname = ] name ]
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8fea01c47888$8e764ee0$a501280a@.phx.gbl...
> Hello,
> How can i check server collation an if any database table
> or column as a different collation?
> Best regards

Check collations

Hello,
How can i check server collation an if any database table
or column as a different collation?
Best regards
Hi
Look at INFORMATION_SCHEMA.COLUMNS view to retrieve information about
collation.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8fea01c47888$8e764ee0$a501280a@.phx.gbl...
> Hello,
> How can i check server collation an if any database table
> or column as a different collation?
> Best regards
|||And to check the default collation property for the server
select serverproperty('collation')
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8fea01c47888$8e764ee0$a501280a@.phx.gbl...
> Hello,
> How can i check server collation an if any database table
> or column as a different collation?
> Best regards
|||I'm not 100% sure what you're asking.
SERVERPROPERTY ( propertyname )
DATABASEPROPERTYEX( database , property )
and for columns...
sp_help [ [ @.objname = ] name ]

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8fea01c47888$8e764ee0$a501280a@.phx.gbl...
> Hello,
> How can i check server collation an if any database table
> or column as a different collation?
> Best regards

Check collations

Hello,
How can i check server collation an if any database table
or column as a different collation?
Best regardsHi
Look at INFORMATION_SCHEMA.COLUMNS view to retrieve information about
collation.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8fea01c47888$8e764ee0$a501280a@.phx.gbl...
> Hello,
> How can i check server collation an if any database table
> or column as a different collation?
> Best regards|||And to check the default collation property for the server
select serverproperty('collation')
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8fea01c47888$8e764ee0$a501280a@.phx.gbl...
> Hello,
> How can i check server collation an if any database table
> or column as a different collation?
> Best regards|||I'm not 100% sure what you're asking.
SERVERPROPERTY ( propertyname )
DATABASEPROPERTYEX( database , property )
and for columns...
sp_help [ [ @.objname = ] name ]
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8fea01c47888$8e764ee0$a501280a@.phx.gbl...
> Hello,
> How can i check server collation an if any database table
> or column as a different collation?
> Best regards

Check all existing collations in one database.

Hello,
Can you tell me, how can i get all collations that i have in one database. I
get the database collation but i need to verify if any table or field in the
current database have one different collation.
I've got one job that is returning the following error:
Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
Implicit conversion of char value to char cannot be performed because the
collation of the value is unresolved due to a collation conflict.
Thanks and best regars.
The following will list columns in tables and views that have a collation
other than the current database default:
SELECT
o.type,
USER_NAME(o.uid),
o.name,
c.name
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
WHERE
o.type IN('U', 'V') AND
c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
> Hello,
> Can you tell me, how can i get all collations that i have in one database.
> I
> get the database collation but i need to verify if any table or field in
> the
> current database have one different collation.
> I've got one job that is returning the following error:
> Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
> Implicit conversion of char value to char cannot be performed because the
> collation of the value is unresolved due to a collation conflict.
> Thanks and best regars.
|||Thanks Dan,
Best regards
"Dan Guzman" wrote:

> The following will list columns in tables and views that have a collation
> other than the current database default:
> SELECT
> o.type,
> USER_NAME(o.uid),
> o.name,
> c.name
> FROM sysobjects o
> JOIN syscolumns c ON c.id = o.id
> WHERE
> o.type IN('U', 'V') AND
> c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
>
>

Check all existing collations in one database.

Hello,
Can you tell me, how can i get all collations that i have in one database. I
get the database collation but i need to verify if any table or field in the
current database have one different collation.
I've got one job that is returning the following error:
Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
Implicit conversion of char value to char cannot be performed because the
collation of the value is unresolved due to a collation conflict.
Thanks and best regars.The following will list columns in tables and views that have a collation
other than the current database default:
SELECT
o.type,
USER_NAME(o.uid),
o.name,
c.name
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
WHERE
o.type IN('U', 'V') AND
c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
> Hello,
> Can you tell me, how can i get all collations that i have in one database.
> I
> get the database collation but i need to verify if any table or field in
> the
> current database have one different collation.
> I've got one job that is returning the following error:
> Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
> Implicit conversion of char value to char cannot be performed because the
> collation of the value is unresolved due to a collation conflict.
> Thanks and best regars.|||Thanks Dan,
Best regards
"Dan Guzman" wrote:
> The following will list columns in tables and views that have a collation
> other than the current database default:
> SELECT
> o.type,
> USER_NAME(o.uid),
> o.name,
> c.name
> FROM sysobjects o
> JOIN syscolumns c ON c.id = o.id
> WHERE
> o.type IN('U', 'V') AND
> c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
> > Hello,
> >
> > Can you tell me, how can i get all collations that i have in one database.
> > I
> > get the database collation but i need to verify if any table or field in
> > the
> > current database have one different collation.
> >
> > I've got one job that is returning the following error:
> > Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
> > Implicit conversion of char value to char cannot be performed because the
> > collation of the value is unresolved due to a collation conflict.
> >
> > Thanks and best regars.
>
>

Check all existing collations in one database.

Hello,
Can you tell me, how can i get all collations that i have in one database. I
get the database collation but i need to verify if any table or field in the
current database have one different collation.
I've got one job that is returning the following error:
Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
Implicit conversion of char value to char cannot be performed because the
collation of the value is unresolved due to a collation conflict.
Thanks and best regars.The following will list columns in tables and views that have a collation
other than the current database default:
SELECT
o.type,
USER_NAME(o.uid),
o.name,
c.name
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
WHERE
o.type IN('U', 'V') AND
c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
> Hello,
> Can you tell me, how can i get all collations that i have in one database.
> I
> get the database collation but i need to verify if any table or field in
> the
> current database have one different collation.
> I've got one job that is returning the following error:
> Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
> Implicit conversion of char value to char cannot be performed because the
> collation of the value is unresolved due to a collation conflict.
> Thanks and best regars.|||Thanks Dan,
Best regards
"Dan Guzman" wrote:

> The following will list columns in tables and views that have a collation
> other than the current database default:
> SELECT
> o.type,
> USER_NAME(o.uid),
> o.name,
> c.name
> FROM sysobjects o
> JOIN syscolumns c ON c.id = o.id
> WHERE
> o.type IN('U', 'V') AND
> c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
>
>

character sets, sort order, collations

Just trying to determine character set and sort order for a couple servers.
I see that the output for sp_helpsort has changed, such that it used to be
more helpful than it is now. I guess it changed at some point. On a SQL 7
system, I can easily get the charset and sortorder (below), as well as a
listing of the characters in order.
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
charset_num=1 sort_order_num=52
But on some SQL 2000 systems, sp_helpsort returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive
And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
nocase_iso when such descriptive output isn't provided any more?
Dale.these might be of help:
http://msdn.microsoft.com/library/en-us/instsql/in_collation_3oa6.asp
select *
from ::fn_helpcollations()
-oj
<Dale Kerr> wrote in message news:%23GKAhQovFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Just trying to determine character set and sort order for a couple
> servers. I see that the output for sp_helpsort has changed, such that it
> used to be more helpful than it is now. I guess it changed at some point.
> On a SQL 7 system, I can easily get the charset and sortorder (below), as
> well as a listing of the characters in order.
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
> charset_num=1 sort_order_num=52
> But on some SQL 2000 systems, sp_helpsort returns:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive
> And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
> How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
> nocase_iso when such descriptive output isn't provided any more?
> Dale.
>|||Cheers, I'll see if I can get my head around it.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O4k18covFHA.2880@.TK2MSFTNGP12.phx.gbl...
> these might be of help:
> http://msdn.microsoft.com/library/en-us/instsql/in_collation_3oa6.asp
> select *
> from ::fn_helpcollations()

character sets, sort order, collations

Just trying to determine character set and sort order for a couple servers.
I see that the output for sp_helpsort has changed, such that it used to be
more helpful than it is now. I guess it changed at some point. On a SQL 7
system, I can easily get the charset and sortorder (below), as well as a
listing of the characters in order.
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
charset_num=1 sort_order_num=52
But on some SQL 2000 systems, sp_helpsort returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive
And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
nocase_iso when such descriptive output isn't provided any more?
Dale.these might be of help:
http://msdn.microsoft.com/library/e...lation_3oa6.asp
select *
from ::fn_helpcollations()
-oj
<Dale Kerr> wrote in message news:%23GKAhQovFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Just trying to determine character set and sort order for a couple
> servers. I see that the output for sp_helpsort has changed, such that it
> used to be more helpful than it is now. I guess it changed at some point.
> On a SQL 7 system, I can easily get the charset and sortorder (below), as
> well as a listing of the characters in order.
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
> charset_num=1 sort_order_num=52
> But on some SQL 2000 systems, sp_helpsort returns:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive
> And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
> How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
> nocase_iso when such descriptive output isn't provided any more?
> Dale.
>|||Cheers, I'll see if I can get my head around it.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O4k18covFHA.2880@.TK2MSFTNGP12.phx.gbl...
> these might be of help:
> http://msdn.microsoft.com/library/e...lation_3oa6.asp
> select *
> from ::fn_helpcollations()

character sets, sort order, collations

Just trying to determine character set and sort order for a couple servers.
I see that the output for sp_helpsort has changed, such that it used to be
more helpful than it is now. I guess it changed at some point. On a SQL 7
system, I can easily get the charset and sortorder (below), as well as a
listing of the characters in order.
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
charset_num=1 sort_order_num=52
But on some SQL 2000 systems, sp_helpsort returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive
And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
nocase_iso when such descriptive output isn't provided any more?
Dale.
these might be of help:
http://msdn.microsoft.com/library/en...ation_3oa6.asp
select *
from ::fn_helpcollations()
-oj
<Dale Kerr> wrote in message news:%23GKAhQovFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Just trying to determine character set and sort order for a couple
> servers. I see that the output for sp_helpsort has changed, such that it
> used to be more helpful than it is now. I guess it changed at some point.
> On a SQL 7 system, I can easily get the charset and sortorder (below), as
> well as a listing of the characters in order.
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> Alternatively, EXEC sp_serverinfo 18: charset=iso_1 sort_order=nocase_iso
> charset_num=1 sort_order_num=52
> But on some SQL 2000 systems, sp_helpsort returns:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive
> And EXEC sp_server_info 18: charset=iso_1 collation=Latin1_General_CI_AS
> How do I confirm that newer systems are 72 - nocase_1252 rather than 52 -
> nocase_iso when such descriptive output isn't provided any more?
> Dale.
>
|||Cheers, I'll see if I can get my head around it.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O4k18covFHA.2880@.TK2MSFTNGP12.phx.gbl...
> these might be of help:
> http://msdn.microsoft.com/library/en...ation_3oa6.asp
> select *
> from ::fn_helpcollations()