.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL Server and DMO: Distributed Management Objects Enable Easy Task Automation

Posted By:      Posted Date: August 21, 2010    Points: 0   Category :ASP.Net

SQL Server can be administered programmatically using system stored procedures, but Distributed Management Objects (DMO) offer a more modern, object-oriented alternative. This article introduces SQL-DMO in SQL Server 7.0 and SQL Server 2000 and describes the SQL-DMO object model, then focuses primarily on the Databases tree and the JobServer tree of the object model. The sample code and the article show how to use various objects such as the Registry object, the Configuration object, and the Database object to automate common administration tasks such as programmatically retrieving configuration settings, creating new databases, applying T-SQL scripts, and creating and scheduling backups.

Francesco Balena

MSDN Magazine May 2001

View Complete Post

More Related Resource Links

Data Points: SQL Server Management Objects


SQL Server Management Objects offer developers a robust toolset for backing up and restoring databases, and issuing DDL commands, as John Papa explains.

John Papa

MSDN Magazine June 2007

Error 2908 while installing sql server 2005 management objects collection on Win 7

I have SQL Server native client 2005 installed on Win 7 64-bit machine. While installing SQL Server XMO 2005 it gives error 2908 and aborts installation. Any suggestion on the error?

PS2007/MSSQL2008: Cannot install Microsoft SQL Server 2005 Management Objects Collection (sqlserver2

Hi folks, about 1,5 months ago I have set up a - 1 server - development environment for PS2007. I had installed all components on one Windows 2008 box. Everything worked fine, now I receive an error. •MOSS 2007 •SQL 2008 SP1 •MOPS 2007 SP2 I have done the additional installation for the cube building part according to this page http://technet.microsoft.com/en-us/library/dd285466(office.12).aspx Microsoft SQL Server Native Client (sqlncli.msi) Microsoft SQL Server 2005 Management Objects Collection (sqlserver2005_xmo.msi) Microsoft SQL Server 2005 Backwards Compatibility Components (SQLServer2005_BC.msi) Everything worked fine. Cubes were able to be built.. everything was good. In order to have Reports built I added SSRS 2008 Reporting Services Addin for MOSS (rsSharePoint_x64) Business Intelligence Development Studio (=Visual Studio for MSSQL) No CUs have been added to this box. NOW, Cube creation fails with the error: ULS LOG: [CBS] ProjectServerOlapCubeGenerator status: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server <IPADDRESS>. Error: ActiveX component can't create object 08efbc13-299a-4abe-9021-c46887ca6915 Eventlog says: SSP Name: SharedServices1 PSError: CBSASConnectionFailure (17003) Cube build failed to connect to the Analysis Services server. Verify the data source connection i

Transfer SQL Server Objects Task

I'm trying to use the Transfer SQL Server Objects Task to copy database users and database roles from one database to another. The problem is that some of the users already exists in the destination database. Is there a setting or expression or error handler that will allow me to specify to only copy the objects that don't already exist? I can ignore the failure but I won't know if it's really a copy failure or a duplicate. I read the roll-your-own blog referenced in a similar post (http://blogs.msdn.com/b/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx) but I don't know if a property exists for the transfer object with will allow me to indicate that I want to copy users that aren't already in the destination. Has anyone successfully done this? It seems like it would be a simple task.

Does Transfer SQL Server Objects task transfer objects created in the source AFTER the package has b

I created an SSIS package which contains a Transfer SQL Server Objects Task. I configured this task to copy table objects, stored procedures, and object permissions to the destination. Between the time I created my SSIS package and the time it was run, someone created a new table object in the source, and changed permissions on a stored proc in the source. My question is this, at the time the SSIS package is created, behind the scenes, does SSIS create a list of objects to transfer? I had hoped that it creates the list of what specific objects (of the pre-defined type) to transfer at runtime so that whatever changes were made to the source database would be included at runtime.

Is there any way to get the Transfer SQL Server Objects Task to not throw error if an object already


I've asked this before but never got an answer. Is there a way to configure the Transfer SQL Server Objects Task so that it will only transfer objects that don't already exist in the destination? Or to skip over objects that already exist?

I do not want to "roll my own". I want to use the task in order to save time.

Why does BI "Transfer SQL Server Objects Task" error occur?


I'm using SSIS to copy all tables and the data from server1 to server2.  Database names are same on both source and destination servers. dbo.MyTable definately exists in the source so I don't understand this error message:

 [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E37  Description: "Invalid object name 'dbo.MyTable'.".  helpFile=dtsmsg100.rll helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".

 There's nothing fancy about MyTable:

CREATE TABLE [dbo].[MyTable](

[MyId] [

Can't find "Analysis Management Objects" in SSIS Script Task reference



I am trying to connect to an Analysis services cube through my script task. To achieve this, i will have to use the Microsoft.AnalysisServices namespace. For this, I need to add a reference in my Project explorer space, to "Analysis management Objects" . However, I am not able to find this. Does any one know where I can find the dll file required for this, or why it's not showing in the 'Add Reference' of my script task?



--Mark the thread as answered if one of the replies answers your question. Thank you

"Transfer SQL Server Objects Task" doesn't always copy the data when it's configured to do so

Can someone enlighten me on why this is happening? I have three instances of the "Transfer SQL Server Objects Task" in an SSIS package, each instance copies tables and data from a different source database. Two of the three copy the data without a problem, but one of them does not copy the data even though I have set the CopyData property = true.  And it does not give an error. The status at the end reports Success. Why is this happening?

Install failed for SQL Server Management Objects


I am trying to install SQL Server Management Objects on a server which has MSXML6.0 and ReportingServices Add-in for Sharepoint, but the installation is failing saying higher version is already exists on the machine.

But when I could not find the SMO assemblies (Microsoft.SqlServer.Smo.dll) on the  server.

What could be the problem? Is SMO collection installed by another component other than SQL Server? Where can i find the SMO assemblies on my server.

Sujanakar Reddy

Getting started with SQL Server Management Objects (SMO)

Getting started with SQL Server Management Objects (SMO)

SQL Server 2008 R2 Management Objects (SMO) standalone installer

Is there a standalone installer for SQL Server 2008 R2 Management Objects (SMO)? I have been unable to find a download location for a R2 package. It was installed on my machines as part of the Express edition, but I need a standalone installer for individual client deployment. Any help would be appreciated. Cheers, Travis

To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects


What could be wrong?

I get this error when a stored procedure is executing sp_OAMethod from SQL Server 2000 SP4.

EXEC @rc = master..sp_OAMethod @Obj, 'Execute'

The linked server is SQL Server 2005 x64.  Already installed is SQLServer2005_BC_x64.msi version April 15, 2006.  Local server is SQL Server 2000 SP4. 

I can execute the sproc successfully in Query Analyzer from my local dev box which has both 2000 and 2005 installed.  Profiler in 2005 indicates success for the whole sproc. 

It fails when I execute in Query Analyzer while connected on SQL 2000 SP4.  Tried this with 3 different machines SQL Server 2000 SP4.  The server do not have any 2005 component installed.

According to Profiler in 2005, the last event that happen is

SQL: BatchCompleted

if (object_id('master.dbo.sp_MSSQLDMO80_version') is not null) exec master.dbo.sp_MSSQLDMO80_version else if
(object_id('master.dbo.sp_MSSQLDMO70_version') is not null) exec master.dbo.sp_MSSQLDMO70_version else select 0

Below is the trapped Query Analyzer message.

Error Source: Microsoft Data Transformation Services (DTS) Package 
Error Description:Package failed because Step 'dts_name_removed' failed. 
Error code: 80040428 \
Error Help File:sqldts80.hlp 
Error Help Context ID:700 

Step Error Source: Microsoft SQL-DMO (O

Word Automation Issue in Windows Server 2008 Hosting



The problem I am posting here is that I was facing nearly 2 weeks around. Any body comes with this stuff please help.

Word Automation in sample ASP.NET(C#) application.

I am using Microsoft.Office.Inetrop.Word Assembly for automation. Here I am reading a XXX.dot template file and fill the contents with dynamic data.

When i am executing my code in localhost:someportnumber the automation is working fine and I could get expected result and when I am hosting in my inetmgr(Windows XP is my OS) it is also working fine.

But the problem is that when I am hosting in my production server(Windows Server 2008 Standard Edition) I am not able to perform automation and results in the following error.

Data: System.Collections.ListDictionaryInternal
Message: Word has encountered a problem.
Source: Microsoft Word

The code gets failed in the following line:

ApplicationClass wordApp = new Microsoft.Office.Interop.Word.ApplicationClass();

Document wordDoc = wordApp.Documents.Add(ref oTemplate, ref oFalse, ref oMissing, ref oMissing); // Error in this line

I cannot able to proceed further. Can anybody please help me in solving this issue?

Thank you.

With Regards,


Agile Development: Extend Team Foundation Server To Enable Continuous Integration


How does Visual Studio 2005 Team System and Team Foundation Server facilitate the process of agile development and continuous integration? Here Ben Waldron explains it all.

Ben Waldron

MSDN Magazine March 2006

Test: Build Quick and Easy UI Test Automation Suites with Visual Studio .NET


The .NET Framework provides a surprising new way to quickly and easily create user interface test automation. By using objects in the System.Reflection and System.Threading namespaces, you can write automated tests in minutes instead of hours. This article walks you through the building of a typical Windows-based application that will be used as the test subject. The author then runs through the creation of a C#-based test tool that simulates clicking the test app's UI controls and checks the application's state. After the tool is built, the author explains in detail how it works so you can modify and extend it for your own use.

James McCaffrey

MSDN Magazine January 2003

Basic Instincts: Objects and Values, Part 2: Memory Management


In the June 2002 installment of Basic Instincts I began a discus-sion of objects and values. This month I'll build on that column, so I will assume you've read the June installment and that you know the fundamental differences between value types and reference types.

Ted Pattison

MSDN Magazine October 2002

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend