Showing posts with label operation. Show all posts
Showing posts with label operation. Show all posts

Friday, February 10, 2012

Collation Problem on Sql Server 2000

Hi all,
My database's collation setting is Turkish_CI_AS and I need to change as
SQL_Latin_General_CP1_CI_AS
which I use command for this operation as follows
"Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
but I have error message like that :
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
set to SQL_Latin1_General_CP1_CI_AS.
so how can I solve tihs problem and how can I turn my collation setting as
SQL_Latin_General_CP1_CI_AS
thanks for helps.ss
It is not going to be an easy task ,because sql server will throw an error
of you hasve any indexes on those columns , so first thing I'd remove all
indexe and the run
ALTER TABLE ... ALTER COLUMN ... statement
Moreover if you issue ALTER DATABASE ...COLLATION... all objects that have
an 'old' collation will not be affected
"ss" <ss@.ss.com> wrote in message
news:ur$XkyLoGHA.4332@.TK2MSFTNGP03.phx.gbl...
>
> Hi all,
> My database's collation setting is Turkish_CI_AS and I need to change as
> SQL_Latin_General_CP1_CI_AS
> which I use command for this operation as follows
> "Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
> but I have error message like that :
> Server: Msg 5030, Level 16, State 2, Line 1
> The database could not be exclusively locked to perform the operation.
> Server: Msg 5072, Level 16, State 1, Line 1
> ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
> set to SQL_Latin1_General_CP1_CI_AS.
> so how can I solve tihs problem and how can I turn my collation setting as
> SQL_Latin_General_CP1_CI_AS
> thanks for helps.
>
>

Collation Problem - Migration 7.0 to 2000

Hi,
I have a SQL7 user database backup that I load into the
SQL2000 without problem. When the restore operation is
finished I got the database loaded with
Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command
alter database Glb
COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
the on-line book but I got this message
Line 1: Incorrect syntax
near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with
the same tempdb COLLATION to avoid further problem
like "Cannot resolve collation conflict for equal to
operation."This is a multi-part message in MIME format.
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Leave out the quotes:
alter database Glb COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
Hi,
I have a SQL7 user database backup that I load into the SQL2000 without problem. When the restore operation is finished I got the database loaded with Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in the on-line book but I got this message
Line 1: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with the same tempdb COLLATION to avoid further problem like "Cannot resolve collation conflict for equal to operation."
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Leave out the quotes:
alter database Glb =COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...Hi,I have a SQL7 user database backup that I load into the SQL2000 =without problem. When the restore operation is finished I got the database =loaded with Compatibility_52_409_300003 COLLATION.How could I =change the COLLATION set for that database?I am trying to use this following =command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as =described in the on-line book but I got this messageLine 1: Incorrect =syntax near 'SQL_Latin1_General_CP1_CI_AS'.In fact, I am trying to =do this to have my database with the same tempdb COLLATION to avoid further =problem like "Cannot resolve collation conflict for equal to operation."

--=_NextPart_000_0101_01C35748.686076C0--|||Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just curious, what is the database compatibility level? Have you =changed it to 80? Check out sp_dbcmptlevel in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the ISQL too and put the database in the restricted use... and nothing.
Thanks in advance and if you have another suggestion, please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the >SQL2000 without problem. When the restore operation is >finished I got the database loaded with >Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book but I got this message
>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with >the same tempdb COLLATION to avoid further problem >like "Cannot resolve collation conflict for equal to >operation."
>
>
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Just curious, what is the database =compatibility level? Have you changed it to 80? Check out sp_dbcmptlevel =in the BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0e7301c3576d$ae=023d60$a301280a@.phx.gbl...Thanks TomI got the same error without the quotes. I tried in the =ISQL too and put the database in the restricted use... and =nothing.Thanks in advance and if you have another suggestion, please send it.>--Original Message-->Leave out the quotes:>>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS>>-- >Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Marc=elo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...>Hi,>>I have a SQL7 user database backup that I load into the >SQL2000 =without problem. When the restore operation is >finished I got the =database loaded with >Compatibility_52_409_300003 =COLLATION.>>How could I change the COLLATION set for that database?>I am trying =to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book =but I got this message>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.>>In fact, I am trying to =do this to have my database with >the same tempdb COLLATION to avoid =further problem >like "Cannot resolve collation conflict for equal to >operation.">>>

--=_NextPart_000_0172_01C3574D.102E6480--|||Unfortunately, changing the database's default collation won't change the
collation of any existing tables or columns; it only affects new tables
that are created in that database. In other words, it won't have any
effect on your collation conflict errors.
It is not trivial to change the collation of all the columns in an existing
database. If you have the option, probably the easiest solution by far is
to modify your stored procs slightly so that they use "COLLATE
database_default" when create temp tables. This will tell SQL that the
temp table's collation should be inherited from the current user database,
not tempdb. From that point forward your code will be "server
collation-agnostic", meaning you can move this database to any server with
any collation and the code will run fine. An example:
CREATE #temptbl (
col1 int,
col2 varchar(20) COLLATE database_default,
col3 nchar(4) COLLATE database_default
)
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Tom Moreau" <tom@.dont.spam.me.cips.ca>
References: <0f3101c35768$1eefe780$a101280a@.phx.gbl>
<#J4n2n2VDHA.392@.TK2MSFTNGP11.phx.gbl>
<0e7301c3576d$ae023d60$a301280a@.phx.gbl>
Subject: Re: Collation Problem - Migration 7.0 to 2000
Date: Thu, 31 Jul 2003 10:18:19 -0400
Lines: 154
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="--=_NextPart_000_0172_01C3574D.102E6480"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
Message-ID: <e78Jk62VDHA.3220@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: 142.108.149.181
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:298816
X-Tomcat-NG: microsoft.public.sqlserver.server
Just curious, what is the database compatibility level? Have you changed
it to 80? Check out sp_dbcmptlevel in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message
news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>