FAQ: Installing SQL Express side by side with SQL 2000 Enterprise Manager seems to break it, its still broken when I uninstall express, whats going on?


Because of changes in the SQL Server 2005 metadata we have updated SQLDMO, the new version is SQLDMO9, it should work with SQL Server 7/2000 and 2005. For backwards compatibility it is installed by SQL Express so that existing DMO apps should just work. Due to the way that SQLDMO is versioned, only one version can exist on a machine at one time, so when Express is installed the SQLDMO9 version is the one that is registered. Having explained the scenario lets take a look at potential problems:

1/ Looking at permisions of objects in EM does not work.

Like the rest of the current version of SQL Express this is a pre-release version, the permissions problem is a bug which we have resolved in the B2 build of SQL Server 2005. If you need to use EM before we update SQLDMO and Express, then go to the version of SQLDMO.dll thats in SQL Server 2000 directory and execute regsvr32 sqldmo.dll. This should enable EM to work as it was before against SQL Server 2000 but neither EM nor DMO will work against SQL Express in this case.

 

2/ Uninstalling SQL Express breaks DMO and Enterprise Manager

When SQL Express installs it registers SQLDMO9 and when it uninstalls, it de-registers it, but if there was a SQLDMO (SQL2000 or SQL7) version on the machine previously this registration is not put back, leaving the machine with no SQLDMO registered. The resolution to this is the same as the above, re-register the right version of SQLDMO.

 

-Euan Garden

Product Unit Manager

SQL Server Tools

Comments (7)

  1. OK, well obviously #2 should be resolved for the shipping product….

  2. David Hayden says:

    Sure, now you mention it after I figured this out for myself (re-registering SQLDMO) last week when I couldn’t rename a single table in SQL Server using EM 🙂

    I would really be interested in a step-by-step process on how to register MACHINENAME/ASPNET as a admin user in SQLEXPRESS for my web applications. I keep getting an error that says ASPNET does not have privileges when using SQLEXPRESS in web applications on the localhost. I have been told I can use one of the sp_grantdbaccess stored procedures or something, but I don’t know how to run that in SQLEXPRESS from the command line or however it needs to be run. Can you provide a detailed example on how to do it? Thanks.

  3. Euan Garden says:

    I’m working on some Express samples with SMO right now, I’ll add it to the list.

  4. Ray Metz says:

    The fix worked great. Thanks!