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

Books Online:

Properties

Methods

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

methods:

Properties

Methods

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

when Yukon

is released we get a set of '3' objects!