Using SMO for SQL Server 2008 Express AND SQL Server 2005 Express

[AndrewW]

Hello,

We have an application that uses SMO (SQL Server Management Objects) to
interact with SQL Server.

In the previous version of our product, we only supported connecting to SQL
Server 2005 Express Edition. However, in our latest version we wish to
support both SQL Server 2005 and 2008.

In order to connect to SQL Server 2008 Express Edition, we found that we had
to download and install the latest ‘Microsoft SQL Server 2008 Management
Objects’ component (file SharedManagementObjects.msi from link
http://www.microsoft.com/downloads/...ef-ba29-4a43-8d69-a2bed18fe73c&DisplayLang=en).
Building against these libraries allowed our application to work with SQL
Server 2008 Express.

However, after compiling against the latest SMO libraries when we now try to
run our application on a system where only SQL Server 2005 Express is
installed, our application fails with the following exception: “Could not
load file or assembly 'Microsoft.SqlServer.BatchParserClient,
Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of
its dependencies. The system cannot find the file specified.â€

We would like our application to be able to work with both SQL Server 2005
Express and SQL Server 2008 Express but it seems that our application now
demands the latest libraries (v10.0) on the target system and that these
libraries do not exist when only SQL Server 2005 Express is installed.

What is the suggested practice for writing an application that can interact
with both SQL Server 2005 Express and SQL Server 2008 Express through SMO?
Alternatively, is there a download (or redistributable installer) that will
update the SMO libraries for existing SQL Server 2005 Express users?

Thank you very much for your assistance.

 

[Gregory A. Beamer \(Cowboy\) - MVP]

You can set up builds that include only the 2005 reference on machines that
do not have the latest libs.

You can also bootstrap the libs, i would assume, if not include them in your
installer (not sure with SMO the method of deploying, however -- they may
require GAC (more from a EULA standpoint than anything else)).

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://feeds.feedburner.com/GregoryBeamer#

or just read it:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************

 

[Guest]

Thank you for your suggestions for this problem.

Is there any chance you could clarify what you had in mind here:

You can set up builds that include only the 2005 reference on machines that
do not have the latest libs.

I was not sure whether you were suggesting changing the references
according to what's available at build time, or whether we can set up
our builds to dynamically manage which libraries are loaded at runtime
(or if perhaps you were suggesting something else entirely ).

I would like to clarify that in our scenario, the build machine has
both 2005/v9 libraries and 2008/v10 libraries installed (and thus
available for reference at build time), but our customer machines may
have only one of SQL Server 2005 or SQL Server 2008 installed.

 

[Rapunzel]

As I investigate this issue (and become more familiar with Windows deployment
in general and SMO and SQL Server in particular), I find I am able to
describe our situation a little better (and more accurately).

I now understand that in fact, when we package our application with
InstallShield, InstallShield bundles our SMO references into our installer.
So I was incorrect earlier when I thought we were not redistributing the SMO
libraries.

In fact, we see these behaviours:

1. If we reference SMO 9 in our .csproj, build our application, and install
it on the test computer, we see SMO v9.00.3042.00 libraries have been copied
into our application folder along with our own components. This build will
work with SQL Server 2005, whether local or remote. (Note that in the local
case, there will also be copies of v9.00.242.0 of the SMO libraries located
in C:\Windows\assembly.) If we run this build against SQL Server 2008 we get
the ConnectionFailure Exception "This SQL server version (10.0) is not
supported." (This seems reasonable, since SQL Server 2008 did not exist when
these libraries were released.)

2. If we reference SMO 10, we see SMO v10.0.1600.22 libraries copied into
our application folder. This confirms that we have RTM and not RC0 versions.
This build will work with SQL Server 2008, whether local or remote. If we
run this build against SQL Server 2005, we get the error "Could not load file
or assembly 'Microsoft.SqlServer.BatchParserClient, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies.
The system cannot find the file specified."

In the case of (2), we noticed that Microsoft.SqlServer.BatchParserClient
was not among the SMO libraries copied by our build process, so we put in a
little hack to copy this dll as well. Afterwards, if we attempt to test
against SQL Server 2005 we get a similar error message, this time for
Microsoft.SqlServer.BatchParser.

Some specific questions fall out of this (aside from the general question of
how to make this work as simply as possible):

a) It's possible we follow this process and add hacks that will copy over
each missing dll, but will we run into problems with platform-specific dlls?

b) Now, I noticed that when we are testing our SMO 10 build against SQL
Server 2005, the server is a local instance. Thus, there are v10 SMO
libraries in our application folder and v9 libraries in C:\Windows\assembly.
Can this be contributing to our problem?

c) Has SMO v9 ever been updated to support SQL Server 2008? I have seen
claims that it would be, but I have not been able to find a download that
includes this support.

Thanks to anyone with advice!

 

[Jos G]

Hi,

I'm facing exactly the same problem. Did you get a solution for this? Anyone?

Thanks in advance,
Jose

 

[ganeshi]

Hi, have you got any lead on this? I am looking for the solution of the
similar issue. I have SMO 9 binaries in my application folder and SMO 10
is installed on the machine. My application fails to load the SMO 9 assembly.

Thanks.

 

[M M]

Hi Guys,

I had the same issue...

Actually I was trying to install a software called Eservicedesk to our local server and we have our database located to a windows remote server W2k3 - Sp2 on which all ports are opened...

While installing it - I got the same error then after some research noticed that he is looking for the "microsoft.sqlserver.batchparserclient dll"...

Previously I had install the SMO and SQLClrType on the database server so had checked - the dll was located in the GAC however the same was not located on our local host (the app server) so install the SMO and SQLCLrType to the localhost and it worked...

hope it helps!