If you've used SQL-DMO very much you may have noticed that there are lots of
almost duplicate objects. Examples are SQLServer vs SQLServer2, Database vs
Database2, and many others. One question I get a lot is "which object
should I use?", which is a fair question and worth a brief discussion.
To start with, you'll only see the "2" objects if you're using
SQL2K (or at least the SQL2K DMO library). Let's take the SQLServer object as a
good example. If you look in the help file in SQL7 or SQL2K, you'll get a huge
list of properties and methods, which I'm showing here to save you a trip to
Then if you look at the SQLServer2 object you'll see that it includes
everything in the SQLServer object plus the following additional properties and
I know I know, I haven't answered the question yet!
First, let's talk about why we have two sets of very similar objects. Object
interfaces...the properties and methods that you can see above...are a contract
with you the programmer. That contract is commonly called an interface. Once
published, you're supposed to be guaranteed that if you set the
loginsecure property (or any other, it's just an example) to true it always
means the same thing and that the loginsecure property will always be there. You
can release upgrades/revisions where you have new and improved code inside the
properties or methods, but the name and the end result have to stay the same.
Just so we all stay sane.
That's what you get with SQL7 - a new release of SQL-DMO that goes with the
server and naturally doesn't support a method such as "listinstalledinstances"
because in SQL7 there are no instances.
Now MS decides to build SQL2K and add in a lot of neat stuff that we've been
requesting, named instances (who asked for that??) being one good example. Now
they need to add support for it to DMO, but need to do so without breaking all
the code that is already out there. That means they can't just add the
ListInstalledMethods to the SQLServer object. The best way to solve this issue
(and Im only guessing that this is what they did) is to use inheritance to
create a new object called...SQLServer2. It inherits all the properties and
methods of the SQLServer object and adds a few new ones.
Now we get closer to the answer. If you need to access SQL2K specific
functionality you have to use a '2' object. If your code will never run on a
SQL7 box, use the '2' object. What if your code will run on both versions? You
have two choices. The first is to avoid the '2' objects, basically coding for
the lowest common denominator. The other is to use the '2' objects and test very
early on to determine which version of SQL is running. If you detect it's SQL7
then you..the weary developer...have to code so that you never use any of the
SQL2K specific functionality in the objects.
Should you upgrade your code to use the '2' objects? Almost the same answer.
If you need the functionality, yes. If not, don't sweat it.
All in all it's a fairly decent solution. Should be interesting to see if
is released we get a set of '3' objects!