Does anyone know if there is a command in query analizer for sql server 2005 (Express) that changes the collation of all the char (varchar, nvarchar etc) in a table or in a database. And I don't mean to set the collation when creating a database, but to modify a database's collation seting.
thank you
This information might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_3zw3.asp
Buck Woody
Can SQL Express reside on the same server as MSDE and both run at the same time (different apps)?
I have one app that runs on MSDE and a new to be developed app by a vendor that needs SQL Express.
Thanks
JB
Yes, that is possible. When you start the SQL 2005 Express setup, it will detect the MSDE and ask if you wish to upgrade or install a new instance. Choose a new instance.
You may wish to read in Books Online about the topic: "Instance Naming Page"
can sql2000 and sql express 2005 coexist on the same machine?
if so, are there any rules or restrictions I must follow when installing sql express?
TIA!
Just specify another instance name and another port (if using TCP/IP), thats all.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
If you have control over such things, make sure to install SQL 2000 before SQL 2005. There is an issue with SQL Browser and instance discovery when you install them in the wrong order. Check out the KB at http://support.microsoft.com/kb/905618/en-us for details.
Mike
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,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
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
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,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
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
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
|||
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,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
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
