|
Home > Archive > MS SQL Server > October 2006 > collation
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| stoney 2006-10-24, 6:33 pm |
| please let me know how to check which collation it is set for.
thx
| |
| Sue Hoegemeier 2006-10-24, 6:33 pm |
| Server Level:
SELECT SERVERPROPERTY ('Collation')
Database Level:
SELECT DATABASEPROPERTYEX('
YourDatabase' , 'Collation' )
-Sue
On Mon, 2 Oct 2006 08:48:01 -0700, stoney
<stoney@discussions.microsoft.com> wrote:
>please let me know how to check which collation it is set for.
>
>thx
| |
| stoney 2006-10-24, 6:33 pm |
| thx that is great
I know I can't change it at the server level unless I reinstall but can I
change it and the db level?
"Sue Hoegemeier" wrote:
> Server Level:
> SELECT SERVERPROPERTY ('Collation')
>
> Database Level:
> SELECT DATABASEPROPERTYEX('
YourDatabase' , 'Collation' )
>
> -Sue
>
> On Mon, 2 Oct 2006 08:48:01 -0700, stoney
> <stoney@discussions.microsoft.com> wrote:
>
>
>
| |
| Tibor Karaszi 2006-10-24, 6:33 pm |
| > I know I can't change it at the server level unless I reinstall
No need to reinstall, but you do need to rebuild the system databases.
> but can I
> change it and the db level?
Yes, with ALTER DATABASE. But that won't change collation for your existing data. For that you need
to do ALTER TABLE ... ALTER COLUMN. Probably easier to export/import.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"stoney" <stoney@discussions.microsoft.com> wrote in message
news:DFE231CB-16EA-4643-A3B1- 7B9F7CB211C2@microso
ft.com...[color=darkred]
> thx that is great
>
> I know I can't change it at the server level unless I reinstall but can I
> change it and the db level?
>
> "Sue Hoegemeier" wrote:
>
| |
| Sue Hoegemeier 2006-10-24, 6:33 pm |
| You can change a database collation with:
ALTER DATABASE YourDatabase
COLLATE CollationName
But that only affects new tables. So to really change the
collation and include all existing data and objects isn't
necessarily all that simple. You'd have to use
ALTER TABLE TableName
ALTER COLUMN....
and you have to drop index, constraints
etc. first and then recreate.
It's probably easiest to create a new database with the
appropriate collation and then use DTS or SSIS to copy the
objects and data over to the new database.
Either way, you'd want to allow for a good deal of testing
as well.
-Sue
On Mon, 2 Oct 2006 09:05:02 -0700, stoney
<stoney@discussions.microsoft.com> wrote:
[color=darkred]
>thx that is great
>
>I know I can't change it at the server level unless I reinstall but can I
>change it and the db level?
>
>"Sue Hoegemeier" wrote:
>
|
|
|
|
|