Collation difference between SQL 6.5 server and SQL 2000 server

Hello,
We are facing the following problem:
We have installed SQL 6.5 server Enterprise edition on Windows 2000
Advanced server. On this system, in the regional settings, the Locale
is Japanese and in Office 2003 Language settings also, we have added
Japanese.
We have another system with Windows 2000 server on which we have
installed SQL 2000 server Enterprise edition with SP4. On this system,
in the regional settings, the Locale is Japanese and in Office 2003
Language settings also, we have added Japanese.

>From the front end VB code, a query is formed which contains Japanese
value. This works in the SQL 6.5 environment but does not work in the
SQL 2000 environment. If we directly execute the query in SQL 2000
server, it is returning the row. But the VB front end is not
recognizing the row.
The query is like this:
SELECT * FROM Tstar_Currency WHERE Currency =3D N'=C6=CE=DD'
This query works when the VB client connects to the SQL 6.5 server and
the front end fetches one row. But when the VB client connects to SQL
2000 server, the front end is not fetching any row. However, if we
directly execute this in query analyzer in SQL 2000 server, it returns
one row.
Any idea why this is happening? What collation setting should we give
in the SQL 2000 server? Do we need to do anything in the front end
application?
Any help will be greatly appreciated. This is very urgent. Please also
mail to ravi.meenakshi@.gmail.com
Thanks!I would sugest running the VB app in debug mode and see what happens when
the VB app issues the select statment. In addition at the same time you may
open Profiler.
The collation you are using in sql 6.5 works well for you so I would try to
keep the same collation with sql 2000.
Be aware that with sql2000 you have collation at three levels: server,
database and columns.
Verify that you have installed sql 2000 with the desired collation.
Yaniv
--
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1135763223.233337.224300@.g49g2000cwa.googlegroups.com...
Hello,
We are facing the following problem:
We have installed SQL 6.5 server Enterprise edition on Windows 2000
Advanced server. On this system, in the regional settings, the Locale
is Japanese and in Office 2003 Language settings also, we have added
Japanese.
We have another system with Windows 2000 server on which we have
installed SQL 2000 server Enterprise edition with SP4. On this system,
in the regional settings, the Locale is Japanese and in Office 2003
Language settings also, we have added Japanese.

>From the front end VB code, a query is formed which contains Japanese
value. This works in the SQL 6.5 environment but does not work in the
SQL 2000 environment. If we directly execute the query in SQL 2000
server, it is returning the row. But the VB front end is not
recognizing the row.
The query is like this:
SELECT * FROM Tstar_Currency WHERE Currency = N''
This query works when the VB client connects to the SQL 6.5 server and
the front end fetches one row. But when the VB client connects to SQL
2000 server, the front end is not fetching any row. However, if we
directly execute this in query analyzer in SQL 2000 server, it returns
one row.
Any idea why this is happening? What collation setting should we give
in the SQL 2000 server? Do we need to do anything in the front end
application?
Any help will be greatly appreciated. This is very urgent. Please also
mail to ravi.meenakshi@.gmail.com
Thanks!|||(Ravi.Meenakshi@.gmail.com) writes:
> We have installed SQL 6.5 server Enterprise edition on Windows 2000
> Advanced server. On this system, in the regional settings, the Locale
> is Japanese and in Office 2003 Language settings also, we have added
> Japanese.
> We have another system with Windows 2000 server on which we have
> installed SQL 2000 server Enterprise edition with SP4. On this system,
> in the regional settings, the Locale is Japanese and in Office 2003
> Language settings also, we have added Japanese.
>
> value. This works in the SQL 6.5 environment but does not work in the
> SQL 2000 environment. If we directly execute the query in SQL 2000
> server, it is returning the row. But the VB front end is not
> recognizing the row.
> The query is like this:
> SELECT * FROM Tstar_Currency WHERE Currency = N''
> This query works when the VB client connects to the SQL 6.5 server and
> the front end fetches one row. But when the VB client connects to SQL
> 2000 server, the front end is not fetching any row. However, if we
> directly execute this in query analyzer in SQL 2000 server, it returns
> one row.
Wait a minute, that query does not even have legal syntax for
SQL 6.5. Is the N really there?
What does sp_helpsort return on SQL 6.5?
On SQL 2000, what does SELECT Serverproperty('Collation') return?
If you run sp_help on Tstar_Currency on SQL 2000, which is the
data type, and which is the collation for that column?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The N is there in the query only in SQL 2000, not in 6.5
The data type of Currency column in Tstar_Currency in SQL 6.5 is
char(20) and in SQL 2000 is nchar(20)
The collation of this column in SQL 2000 is Japanese_CI_AS
The only change in the VB code is the inclusion of N before the
Japanese value.
Any clues on how to solve the problem?|||I ran profiler for the 2000 environment. In the query I first saw in
the profiler, I saw boxes. Then I changed the font in the Profiler to
Japanese. Then the query displayed like this:
SELECT MSCICode FROM Tstar_Currency WHERE Currency =3D N'=EF=BE=86=EF=BE=8E=
=EF=BE=9D'
When I executed this query in query analyzer, it did not return any
rows.
Please note that the client PC on which the VB application is running,
the regional settings is changed to Japanese.
I can't understand why the Japanese value was not passed correctly from
VB to SQL Server.
Any clues?|||(Ravi.Meenakshi@.gmail.com) writes:
> The N is there in the query only in SQL 2000, not in 6.5
> The data type of Currency column in Tstar_Currency in SQL 6.5 is
> char(20) and in SQL 2000 is nchar(20)
> The collation of this column in SQL 2000 is Japanese_CI_AS
> The only change in the VB code is the inclusion of N before the
> Japanese value.
> Any clues on how to solve the problem?
I asked you these questions:
What does sp_helpsort return on SQL 6.5?
On SQL 2000, what does SELECT Serverproperty('Collation') return?
It's difficult to give clues, if I don't get any.
But it does sound funny to me that the sole difference should be the
addition of the N. If the characters in the string are Unicode, it would
not work on 6.5. Since it works on 6.5, I suspect that the string is in
some other character set. In which case it will of course not work with
a Unicode column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The output of sp_helpsort in 65. is:
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase
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.
The output of SELECT Serverproperty('Collation') in SQL 2000 is
SQL_Latin1_General_CP1_CI_AS
The collation setting of the SQL 2000 server is the default setting.
The collation for the database that the front end code is accessing is
Japanese_CI_AS|||(Ravi.Meenakshi@.gmail.com) writes:
> The output of sp_helpsort in 65. is:
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase
> 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.
> The output of SELECT Serverproperty('Collation') in SQL 2000 is
It sounds like a miracle if you get Japanese text correcly into that
server. Or at least a great deal of luck.

> SQL_Latin1_General_CP1_CI_AS
> The collation setting of the SQL 2000 server is the default setting.
> The collation for the database that the front end code is accessing is
> Japanese_CI_AS
I have not really managed to fully understand at what collation the
client APIs looks at when they convert data between character sets,
but I suspect that's the server collation. In which case Japanese text
that is the ANSI code page, will be mangled before it's sent to SQL Server.
Apparently you are building a an entire command string in VB. Try to use
parameterised command instead, as then you can declare the type of the
parameter.
You could also try to install an instance of SQL 2000 that has
Japanese_CI_AS as its server collation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

Collation Confusion

Greetings!
I'm trying to install SQL Server 2000 (Standard edition) on a new server to
mirror the version on an older server. The trouble I'm having is with
identifying the settings I need in order to match collations.
The instance of SQL Server on the old server has a collation described as
'SQL_Latin1_General_CP1_CI_AS'. I realise that 'CI' and 'AS' refer to
case-insensitive and accent-sensitive respectively, but what does 'CP1'
relate to?
I've searched through various resources but they all seem to avoid
describing that one crucial bit of information! Can anyone help?
Code page 1
"Mario" <someone.else@.microsoft.com> wrote in message
news:O3bumugWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> Greetings!
> I'm trying to install SQL Server 2000 (Standard edition) on a new server
to
> mirror the version on an older server. The trouble I'm having is with
> identifying the settings I need in order to match collations.
> The instance of SQL Server on the old server has a collation described as
> 'SQL_Latin1_General_CP1_CI_AS'. I realise that 'CI' and 'AS' refer to
> case-insensitive and accent-sensitive respectively, but what does 'CP1'
> relate to?
> I've searched through various resources but they all seem to avoid
> describing that one crucial bit of information! Can anyone help?
>
|||In setup you should select "Dictionary order, case-insensitive, for use
with 1252 character set" in the collation selection dialog. This is the
default collation for U.S. English SQL Servers.
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
References: <O3bumugWEHA.1368@.TK2MSFTNGP10.phx.gbl>
Subject: Re: Collation Confusion
Date: Thu, 24 Jun 2004 13:08:24 -0400
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <uAyDd3gWEHA.1468@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: firewall.getconnected.com 207.31.238.50
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA06.phx.gbl!cpmsftn gxa06.phx.gbl!TK2MSFTNGX
A01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx. gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:347579
X-Tomcat-NG: microsoft.public.sqlserver.server
Code page 1
"Mario" <someone.else@.microsoft.com> wrote in message
news:O3bumugWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> Greetings!
> I'm trying to install SQL Server 2000 (Standard edition) on a new server
to
> mirror the version on an older server. The trouble I'm having is with
> identifying the settings I need in order to match collations.
> The instance of SQL Server on the old server has a collation described
as
> 'SQL_Latin1_General_CP1_CI_AS'. I realise that 'CI' and 'AS' refer to
> case-insensitive and accent-sensitive respectively, but what does 'CP1'
> relate to?
> I've searched through various resources but they all seem to avoid
> describing that one crucial bit of information! Can anyone help?
>
sqlsql

Collation Confusion

Greetings!
I'm trying to install SQL Server 2000 (Standard edition) on a new server to
mirror the version on an older server. The trouble I'm having is with
identifying the settings I need in order to match collations.
The instance of SQL Server on the old server has a collation described as
'SQL_Latin1_General_CP1_CI_AS'. I realise that 'CI' and 'AS' refer to
case-insensitive and accent-sensitive respectively, but what does 'CP1'
relate to?
I've searched through various resources but they all seem to avoid
describing that one crucial bit of information! Can anyone help?Code page 1
"Mario" <someone.else@.microsoft.com> wrote in message
news:O3bumugWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> Greetings!
> I'm trying to install SQL Server 2000 (Standard edition) on a new server
to
> mirror the version on an older server. The trouble I'm having is with
> identifying the settings I need in order to match collations.
> The instance of SQL Server on the old server has a collation described as
> 'SQL_Latin1_General_CP1_CI_AS'. I realise that 'CI' and 'AS' refer to
> case-insensitive and accent-sensitive respectively, but what does 'CP1'
> relate to?
> I've searched through various resources but they all seem to avoid
> describing that one crucial bit of information! Can anyone help?
>|||In setup you should select "Dictionary order, case-insensitive, for use
with 1252 character set" in the collation selection dialog. This is the
default collation for U.S. English SQL Servers.
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
References: <O3bumugWEHA.1368@.TK2MSFTNGP10.phx.gbl>
Subject: Re: Collation Confusion
Date: Thu, 24 Jun 2004 13:08:24 -0400
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <uAyDd3gWEHA.1468@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: firewall.getconnected.com 207.31.238.50
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA06.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGX
A01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:347579
X-Tomcat-NG: microsoft.public.sqlserver.server
Code page 1
"Mario" <someone.else@.microsoft.com> wrote in message
news:O3bumugWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> Greetings!
> I'm trying to install SQL Server 2000 (Standard edition) on a new server
to
> mirror the version on an older server. The trouble I'm having is with
> identifying the settings I need in order to match collations.
> The instance of SQL Server on the old server has a collation described
as
> 'SQL_Latin1_General_CP1_CI_AS'. I realise that 'CI' and 'AS' refer to
> case-insensitive and accent-sensitive respectively, but what does 'CP1'
> relate to?
> I've searched through various resources but they all seem to avoid
> describing that one crucial bit of information! Can anyone help?
>

Collation Confusion

Greetings!
I'm trying to install SQL Server 2000 (Standard edition) on a new server to
mirror the version on an older server. The trouble I'm having is with
identifying the settings I need in order to match collations.
The instance of SQL Server on the old server has a collation described as
'SQL_Latin1_General_CP1_CI_AS'. I realise that 'CI' and 'AS' refer to
case-insensitive and accent-sensitive respectively, but what does 'CP1'
relate to?
I've searched through various resources but they all seem to avoid
describing that one crucial bit of information! Can anyone help?Code page 1
"Mario" <someone.else@.microsoft.com> wrote in message
news:O3bumugWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> Greetings!
> I'm trying to install SQL Server 2000 (Standard edition) on a new server
to
> mirror the version on an older server. The trouble I'm having is with
> identifying the settings I need in order to match collations.
> The instance of SQL Server on the old server has a collation described as
> 'SQL_Latin1_General_CP1_CI_AS'. I realise that 'CI' and 'AS' refer to
> case-insensitive and accent-sensitive respectively, but what does 'CP1'
> relate to?
> I've searched through various resources but they all seem to avoid
> describing that one crucial bit of information! Can anyone help?
>|||In setup you should select "Dictionary order, case-insensitive, for use
with 1252 character set" in the collation selection dialog. This is the
default collation for U.S. English SQL Servers.
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
References: <O3bumugWEHA.1368@.TK2MSFTNGP10.phx.gbl>
Subject: Re: Collation Confusion
Date: Thu, 24 Jun 2004 13:08:24 -0400
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <uAyDd3gWEHA.1468@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: firewall.getconnected.com 207.31.238.50
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA06.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGX
A01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:347579
X-Tomcat-NG: microsoft.public.sqlserver.server
Code page 1
"Mario" <someone.else@.microsoft.com> wrote in message
news:O3bumugWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> Greetings!
> I'm trying to install SQL Server 2000 (Standard edition) on a new server
to
> mirror the version on an older server. The trouble I'm having is with
> identifying the settings I need in order to match collations.
> The instance of SQL Server on the old server has a collation described
as
> 'SQL_Latin1_General_CP1_CI_AS'. I realise that 'CI' and 'AS' refer to
> case-insensitive and accent-sensitive respectively, but what does 'CP1'
> relate to?
> I've searched through various resources but they all seem to avoid
> describing that one crucial bit of information! Can anyone help?
>

Co-exist of SQL 2000 and 2005

Dear all,

I have SQL 2000 Enterprise mgr and Query Analyzer in my desktop, and I install SQL2005 Express Edition + Mgm Studio today, however, my SQL 2000 Enterprise mgr disappear and all my registered servers disappear too when I open up the SQL 2005 Mgm Studio. Is it mean that I no longer able to use SQL 2000? How can I rollback the installation of SQL 2005 because I still have to use Enterprise mgr to manage my existing database and clients. (Yes, I give up SQL 2005 now)

Thanks,
The 2 should work side by side just fine, there is no need to uninstall. What do you mean Enterprise Manager disapears? Did you do a sidebyside install or an upgrade?|||

You say 'when I open up 2005 Management Studio..'

If you did a sidebyside install, all your 2000 stuff is till there, but you still use 2000 EM for them, they aren't in Management Studio. Your old EM should still be available from the programs menu, and 2005 have it's own start menu.

/Kenneth

|||Hi Ken,

Thanks for your advice, Maybe I select "upgrade" SQL 2000 to SQL 2005 in the installation process (is there an option like this?)... so everything gone and I can't see my SQL 2000 Enterprice Manaager and Query Analyzer (you know, these 2 tools are the best friend of DBA ;)

I tried to uninstall SQL 2005 and select "side by side" installation (is there a option like this?) but it create another problem: here.

My original database in SQL 2000 disappear even I re-install SQL 2000 EM, but the MDF and LDF are still in my machine, so I want to "attach" these database immediately, but I can't do so.. do you know why?

(Installation of SQL 2005 is quite problematic... and bring some other problems as well in my opinion.. I can use my EM and QA smoothly over the last few years)

Thanks!
|||So it sounds like you did an upgrade to SQL 2005. For the tools you can uninstall 2005 and then re-install 2000 and it should work, HOWEVER for your data this is a problem. Once a database has been touched by a 2005 server it can not be attached or restored on a 2000 machine. Your only choice is to export the data from a 2005 running copy of the database, out to csv/text etc and then load that on on the other side.|||

Oh my god... I just un-install the SQL 2005 and re-install SQL 2000, that's fine. I want to make everything back to normal first.

Next, I install a lite version of SQL 2005 Express instead (Yes, I'm afraid the SQL 2005 now), and I specifically choose NOT upgrade my existing database (Well, the upgrade option is disabled by default due to the limitation of SQL 2005 Express anyway, so I'm pretty sure it WON'T affect my existing SQL 2000 DBs and Enterprise Mgr, alright?)

However, the result is - It break all of my databases AGAIN. And an error message come out when I try to open my SQL 2000 Ent. Mgr:

Microsoft Management Console

Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}

I'm pretty sure the co-existence of SQL 2000 and SQL 2005 (any edition) is very poor.. I can't do any project against my SQL 2000 nor SQL 20005 db now. :(

Thanks,

|||

Sorry but you are wrong, SQL 2000 and 2005 work just fine together, it is unfortunate that you are having problems, btu I run 4 machiens with this config and they all work fine, as do 10,000s of others.

What do you mean the databases are broken, is there an error message?

To fix the mmc problem try and run regsvr32 on sqlmmc.dll

|||

I'm also running a few servers running both SQL2k and 2005 without problems. Servers did not upgrade the existing sql2k 's. I installed the 2005's as new instances.

Eralper

http://www.kodyaz.com

|||

Euan Garden wrote:

To fix the mmc problem try and run regsvr32 on sqlmmc.dll

I have more than 1 machine with SQL 2005 installed, and nearly 50% of them are facing various problem. I understand that there're 10000s installation successed, but you should be aware that there're also have another 10000s installation failed and looking for help here or other NGs everyday. This is a pain. Even 1 installation failed, this product is buggy.

The databases can't be attached or re-used anymore if SQL 2005 "touch" the db from SQL 2000, that's a famous bug since beta age.

The regsvr32 sqlmmc.dll and sqldmo.dll are no good...

Thanks,

|||

Sorry peter but there are not 1000's of installations which are failed.

Correct once a DB is touched by 2005 it can not be moved back, this is not a bug this is by design, we have to upgrade the format to be able to use the new features of SQL 2000, this has been this way for the last 4 versions of SQL Server and I believe is also true for other DB vendors.

When you say that sqlmmc and sqldmo are no good are you saying that you tried to re-reg and ti did not work?

|||

We have to buy all license and upgrade to SQL 2005 immmediately and we don't have any backward compatiability if we're using SQL 2000? Compatiability and stability are two of the key factors for any DB system.

re-reg doesn't help, SQL 2000 Enterprise Manager still messed up.

(Please see this MSDN blog for more info about this bug)

Thanks,

|||

First of all you don't have to buy new licenses and upgrade, SQL 2000 is a fine product and will be support for several years to come. However if you want to upgrade and get the new version you can.

Your definition of Back Compat differs from mine (and that of the SQL Server Team), apps that work against SQL2000/7 should work against SQL 2005 without changes, thats significant. SQL 2005 can used SQL 2000 as a linked server, it can consume backups, database files and data via OLE DB and ADO.Net. All of that is pretty impressive.

Your issue is that you have taken a SQL Server 2000 DB, and attached it to SQL 2005, obviously wanting to leverage the new features of SQL 2005, to do that means changes to the file format, making it impossible to use the file on 2000(but you can script out) just as if you had used one of the new T-SQL commands in your app, that would not run against a 2000 server. Just like a Winforms 2.0 app will not run against .Net 1.1, just as a etc etc. I'm sorry you disagree with the principle, but this decision/position does not imply anything related to the stability of the system, which is just fine.

I am very familiar with the "bug" and the blog entry you reference(hint take a look at the author of the entry), this is not the same bug. Re-registering DMO fixes the CTP bug you reference.

Now back to the original problem of EM not working. When you re-reg sqlmmc and sqldmo to they both register correctly or is there an error message? If they register correctly has the error message you get when trying to start EM the same or has it changed from the original post? Have you tried applying SP4 of SQL 2000 again?

|||Sorry to post this bug here, but this is the problem after my installation of SQL 2005. I don't want to upgrade my SQL 2000 database to SQL 2005 in fact, if you read my posts above.

The wizard drive me to this stage and now, the database can't be rollbacked to its original state, which is a backward process. Sorry that you have another definition of backward compatibility, but please be considerate when you reading post here, even you have your own definition in your own mind. You can't force others to follow your own definion and you don't have problem in the installation doesn't mean other 10000 people are as lucky as you.

The problem and error messages and all service pack installed. Please read the posts but don't just focus on what you like to see but ignore others' opinion.

Thanks,
|||

If you are playing around with

various versions or instances of MS SQL Server

you may hit the problem of

Microsoft Management Console

Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}

To fix the problem,

1. Click Start, click Run, type regedit, and then click OK. 2. Locate the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup

3. Right-click the ClientSetup registry subkey, point to New, and then click String Value. 4. Rename the registry subkey that you just created SqlPath, and then press ENTER. 5. Double-click the SqlPath registry subkey, and then type C:\Program Files\Microsoft SQL Server\80\Tools in the Value data box. 6. Click OK, and then close Registry Editor. 7. Click Start, click Run, type regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll", and then click OK.

Good Luck,

Alfred.

|||

well. . . unfortunately we have the same problem as well.

after having both SQL 2000 and 2005 we tried to uninstall 2005.

buggered the sql 2000 as noted

running the fix outlined above (as well as the othere method outlined in the kb article the above was copied from) we get:

DllRegisterServer in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll failed.

Return code was: 0x8011041a

We need to get back to a totally clean state so we can do an install of just SQL 2000.

And dont just copy that other kb article 290991 as that didnt work either

Co-exist of SQL 2000 and 2005

Dear all,

I have SQL 2000 Enterprise mgr and Query Analyzer in my desktop, and I install SQL2005 Express Edition + Mgm Studio today, however, my SQL 2000 Enterprise mgr disappear and all my registered servers disappear too when I open up the SQL 2005 Mgm Studio. Is it mean that I no longer able to use SQL 2000? How can I rollback the installation of SQL 2005 because I still have to use Enterprise mgr to manage my existing database and clients. (Yes, I give up SQL 2005 now)

Thanks,The 2 should work side by side just fine, there is no need to uninstall. What do you mean Enterprise Manager disapears? Did you do a sidebyside install or an upgrade?|||

You say 'when I open up 2005 Management Studio..'

If you did a sidebyside install, all your 2000 stuff is till there, but you still use 2000 EM for them, they aren't in Management Studio. Your old EM should still be available from the programs menu, and 2005 have it's own start menu.

/Kenneth

|||Hi Ken,

Thanks for your advice, Maybe I select "upgrade" SQL 2000 to SQL 2005 in the installation process (is there an option like this?)... so everything gone and I can't see my SQL 2000 Enterprice Manaager and Query Analyzer (you know, these 2 tools are the best friend of DBA ;)

I tried to uninstall SQL 2005 and select "side by side" installation (is there a option like this?) but it create another problem: here.

My original database in SQL 2000 disappear even I re-install SQL 2000 EM, but the MDF and LDF are still in my machine, so I want to "attach" these database immediately, but I can't do so.. do you know why?

(Installation of SQL 2005 is quite problematic... and bring some other problems as well in my opinion.. I can use my EM and QA smoothly over the last few years)

Thanks!|||So it sounds like you did an upgrade to SQL 2005. For the tools you can uninstall 2005 and then re-install 2000 and it should work, HOWEVER for your data this is a problem. Once a database has been touched by a 2005 server it can not be attached or restored on a 2000 machine. Your only choice is to export the data from a 2005 running copy of the database, out to csv/text etc and then load that on on the other side.|||

Oh my god... I just un-install the SQL 2005 and re-install SQL 2000, that's fine. I want to make everything back to normal first.

Next, I install a lite version of SQL 2005 Express instead (Yes, I'm afraid the SQL 2005 now), and I specifically choose NOT upgrade my existing database (Well, the upgrade option is disabled by default due to the limitation of SQL 2005 Express anyway, so I'm pretty sure it WON'T affect my existing SQL 2000 DBs and Enterprise Mgr, alright?)

However, the result is - It break all of my databases AGAIN. And an error message come out when I try to open my SQL 2000 Ent. Mgr:

Microsoft Management Console

Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}

I'm pretty sure the co-existence of SQL 2000 and SQL 2005 (any edition) is very poor.. I can't do any project against my SQL 2000 nor SQL 20005 db now. :(

Thanks,

|||

Sorry but you are wrong, SQL 2000 and 2005 work just fine together, it is unfortunate that you are having problems, btu I run 4 machiens with this config and they all work fine, as do 10,000s of others.

What do you mean the databases are broken, is there an error message?

To fix the mmc problem try and run regsvr32 on sqlmmc.dll

|||

I'm also running a few servers running both SQL2k and 2005 without problems. Servers did not upgrade the existing sql2k 's. I installed the 2005's as new instances.

Eralper

http://www.kodyaz.com

|||

Euan Garden wrote:

To fix the mmc problem try and run regsvr32 on sqlmmc.dll

I have more than 1 machine with SQL 2005 installed, and nearly 50% of them are facing various problem. I understand that there're 10000s installation successed, but you should be aware that there're also have another 10000s installation failed and looking for help here or other NGs everyday. This is a pain. Even 1 installation failed, this product is buggy.

The databases can't be attached or re-used anymore if SQL 2005 "touch" the db from SQL 2000, that's a famous bug since beta age.

The regsvr32 sqlmmc.dll and sqldmo.dll are no good...

Thanks,

|||

Sorry peter but there are not 1000's of installations which are failed.

Correct once a DB is touched by 2005 it can not be moved back, this is not a bug this is by design, we have to upgrade the format to be able to use the new features of SQL 2000, this has been this way for the last 4 versions of SQL Server and I believe is also true for other DB vendors.

When you say that sqlmmc and sqldmo are no good are you saying that you tried to re-reg and ti did not work?

|||

We have to buy all license and upgrade to SQL 2005 immmediately and we don't have any backward compatiability if we're using SQL 2000? Compatiability and stability are two of the key factors for any DB system.

re-reg doesn't help, SQL 2000 Enterprise Manager still messed up.

(Please see this MSDN blog for more info about this bug)

Thanks,

|||

First of all you don't have to buy new licenses and upgrade, SQL 2000 is a fine product and will be support for several years to come. However if you want to upgrade and get the new version you can.

Your definition of Back Compat differs from mine (and that of the SQL Server Team), apps that work against SQL2000/7 should work against SQL 2005 without changes, thats significant. SQL 2005 can used SQL 2000 as a linked server, it can consume backups, database files and data via OLE DB and ADO.Net. All of that is pretty impressive.

Your issue is that you have taken a SQL Server 2000 DB, and attached it to SQL 2005, obviously wanting to leverage the new features of SQL 2005, to do that means changes to the file format, making it impossible to use the file on 2000(but you can script out) just as if you had used one of the new T-SQL commands in your app, that would not run against a 2000 server. Just like a Winforms 2.0 app will not run against .Net 1.1, just as a etc etc. I'm sorry you disagree with the principle, but this decision/position does not imply anything related to the stability of the system, which is just fine.

I am very familiar with the "bug" and the blog entry you reference(hint take a look at the author of the entry), this is not the same bug. Re-registering DMO fixes the CTP bug you reference.

Now back to the original problem of EM not working. When you re-reg sqlmmc and sqldmo to they both register correctly or is there an error message? If they register correctly has the error message you get when trying to start EM the same or has it changed from the original post? Have you tried applying SP4 of SQL 2000 again?

|||Sorry to post this bug here, but this is the problem after my installation of SQL 2005. I don't want to upgrade my SQL 2000 database to SQL 2005 in fact, if you read my posts above.

The wizard drive me to this stage and now, the database can't be rollbacked to its original state, which is a backward process. Sorry that you have another definition of backward compatibility, but please be considerate when you reading post here, even you have your own definition in your own mind. You can't force others to follow your own definion and you don't have problem in the installation doesn't mean other 10000 people are as lucky as you.

The problem and error messages and all service pack installed. Please read the posts but don't just focus on what you like to see but ignore others' opinion.

Thanks,|||

If you are playing around with

various versions or instances of MS SQL Server

you may hit the problem of

Microsoft Management Console

Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}

To fix the problem,

1.

Click Start, click Run, type regedit, and then click OK.

2.

Locate the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup

3.

Right-click the ClientSetup registry subkey, point to New, and then click String Value.

4.

Rename the registry subkey that you just created SqlPath, and then press ENTER.

5.

Double-click the SqlPath registry subkey, and then type C:\Program Files\Microsoft SQL Server\80\Tools in the Value data box.

6.

Click OK, and then close Registry Editor.

7.

Click Start, click Run, type regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll", and then click OK.

Good Luck,

Alfred.

|||

well. . . unfortunately we have the same problem as well.

after having both SQL 2000 and 2005 we tried to uninstall 2005.

buggered the sql 2000 as noted

running the fix outlined above (as well as the othere method outlined in the kb article the above was copied from) we get:

DllRegisterServer in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll failed.

Return code was: 0x8011041a

We need to get back to a totally clean state so we can do an install of just SQL 2000.

And dont just copy that other kb article 290991 as that didnt work either

Co-exist of SQL 2000 and 2005

Dear all,

I have SQL 2000 Enterprise mgr and Query Analyzer in my desktop, and I install SQL2005 Express Edition + Mgm Studio today, however, my SQL 2000 Enterprise mgr disappear and all my registered servers disappear too when I open up the SQL 2005 Mgm Studio. Is it mean that I no longer able to use SQL 2000? How can I rollback the installation of SQL 2005 because I still have to use Enterprise mgr to manage my existing database and clients. (Yes, I give up SQL 2005 now)

Thanks,The 2 should work side by side just fine, there is no need to uninstall. What do you mean Enterprise Manager disapears? Did you do a sidebyside install or an upgrade?|||

You say 'when I open up 2005 Management Studio..'

If you did a sidebyside install, all your 2000 stuff is till there, but you still use 2000 EM for them, they aren't in Management Studio. Your old EM should still be available from the programs menu, and 2005 have it's own start menu.

/Kenneth

|||Hi Ken,

Thanks for your advice, Maybe I select "upgrade" SQL 2000 to SQL 2005 in the installation process (is there an option like this?)... so everything gone and I can't see my SQL 2000 Enterprice Manaager and Query Analyzer (you know, these 2 tools are the best friend of DBA ;)

I tried to uninstall SQL 2005 and select "side by side" installation (is there a option like this?) but it create another problem: here.

My original database in SQL 2000 disappear even I re-install SQL 2000 EM, but the MDF and LDF are still in my machine, so I want to "attach" these database immediately, but I can't do so.. do you know why?

(Installation of SQL 2005 is quite problematic... and bring some other problems as well in my opinion.. I can use my EM and QA smoothly over the last few years)

Thanks!|||So it sounds like you did an upgrade to SQL 2005. For the tools you can uninstall 2005 and then re-install 2000 and it should work, HOWEVER for your data this is a problem. Once a database has been touched by a 2005 server it can not be attached or restored on a 2000 machine. Your only choice is to export the data from a 2005 running copy of the database, out to csv/text etc and then load that on on the other side.|||

Oh my god... I just un-install the SQL 2005 and re-install SQL 2000, that's fine. I want to make everything back to normal first.

Next, I install a lite version of SQL 2005 Express instead (Yes, I'm afraid the SQL 2005 now), and I specifically choose NOT upgrade my existing database (Well, the upgrade option is disabled by default due to the limitation of SQL 2005 Express anyway, so I'm pretty sure it WON'T affect my existing SQL 2000 DBs and Enterprise Mgr, alright?)

However, the result is - It break all of my databases AGAIN. And an error message come out when I try to open my SQL 2000 Ent. Mgr:

Microsoft Management Console

Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}

I'm pretty sure the co-existence of SQL 2000 and SQL 2005 (any edition) is very poor.. I can't do any project against my SQL 2000 nor SQL 20005 db now. :(

Thanks,

|||

Sorry but you are wrong, SQL 2000 and 2005 work just fine together, it is unfortunate that you are having problems, btu I run 4 machiens with this config and they all work fine, as do 10,000s of others.

What do you mean the databases are broken, is there an error message?

To fix the mmc problem try and run regsvr32 on sqlmmc.dll

|||

I'm also running a few servers running both SQL2k and 2005 without problems. Servers did not upgrade the existing sql2k 's. I installed the 2005's as new instances.

Eralper

http://www.kodyaz.com

|||

Euan Garden wrote:

To fix the mmc problem try and run regsvr32 on sqlmmc.dll

I have more than 1 machine with SQL 2005 installed, and nearly 50% of them are facing various problem. I understand that there're 10000s installation successed, but you should be aware that there're also have another 10000s installation failed and looking for help here or other NGs everyday. This is a pain. Even 1 installation failed, this product is buggy.

The databases can't be attached or re-used anymore if SQL 2005 "touch" the db from SQL 2000, that's a famous bug since beta age.

The regsvr32 sqlmmc.dll and sqldmo.dll are no good...

Thanks,

|||

Sorry peter but there are not 1000's of installations which are failed.

Correct once a DB is touched by 2005 it can not be moved back, this is not a bug this is by design, we have to upgrade the format to be able to use the new features of SQL 2000, this has been this way for the last 4 versions of SQL Server and I believe is also true for other DB vendors.

When you say that sqlmmc and sqldmo are no good are you saying that you tried to re-reg and ti did not work?

|||

We have to buy all license and upgrade to SQL 2005 immmediately and we don't have any backward compatiability if we're using SQL 2000? Compatiability and stability are two of the key factors for any DB system.

re-reg doesn't help, SQL 2000 Enterprise Manager still messed up.

(Please see this MSDN blog for more info about this bug)

Thanks,

|||

First of all you don't have to buy new licenses and upgrade, SQL 2000 is a fine product and will be support for several years to come. However if you want to upgrade and get the new version you can.

Your definition of Back Compat differs from mine (and that of the SQL Server Team), apps that work against SQL2000/7 should work against SQL 2005 without changes, thats significant. SQL 2005 can used SQL 2000 as a linked server, it can consume backups, database files and data via OLE DB and ADO.Net. All of that is pretty impressive.

Your issue is that you have taken a SQL Server 2000 DB, and attached it to SQL 2005, obviously wanting to leverage the new features of SQL 2005, to do that means changes to the file format, making it impossible to use the file on 2000(but you can script out) just as if you had used one of the new T-SQL commands in your app, that would not run against a 2000 server. Just like a Winforms 2.0 app will not run against .Net 1.1, just as a etc etc. I'm sorry you disagree with the principle, but this decision/position does not imply anything related to the stability of the system, which is just fine.

I am very familiar with the "bug" and the blog entry you reference(hint take a look at the author of the entry), this is not the same bug. Re-registering DMO fixes the CTP bug you reference.

Now back to the original problem of EM not working. When you re-reg sqlmmc and sqldmo to they both register correctly or is there an error message? If they register correctly has the error message you get when trying to start EM the same or has it changed from the original post? Have you tried applying SP4 of SQL 2000 again?

|||Sorry to post this bug here, but this is the problem after my installation of SQL 2005. I don't want to upgrade my SQL 2000 database to SQL 2005 in fact, if you read my posts above.

The wizard drive me to this stage and now, the database can't be rollbacked to its original state, which is a backward process. Sorry that you have another definition of backward compatibility, but please be considerate when you reading post here, even you have your own definition in your own mind. You can't force others to follow your own definion and you don't have problem in the installation doesn't mean other 10000 people are as lucky as you.

The problem and error messages and all service pack installed. Please read the posts but don't just focus on what you like to see but ignore others' opinion.

Thanks,|||

If you are playing around with

various versions or instances of MS SQL Server

you may hit the problem of

Microsoft Management Console

Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}

To fix the problem,

1.

Click Start, click Run, type regedit, and then click OK.

2.

Locate the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup

3.

Right-click the ClientSetup registry subkey, point to New, and then click String Value.

4.

Rename the registry subkey that you just created SqlPath, and then press ENTER.

5.

Double-click the SqlPath registry subkey, and then type C:\Program Files\Microsoft SQL Server\80\Tools in the Value data box.

6.

Click OK, and then close Registry Editor.

7.

Click Start, click Run, type regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll", and then click OK.

Good Luck,

Alfred.

|||

well. . . unfortunately we have the same problem as well.

after having both SQL 2000 and 2005 we tried to uninstall 2005.

buggered the sql 2000 as noted

running the fix outlined above (as well as the othere method outlined in the kb article the above was copied from) we get:

DllRegisterServer in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll failed.

Return code was: 0x8011041a

We need to get back to a totally clean state so we can do an install of just SQL 2000.

And dont just copy that other kb article 290991 as that didnt work either

Clustering with SQL2005 Standard Edition

I see that the new SQL 2005 Standard edition supports 2-node
clustering. Does that mean that Standard edition could be used in a 4
node N+1 scenario set-up as follows:
Node 1: File Server
Node 2: Exchange Server
Node 3: SQL 2005 Standard
Node 4: Dedicated failover node for 1,2 & 3
Only Nodes 3 and 4 will ever be running SQL2005. So would standard be
suitable or would SQL2005 Enterprise edition be needed?
Cheers,
Ian
Ian Robinson, Belfast, UK
<http://www.canicula.com/wp/>
Hi
Standard 2005 would suffice, but you still need the Enterprise Edition of
the Windows 2003 to do clustering.
Be very careful as to the features you need from the SQL Server edition.
There are a lot of performance features that are only available on
Enterprise Edition.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ian Robinson" <junk@.canicula.com> wrote in message
news:0001HW.BF93850800056921F0386550@.news.gradwell .net...
>I see that the new SQL 2005 Standard edition supports 2-node
> clustering. Does that mean that Standard edition could be used in a 4
> node N+1 scenario set-up as follows:
> Node 1: File Server
> Node 2: Exchange Server
> Node 3: SQL 2005 Standard
> Node 4: Dedicated failover node for 1,2 & 3
> Only Nodes 3 and 4 will ever be running SQL2005. So would standard be
> suitable or would SQL2005 Enterprise edition be needed?
> Cheers,
> Ian
> --
> Ian Robinson, Belfast, UK
> <http://www.canicula.com/wp/>
>
|||Hi Mike,
On Sun, 6 Nov 2005 18:20:28 +0000, Mike Epprecht \(SQL MVP\) wrote
(in article <#I#xC7v4FHA.3880@.TK2MSFTNGP12.phx.gbl>):

> Standard 2005 would suffice,
Good stuff.

> but you still need the Enterprise Edition of
> the Windows 2003 to do clustering.
Yep. I thought I would.

> Be very careful as to the features you need from the SQL Server edition.
> There are a lot of performance features that are only available on
> Enterprise Edition.
I've told the client in question that. We're doing an infrastructure
upgrade and putting a base SQL installation so that it'll be there when
they go to market next year to get a new LOB system based on SQL2005.
I've told them that if they go with Standard edition it may not suit
the development house they choose. They are okay with this and will
upgrade to Enterprise next year if they have to. Their call. I've
advised them to go with enterprise now but I can't force them :-)
Thanks for the answer.
Ian
Ian Robinson, Belfast, UK
<http://www.canicula.com/wp/>

Clustering on SQL Standard Edition?

I guess it is not possible. We can't afford the cost and at the same time downtime, I want to know if it is possible to simulate clustering using SQL Standard edition with minimal downtime. E.g., if I am running Windows 2000 Advanced Server in a cluster a
nd install SQL Server 1 on box1 and point to the data on the disk array and keep it running. When SQL server 1 crashes or stops functioning, can I start SQL Server 2 (on a diff box) and attach the databases on the disk array will it function smoothly. I g
uess it is a stupid question (it doesn't makes sense to me either), but if you have some insights on this please post them.
If you can count the cost of downtime then, depending on these costs to business, you may be able to justify to management the cost of moving to W2K Advanced Server or W2K3 Enterprise Edition and SQL2K Enterprise Edition. Introducing manual processes to
simulate High Availaible is a mistake. But you already knew that ;-)
|||Clustering is an Enterprise Edition feature only.
However, what you outline for a failover scenario will work exactly as you
expect it will. As long as the second SQL Server can gain access to the
data files, the SQL Server will start up cleanly with full access to your
data. The only thing you would have to manage is that your applications
have to be repointed to the new server.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I can completely understand the cost factor of implementing the cluster.
But the bigger question that needs to be asked is - does the downtime and
loss of productivity justify the savings or a workaround for NOT
implementing high availability clustering ? I guess, the mere fact that
your company is looking at clustering is that they value the high
availability aspect of running a database application and they value the
money lost in terms of lost productivity. From now onwards, it would be a
question of justifying the cost-benefit scenario to your BDMs.
If you try to manually point the database to the shared disk, its possible,
but why would you like to do that? There will be a lot of contingencies to
consider and plan for if we go the manual way.
I would sugges that you have a look at this link
MS SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
This is written for both Business and Technical folks and could give you
possible pointers on how you can achieve the functionality by justifying
the costs.
Hope this helps.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Clustering of SQL Server 2000(SE)

We

are looking for clustering of SQL Server 2000 [standard Edition] using

third party software, any inputs in this regard would be of great help

Microsoft does not support clustering SQL Server 2000 standard edition.

SQL Server 2005 standard edition supports 2 node clusters.

|||Kevin,
are there any third party softwares which can help us in cluster creation in SS2000(SE)?

Clustering of SQL Server 2000 SE

We are looking at clustering of SQL Server 2000 [standard Edition] using third party software, any inputs in this regard would be of great helpPlease post your question to the right forum: SQL Server Disaster Recovery and Availability

Clustered SQL 2000

I am currently running a SQL 2000 Ent Server sitting on a MSCS running W2K3 Ent Edition.

One of the nodes is giving me problems, and I suspect that it is due to the build.

I want to evict the offending from the cluster and then rebuild the OS from scratch, and then rejoin the cluster.

My question then is how do I install SQL 2000 Server, up to tha same revision as the existing node, on the newly installed node without overwritting any of the existing data?you should be able to do so just back up your databases system and user
when you reinstall sql server
the advanced options will prompt you for the cluster install.
but as you already know. you will have to update the installation to be as current in version as your existing node.