One of the typical uses of DMO was to instantiate COM objects via t-sql either via stored procedures or ad-hoc submissions through Query Anaylzer/OSQL/ISQL. This allows me to construct helper scripts that have access to objects outside the SQL Server process space, and I don't have to create any application (console or gui) to do what I need.
It seems that SMO is not meant to be 'interface-less' as we could do with DMO, is this true? If this is the case, can we plan on either SMO being able to instantiate objects without an interface, or can we depend on DMO hanging around for a little while longer, while SMO "ramps up"?
Or, should I just start planning on learning how to create my own 'interface-less' objects via CLR, which seems to be the only choice (sofar)?
You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO.Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Some tutorials on using SMO with PowerShell...
http://www.simple-talk.com/sql/database-administration/managing-sql-server-using-powersmo/
Dan
|||The main reason I prefer DMO is that it's much easier to encapsulate it into t-sql and have it run from within sql server (job/sp/batchfile). I don't want to have to start writing applications to do what I used to be able to do with DMO.
As an example, I have an sp that gets executed via job; the sp uses DMO in it's body to determine the drive space information on the server and the job emails the resultset as an attachment. Very easy and tidy, one job and one sp.
Now that DMO is being deprecated, I have to get rid of my sp code and create an application, or invoke a powershell script instead of just running an sp and emailing the results as a text file. Can I do this same exact operation with SMO, and not require an application interface?
It just seems a bit odd, that something as core as the way DMO can be used without an interface isn't part of SMO... I can't be the only one out here who does things in this manner.
|||
Jens K. Suessmeyer wrote:
You should really consider using SMO in your own applications or via the new WIndows PowerShell. This will give you much more control, easier maintainance and coding compared to DMO. Jens K. Suessmeyer.
http://www.sqlserver2005.de
I'm a DBA looking to manage my servers without any more applications than neccessary, not a developer writing software...
|||Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.
Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.
You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.
You are using that COM interface to call a large complex COM library whos primary function is to
Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.
If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.
|||
Powershell is designed for admins and by building T-SQl that calls DMO you are actually developing software.
Pause and think about what is happening in your scenario and why it will negatively impact the reliability of you server.
You are using T-SQL code to call the SQL Server oa(I presume) extended procedures, that provide a COM interface.
You are using that COM interface to call a large complex COM library whos primary function is to
Generate T-SQL and call SPs in the server through ODBC, performing T-SQL tasks.
If you are going to do this why not write it in T-SQL as SPs in the server in the first place, or if you want an easier API then use PowerShell or VB.Net to call SMO from outside the server.
It is "tough" to re-write in TSQL what SMO *already* have. We (DBAs with large number of server / databases) used DMO out-of-the box and yes sp_OA* to automate "EASILY" across servers. Now you are asking to deploy PowerShell (another add-on) in order to use SMO from TSQL. Not easy to deploy it all over the place.
|||
Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).
Neither SMO nor DMO was designed to be called inside the server, there is at least one memory leak in DMO that can not be fixed and there are lots of threading issues. I strongly encourage you not to do it until there is a version desiged to be called inside the server.
|||
Actually no I am not saying that, sorry I was not clear. I am saying why call from SMO or DMO from inside SQL Server at all. If you are inside SQL Server use T-SQL, if you are outside use DMO or better yet us SMO, either directly or via powershell(which be included in the OS at some point and hence no need to deploy).
I have to side with Noeld still on this. Most DBA's are aware that there are potential issues with using the sp_OA* procedures internally. However, most of us are not creating large DMO objects internally. Most of us are going after configuration values (like, say, BackupDirectory) which is extremely difficult to get to via t-sql without DMO (it can be done, but it's a LOT more code). Myself, I've been using a custom set of routines that I've written over the years on several hundred servers and only once have I had an issue with DMO causing an error on the server.
Being able to query for configuration values internally means that I only have to deploy my code to the server and it is 'self-contained' at that point. Why not use what we run (SQL Servers) to get the information we need? Why provide the sp_OA* procedures in the first place if they weren't meant to be used (just being rhetorical)?
I'm very glad to see that this topic got a few more replies, this is a topic I think is quite mis-understood.
|||sp_Oa was provided as a technology solution and it still provides a solution today, thats not to say that I would recomend it. A couple of other examples, SQLMail, in its time was a really cool feature, but on reflection calling MAPI(a non thread safe client focussed API) from inside an Extended Stored Procedure is not going to increase the reliability of your server. SQL Server still supports XPs, I would always look to do something in SQLCLR before an XP however.
Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server
|||
Yes getting config information out of the server should be easier and hopefully it will get better, for me what I would do is use profiler to sniff the T-SQL from DMO and then write some utility procs of my own that wrap the functionality, thus easing the risks on the server
That's mostly how I came up wtih the DMO scripts I use today, sniffing EM and whatnot... I'll have to look into sniffing the DMO itself though, that I haven't tried.
