Use SQL-DMO, ADO or ADO.NET?

[serge]

I've used ADO before and now SQL-DMO to execute
SQL statements on a SQL Server database.

I was just thinking if SQL-DMO has any capabilities
that ADO.NET does not have when it comes to
having a programming interface to SQL Server?

Would you know if I should not waste my time learning
SQL-DMO and rather learn/use C# (ADO.NET) instead?

Does SQL-DMO exist in SQL 2005?

Thank you

[Simon Hayes]

Re: Use SQL-DMO, ADO or ADO.NET?

DMO and ADO have different goals. DMO provides an API which is used for
administrative scripts and tools, so it's very specific to MSSQL; ADO
provides a more generic data programming API, which is intended for
executing queries and processing result sets using different data
sources. Some things may only be possible using one of the two APIs,
eg. the only way to generate CREATE scripts in MSSQL is using DMO.

So if you want to script or automate DBA tasks, or develop an admin
application, then DMO is usually a much better choice; if you're
writing business applications, where you need to process and manipulate
data efficiently, then ADO would be preferred.

In SQL 2005, DMO has been split into SMO and RMO, and instead of being
COM interfaces, they are now .NET assemblies. Since you can already use
COM APIs like DMO from C# or other .NET languages, the most
future-proof plan is probably to learn a .NET language, since you can
then use DMO, ADO, ADO.NET and SMO/RMO.

Simon

[serge]

Re: Use SQL-DMO, ADO or ADO.NET?

> So if you want to script or automate DBA tasks, or develop an admin[color=blue]
> application, then DMO is usually a much better choice; if you're
> writing business applications, where you need to process and manipulate
> data efficiently, then ADO would be preferred.[/color]

I understand better now, however would you know if executing
SQL script files that create a database (tables, indexes, constraints,
functions, stored procedures, triggers, ...) in ADO would execute
faster than in SQL DMO? I am currently building the database using
SQL DMO and if I understand your explanation well, you say using
ADO would be preferred if processing/manipulating data.

My SQL scripts are mainly CREATE scripts, would they fall under
the SQL DMO choice or ADO?

Or there probably won't make a speed difference if executed using
DMO or ADO?

Thank you

[pb648174]

Re: Use SQL-DMO, ADO or ADO.NET?

You can just run osql on the command line against a SQL file.

[Erland Sommarskog]

Re: Use SQL-DMO, ADO or ADO.NET?

serge (sergea@nospam. ehmail.com) writes:[color=blue]
> I understand better now, however would you know if executing
> SQL script files that create a database (tables, indexes, constraints,
> functions, stored procedures, triggers, ...) in ADO would execute
> faster than in SQL DMO? I am currently building the database using
> SQL DMO and if I understand your explanation well, you say using
> ADO would be preferred if processing/manipulating data.
>
> My SQL scripts are mainly CREATE scripts, would they fall under
> the SQL DMO choice or ADO?[/color]

For object creation I would rather go with SQL-DMO, if the alternative
is ADO. Personally, I prefer to use SQL statements, but I would never
use ADO for this sort of thing. This is because the error handling is
so poor in ADO, and ADO may see fit to things behind your back. I have
never used SQL-DMO, but it cannot be worse than the ADO crap.

I should add that ADO .Net is something different. ADO .Net is better
on error handling, although 1.1 has some defeciencies. SqlClient 2.0
has about none, provided that one sets a connection property.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Simon Hayes]

Re: Use SQL-DMO, ADO or ADO.NET?

Personally, I would use either osql.exe from a batch file, or perhaps
the SQLDMO ExecuteImmediat e method from a script. If you need to run
only a few files, a batch file is often the quickest, easiest solution.
For more complex deployments, where you might need conditional
execution of scripts, per-server logic, custom logging etc. then a
script in a language like Perl, Python etc is probably a better
solution. In scripts like that, DMO is a good way to get information
about server and object properties, and perhaps set/create them,
although I generally prefer to use SQL scripts for that.

Simon

[serge]

Re: Use SQL-DMO, ADO or ADO.NET?

I am currently using VB and SQL DMO.
I am looping in a folder and reading each file into a string variable
and then using ExecuteImmediat e to create the SQL server objects.

I don't want to use OSQL as running the SQL DMO is much faster
than OSQL.

I'll stay away from ADO and try to learn ADO.NET with
SqlClient 2.0.

I'm also going to keep in mind to read more about 2005's SMO
and RMO.


Thank you all for the replies.