Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bear
=?Utf-8?B?UG9sYXIgQmVhcg==?= (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.

> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bearexamnotes (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.

> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bear=?Utf-8?B?UG9sYXIgQmVhcg==?= (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.
> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.aspsqlsql

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.
>
> 1) how to check the collation settings on SQL 7 ?
The error log will tell you this information.

> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Eric Crdenas
SQL Server senior support professional

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.>
> 1) how to check the collation settings on SQL 7 ?
--
The error log will tell you this information.

> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
--
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Eric Crdenas
SQL Server senior support professional

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.>
> 1) how to check the collation settings on SQL 7 ?
--
The error log will tell you this information.
> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
--
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
--
Eric Cárdenas
SQL Server senior support professional

Clustered SQL7 upgrade to SQL2000 fails to find default server

We are trying to upgrade to SQL2000 in a failover cluster
environment but the upgrade process is failing to identify
the default server and is instead creating a new named
instance of SQL2000 alongside SQL7 (the 'default' checkbox
on the upgrade wizard is greyed out and unchecked). The
production database is 125Gb so I don't want to use the
copy database wizard if possible. Can anyone suggest how
to get round this? Is it a registry setting?
TIA
JohnI am not sure what process you are taking to do this but here is the proper
sequence:
1. Uncluster SQL Server 7.0 cluster
2. Upgrade SQL Server 7.0 to a SQL Server 2000 default instance (putting
binaries on a local drive)
3. Upgrade the default instance of SQL Server 2000 ti a clustered intance
of SQL Server.
This is documetned in Books on Line:
Upgrading to a SQL SErver 2000 Failover Cluster
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand,
this is exactly the process we are trying to follow, but
the 2nd step fails because the upgrade process can't find
the default SQL7 server to upgrade in place and instead
creates a new instance of SQL2000. We have tried this over
and over again, even to the point of completely removing
and reinstalling SQL7. I've had no trouble before with
upgrading a standalone server so it's probably something
to do with the clustering. Any suggestions would be
greatly appreciated...

>--Original Message--
>I am not sure what process you are taking to do this but
here is the proper
>sequence:
>1. Uncluster SQL Server 7.0 cluster
>2. Upgrade SQL Server 7.0 to a SQL Server 2000 default
instance (putting
>binaries on a local drive)
>3. Upgrade the default instance of SQL Server 2000 ti a
clustered intance
>of SQL Server.
>This is documetned in Books on Line:
>Upgrading to a SQL SErver 2000 Failover Cluster
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>