collation ansi padding and trailing blanks

Hi,

This might sound obvious, or a newbie question, but how are trailing blanks treated by SQL2005 on varchar columns?

I have a column where two rows only differ by a trailing blank. If write a select and a where clause on the column, anly trailing blanks seem to be trimmed. I tried the ansi padding setting but it doesn't change anything. Is it a question of collation? I have default collation on the server set to SQL_Latin1_General_CP1_CI_AS...

The problem also seems to arise when I try to create a unique index on the column, where both values are considered equivalent...

I give here a sample based on the BOL for set ansi_padding. I was expecting each of the select statements below to retrun only one row...

Cany somebody please explain why they all return two rows?

PRINT 'Testing with ANSI_PADDING ON'

SET ANSI_PADDING ON;

GO

CREATE TABLE t1 (

charcol CHAR(16) NULL,

varcharcol VARCHAR(16) NULL,

varbinarycol VARBINARY(8)

);

GO

INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);

INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t1

where varcharcol='Trailing blank';

GO

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t1

where varcharcol='Trailing blank ';

GO

PRINT 'Testing with ANSI_PADDING OFF';

SET ANSI_PADDING OFF;

GO

CREATE TABLE t2 (

charcol CHAR(16) NULL,

varcharcol VARCHAR(16) NULL,

varbinarycol VARBINARY(8)

);

GO

INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);

INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t2

where varcharcol='Trailing blank';

GO

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t2

where varcharcol='Trailing blank ';

GO

DROP TABLE t1

DROP TABLE t2

ANSI padding setting only affects the storage and how the trimming of blanks is performed for non-unicode data. It doesn't change the search semantics. SQL Server will always ignore trailing blanks / spaces for equality searches. If you perform the same using LIKE then trailing blanks will be considered. If you do the query below after inserting the data, you will see how the storage differs when ANSI_PADDING is ON and OFF.

select datalength(charcol), datalength(varcharcol)

from t1

select datalength(charcol), datalength(varcharcol)

from t2

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 formatting for SQL2005 TSQL

Hi !
I have a monster case statement in TSL, autogenerated with some external
program I do not have access to.
The source file is about 4K in size, and the resulting source code probably
will have a few hunderd lines of code if formatted properly for reading.
Is there a feature in SQL 2005 management studio for code formatting ? BIDS
then ?
If not, a reccommendation for a tool for code formatting, preferably free,
would be greatly appreciated.
Regards,
Palli
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:ejv5S05nHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Hi !
> I have a monster case statement in TSL, autogenerated with some external
> program I do not have access to.
> The source file is about 4K in size, and the resulting source code
> probably will have a few hunderd lines of code if formatted properly for
> reading.
> Is there a feature in SQL 2005 management studio for code formatting ?
> BIDS then ?
> If not, a reccommendation for a tool for code formatting, preferably free,
> would be greatly appreciated.
> Regards,
> Palli
Like this?
http://www.simple-talk.com/sql/sql-tools/the-simple-talk-sql-prettifier/
|||>
> Like this?
> http://www.simple-talk.com/sql/sql-tools/the-simple-talk-sql-prettifier/
>
Thanks for the link!
It does half the job, but not quite. It gets confused when 5 or 6 case
statement's END appear in a row!
Any other free options ?
Palli
|||"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:ealOwM8nHHA.4424@.TK2MSFTNGP03.phx.gbl...
> Thanks for the link!
> It does half the job, but not quite. It gets confused when 5 or 6 case
> statement's END appear in a row!
> Any other free options ?
> Palli
If you report that as a possible bug to the author, I imagine he'll jump in
and try to fix it. I don't really know of any other free options myself. I
have heard about some old VB6 trick to access some undocumented feature of a
system DLL to format SQL/T-SQL Code. Not sure where the info is located for
this trick exactly, or if it would even work for SQL 2005, but you might be
able to Google something on it. The problem is that generally a custom code
prettifier for a language as complex as SQL/T-SQL would require you to
implement an LALR parser and a complete SQL/T-SQL grammar from scratch, or a
very deep (read: "slow") recursive-descent parser. For most people it's
probably just a lot easier to prettify their code by hand or just pay for a
pre-built package.
|||Try http://www.sqlinform.com/ and
http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
TheSQLGuru
President
Indicium Resources, Inc.
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:ealOwM8nHHA.4424@.TK2MSFTNGP03.phx.gbl...
> Thanks for the link!
> It does half the job, but not quite. It gets confused when 5 or 6 case
> statement's END appear in a row!
> Any other free options ?
> Palli
>
|||Now that link is too cool I've heard of the GSP, but didn't realize
someone had set up a Web Service to use it.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OHbsgqYoHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Try http://www.sqlinform.com/ and
> http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Pall Bjornsson" <palli@.kvos.is> wrote in message
> news:ealOwM8nHHA.4424@.TK2MSFTNGP03.phx.gbl...
>
|||> Try http://www.sqlinform.com/ and
> http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
>
This is really cool... both of those tools. Thanks a lot.
Palli
|||"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:uWen8lpoHHA.3460@.TK2MSFTNGP04.phx.gbl...
> This is really cool... both of those tools. Thanks a lot.
> Palli
BTW, Phil Factor (the author of the one I sent you) has updated his code.
He also has the source code for the T-SQL stored procedure available for
download. Not sure if it fixes the bug you pointed out, but it might.

cobol -> SQL2005

I'm not "cobol person" but now, I have to bind a cobol application (from mainframe) to query SQL 2005
Have somebody had this task?No, but it should be pretty straightforward... All of the mini / mainframe products I've seen use ODBC to allow native applications running on the host (the mini or the mainframe) to bind to the PC database. Operative word being "should", this should be quite straightforward, simply changing the ODBC driver referenced and setting the server / database / security settings appropriately.

-PatP|||No, but it should be pretty straightforward... All of the mini / mainframe products I've seen use ODBC to allow native applications running on the host (the mini or the mainframe) to bind to the PC database. Operative word being "should", this should be quite straightforward, simply changing the ODBC driver referenced and setting the server / database / security settings appropriately.

-PatP

Thanks Pat
I try to find communication between Mainframe and PC db throgh ODBC :).
This cobol-programm never have been running to bind to the PC db - it is working with mainframe DB2. This is tamporary deal (we are going from DB2-to SQL2005) I could write this from PC using VS C# for example and hit mainframe, but I have to save even interface of this "stupid" programm :).

thanks again|||how about "Connect Direct" or "Direct Connect" ?|||wath do you mean?

Actually, now I'm studing how to do this for IBM CICS environment that uses EXEC SQL CONNECT to DB2 to change to SQL2005

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/>