Collation Conflicts SQL 2005

Hi there,
We have recently upgraded to SQL 2005 standard from SQL 2000.
I have begun a new web project in Visual Studio 2005.
I have added a SQL data source to my project which contains a VIEW.
I am passing a single parameter to the DS to populate a gridview.
I get the following error when I try to:
A. Test the Datasource.
B. Load the page that contains a gridview that uses the DS.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I also get this error when creating stored procedures with an equal
operation.
Any steer in the right direction to resolve this issue would be greatly
appreciated.
Thanks in advance!
Hi
Have you checked the system collation and the column collations? At a guess
you upgraded/restored a backup with the SQL collation and installed SQL 2005
with the windows collation?
This may be the point where you decided to get rid of the SQL collation? In
which case I think the procedure is that same as SQL 2000, you will need to
alter the collation for the database alter the collation for each column that
uses it. These links should help http://tinyurl.com/429qa
http://tinyurl.com/91sg
John
"Codey" wrote:

> Hi there,
> We have recently upgraded to SQL 2005 standard from SQL 2000.
> I have begun a new web project in Visual Studio 2005.
> I have added a SQL data source to my project which contains a VIEW.
> I am passing a single parameter to the DS to populate a gridview.
> I get the following error when I try to:
> A. Test the Datasource.
> B. Load the page that contains a gridview that uses the DS.
> Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
> "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
>
> I also get this error when creating stored procedures with an equal
> operation.
> Any steer in the right direction to resolve this issue would be greatly
> appreciated.
> Thanks in advance!
>
>
|||Hi John,
Worked an absolute treat! - Thanks a million!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C315F5A7-2EFF-4C59-B5A9-1A8721534E19@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked the system collation and the column collations? At a
> guess
> you upgraded/restored a backup with the SQL collation and installed SQL
> 2005
> with the windows collation?
> This may be the point where you decided to get rid of the SQL collation?
> In
> which case I think the procedure is that same as SQL 2000, you will need
> to
> alter the collation for the database alter the collation for each column
> that
> uses it. These links should help http://tinyurl.com/429qa
> http://tinyurl.com/91sg
> John
> "Codey" wrote:

Collation Conflicts SQL 2005

Hi there,
We have recently upgraded to SQL 2005 standard from SQL 2000.
I have begun a new web project in Visual Studio 2005.
I have added a SQL data source to my project which contains a VIEW.
I am passing a single parameter to the DS to populate a gridview.
I get the following error when I try to:
A. Test the Datasource.
B. Load the page that contains a gridview that uses the DS.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I also get this error when creating stored procedures with an equal
operation.
Any steer in the right direction to resolve this issue would be greatly
appreciated.
Thanks in advance!Hi
Have you checked the system collation and the column collations? At a guess
you upgraded/restored a backup with the SQL collation and installed SQL 2005
with the windows collation?
This may be the point where you decided to get rid of the SQL collation? In
which case I think the procedure is that same as SQL 2000, you will need to
alter the collation for the database alter the collation for each column tha
t
uses it. These links should help http://tinyurl.com/429qa
http://tinyurl.com/91sg
John
"Codey" wrote:

> Hi there,
> We have recently upgraded to SQL 2005 standard from SQL 2000.
> I have begun a new web project in Visual Studio 2005.
> I have added a SQL data source to my project which contains a VIEW.
> I am passing a single parameter to the DS to populate a gridview.
> I get the following error when I try to:
> A. Test the Datasource.
> B. Load the page that contains a gridview that uses the DS.
> Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
> "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
>
> I also get this error when creating stored procedures with an equal
> operation.
> Any steer in the right direction to resolve this issue would be greatly
> appreciated.
> Thanks in advance!
>
>|||Hi John,
Worked an absolute treat! - Thanks a million!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C315F5A7-2EFF-4C59-B5A9-1A8721534E19@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked the system collation and the column collations? At a
> guess
> you upgraded/restored a backup with the SQL collation and installed SQL
> 2005
> with the windows collation?
> This may be the point where you decided to get rid of the SQL collation?
> In
> which case I think the procedure is that same as SQL 2000, you will need
> to
> alter the collation for the database alter the collation for each column
> that
> uses it. These links should help http://tinyurl.com/429qa
> http://tinyurl.com/91sg
> John
> "Codey" wrote:
>

Collation Conflicts SQL 2005

Hi there,
We have recently upgraded to SQL 2005 standard from SQL 2000.
I have begun a new web project in Visual Studio 2005.
I have added a SQL data source to my project which contains a VIEW.
I am passing a single parameter to the DS to populate a gridview.
I get the following error when I try to:
A. Test the Datasource.
B. Load the page that contains a gridview that uses the DS.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I also get this error when creating stored procedures with an equal
operation.
Any steer in the right direction to resolve this issue would be greatly
appreciated.
Thanks in advance!Hi
Have you checked the system collation and the column collations? At a guess
you upgraded/restored a backup with the SQL collation and installed SQL 2005
with the windows collation?
This may be the point where you decided to get rid of the SQL collation? In
which case I think the procedure is that same as SQL 2000, you will need to
alter the collation for the database alter the collation for each column that
uses it. These links should help http://tinyurl.com/429qa
http://tinyurl.com/91sg
John
"Codey" wrote:
> Hi there,
> We have recently upgraded to SQL 2005 standard from SQL 2000.
> I have begun a new web project in Visual Studio 2005.
> I have added a SQL data source to my project which contains a VIEW.
> I am passing a single parameter to the DS to populate a gridview.
> I get the following error when I try to:
> A. Test the Datasource.
> B. Load the page that contains a gridview that uses the DS.
> Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
> "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
>
> I also get this error when creating stored procedures with an equal
> operation.
> Any steer in the right direction to resolve this issue would be greatly
> appreciated.
> Thanks in advance!
>
>|||Hi John,
Worked an absolute treat! - Thanks a million!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C315F5A7-2EFF-4C59-B5A9-1A8721534E19@.microsoft.com...
> Hi
> Have you checked the system collation and the column collations? At a
> guess
> you upgraded/restored a backup with the SQL collation and installed SQL
> 2005
> with the windows collation?
> This may be the point where you decided to get rid of the SQL collation?
> In
> which case I think the procedure is that same as SQL 2000, you will need
> to
> alter the collation for the database alter the collation for each column
> that
> uses it. These links should help http://tinyurl.com/429qa
> http://tinyurl.com/91sg
> John
> "Codey" wrote:
>> Hi there,
>> We have recently upgraded to SQL 2005 standard from SQL 2000.
>> I have begun a new web project in Visual Studio 2005.
>> I have added a SQL data source to my project which contains a VIEW.
>> I am passing a single parameter to the DS to populate a gridview.
>> I get the following error when I try to:
>> A. Test the Datasource.
>> B. Load the page that contains a gridview that uses the DS.
>> Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
>> "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
>>
>> I also get this error when creating stored procedures with an equal
>> operation.
>> Any steer in the right direction to resolve this issue would be greatly
>> appreciated.
>> Thanks in advance!
>>sqlsql

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

Code Snippets

I am looking for a sample code to write a tool or a 3rd party tool that will enable me to get the same functionality as visual studio 2005 code snippets in SSMS.

We do not expose the necessary interfaces to have 3rd parties plug-in their components into SQL Server Management Studio in SQL Server 2005. We are looking at this for a future release. We do have a Template Explorer, which may be of use in the meantime.

Using SQL Server Management Studio Templates
How to: Use Templates in SQL Server Management Studio

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Maybe you are looking for something like this (with sourcecode)

http://www.codeproject.com/useritems/enisey.asp

|||

Paul I was able to use the templates, its just that I was hoping for similar style of code snippets that Visual Studio has, where I could right click and have some of common tsql scripts available while using a query window. But for now the templates look better than nothing. I am not sure where to put in a suggestions, but that would be my first to have code snippets like visual studio 2005 uses in ssms.

Jurgen thanks for sending the like, but I am looking for more custom snippet storage not neccessarily code generation tools. But Thank you for the suggestion.

Thanks Troutbum!

|||

We have a customer portal where you can file suggestions. It's called Microsoft Connect. You can submit your suggestion here: http://connect.microsoft.com/SQLServer/.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

code snippets

Goodday

Can anyone tell me if sql server managment studio has the facility of code snippets in the editor.

Thanks

Rob

hi Rob,

Visual Studio does, but not SSMS..

regards

|||

Not quite true.

Management Studio (Express or otherwise) has something called the Template Explorer that contains a batch of common T-SQL templates. You can access the Template Explorer in SSMSE by:

Picking it off the View menu

Coalesce breaking SQL statement in SSRS?

I've run into what may an odd bug. I'm using SQL Server 2005, and
Visual Studio 2005. I was provided with a SQL statement (that is, I
did not originally write the SQL) for use in creating a simple
manifest report. Names are changed for security, but it is like so:
SELECT DISTINCT
A.NAME [name],
B.NAME [item_type],
CASE B.NAME,
WHEN N'option1' THEN COALESCE( C.name, N'')
WHEN N'option2' THEN COALESCE( D.name, N'')
WHEN N'option3' THEN COALESCE( E.name, N'')
WHEN N'option4' THEN COALESCE( F.name, N'')
WHEN N'option5' THEN COALESCE( G.name, N'')
...
FROM TABLE_A A
INNER JOIN TABLE_B B with (nolock) ON B.id = A.id
INNER JOIN TABLE_C C with (nolock) ON B.id = C.id
LEFT OUTER JOIN TABLE_D with (nolock) ON B.guid = D.id
LEFT OUTER JOIN TABLE_E with (nolock) ON B.guid = E.id
LEFT OUTER JOIN TABLE_F with (nolock) ON B.guid = F.id
LEFT OUTER JOIN TABLE_G with (nolock) ON B.guid = G.id
...
and onward, for 27 tables. There is no WHERE clause.
The quest runs perfectly fine in the SQL Server Management studio,
returning around 4000 rows in under one second. However, when I try to
run the same statement in an .RDL file, the query takes as long as
10-15 minutes, and locks up the Design Studio while it waits. If I
remove the COALESCE statements (i.e. I replace COALESCE( C.name, N'')
with ( C.name) ), the problem disappears entirely.
I have tried using ISNULL as well, and the problem remains. I have
tried creating an entirely new solution, new .RDL, new .RDS, with no
luck. sp_who2 indicates the the process is sleeping, no CPU usage or
DiskIO.
Has anyone seen this before, or have an idea?On Oct 1, 10:05 am, avat...@.gmail.com wrote:
> I've run into what may an odd bug. I'm using SQL Server 2005, and
> Visual Studio 2005. I was provided with a SQL statement (that is, I
> did not originally write the SQL) for use in creating a simple
> manifest report. Names are changed for security, but it is like so:
> SELECT DISTINCT
> A.NAME [name],
> B.NAME [item_type],
> CASE B.NAME,
> WHEN N'option1' THEN COALESCE( C.name, N'')
> WHEN N'option2' THEN COALESCE( D.name, N'')
> WHEN N'option3' THEN COALESCE( E.name, N'')
> WHEN N'option4' THEN COALESCE( F.name, N'')
> WHEN N'option5' THEN COALESCE( G.name, N'')
> ...
> FROM TABLE_A A
> INNER JOIN TABLE_B B with (nolock) ON B.id = A.id
> INNER JOIN TABLE_C C with (nolock) ON B.id = C.id
> LEFT OUTER JOIN TABLE_D with (nolock) ON B.guid = D.id
> LEFT OUTER JOIN TABLE_E with (nolock) ON B.guid = E.id
> LEFT OUTER JOIN TABLE_F with (nolock) ON B.guid = F.id
> LEFT OUTER JOIN TABLE_G with (nolock) ON B.guid = G.id
> ...
> and onward, for 27 tables. There is no WHERE clause.
> The quest runs perfectly fine in the SQL Server Management studio,
> returning around 4000 rows in under one second. However, when I try to
> run the same statement in an .RDL file, the query takes as long as
> 10-15 minutes, and locks up the Design Studio while it waits. If I
> remove the COALESCE statements (i.e. I replace COALESCE( C.name, N'')
> with ( C.name) ), the problem disappears entirely.
> I have tried using ISNULL as well, and the problem remains. I have
> tried creating an entirely new solution, new .RDL, new .RDS, with no
> luck. sp_who2 indicates the the process is sleeping, no CPU usage or
> DiskIO.
> Has anyone seen this before, or have an idea?
A couple of suggestions. Firstly, if you aren't already, you will want
to create a stored procedure out of the query above and access it in
the report. This should improve your performance a little. Secondly,
you might want to run the SQL Profiler while you are running the
report to see what kind of processing is going on and if there is an
exception/error hidden somewhere, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant