SQL Server SMO/DMO - English (United States)

Category: sql server smodmo

Question

KISHOREJAMALPURI on Sun, 24 Jun 2018 13:04:29


Hi All,

My Native C++ application is using both ADODB and DMO for Creating Database in SQL Server. 

As DMO is deprecated, I need to replace code.

Could you please let me know which is having best approach here for migrating to SQL Server 2017.

ADODB or SMO

1. ADODB

As we already doing the same Database Creation, is it good to replace DMO code with ADODB functions for the SQL Server 2017.

2. SMO 

To use SMO, I need to create a C# dll which will be called by Native C++ code to achieve this goal.

I see performance hit for doing this.

My actual actions to SQL Database are here from DMO code

1. Check If specific Database is already created or not.

If not created 

Create the given database and run the scripts to Create tables.

Create Database means, creating Database, DBFile, Logfile, FileGroup, TranasactionLg objects and AddDatabase. 

If created

Check the tables.

Any one please let me know same actions actions can be done with ADODB with Native C++? or I should use SMO?

Thanks in Advance

Regards

Kishore

Replies

KISHOREJAMALPURI on Sun, 24 Jun 2018 13:07:28


Olaf Helper -- Could you please give your suggestions.

Dan Guzman on Sun, 24 Jun 2018 13:16:41


All of these actions can be done by executing T-SQL queries with ADODB. SMO executes T-SQL queries behind the scenes.

If you are not comfortable with the T-SQL DDL for these tasks, you can still use SMO. You don't necessarily need C# since you can have a mixed-mode assembly written in C++. I would not expect performance to be a concern for the tasks you've listed. See https://docs.microsoft.com/en-us/cpp/dotnet/native-and-dotnet-interoperability

KISHOREJAMALPURI on Mon, 25 Jun 2018 10:22:57


Thanks for your reply.

I have started using the ADO DB for my task. Now I am able to create database by initially setting up the Connection, with connection string without user details.

Now after creating the Database, I want to execute as Script against the newly created Database

I have created new Connection, Command pointers. and now with that Command pointers called Execute Method to run the Script file which have table creation statements

The Execute method is giving DB_E_ERRORSINCOMMAND error while calling Execute method with Script file.

Could you please let me know what is the issue here. 

Any sample code which do this will help me.

Dan Guzman on Mon, 25 Jun 2018 10:41:00


DB_E_ERRORSINCOMMAND indicates a syntax error detected by the OLE DB driver. More information about error details should be available via OLE DB error objects. You could also try the run the same script using SSMS to get a more meaningful error message. Post the SQL script you have having problems with (obfuscated if needed) if the error is unclear. 

KISHOREJAMALPURI on Mon, 25 Jun 2018 12:09:50


I am getting error for the below query in the script file. I have 3500 lines of code. for sample I am giving two tables creation

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TABLE1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TABLE1](
 [AID] [uniqueidentifier] NOT NULL,
 [SID] [uniqueidentifier] NOT NULL,
 [SName] [nvarchar](512) NOT NULL,
 [SCID] [uniqueidentifier] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ROLE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ROLE](
 [RoleID] [uniqueidentifier] NOT NULL,
 [AID] [uniqueidentifier] NOT NULL,
 [Role] [nchar](40) NOT NULL,
 [UserRoleID] [int] NOT NULL CONSTRAINT [DF_ROLE_UserRoleID]  DEFAULT ((-1)),
 [MultiTextID] [nvarchar](50) NULL,
 [DefaultRole] [bit] NULL CONSTRAINT [DF_ROLE_DefaultRole]  DEFAULT ((0)),
 CONSTRAINT [PK_ROLE] PRIMARY KEY NONCLUSTERED
(
 [RoleID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

******************

I have removed GO statements, then it is working fine, not sure what is the issue here.

Could you please let me know the root cause also how to handle big script files.

Dan Guzman on Tue, 26 Jun 2018 03:08:53


I have removed GO statements, then it is working fine, not sure what is the issue here.

Could you please let me know the root cause also how to handle big script files.

The issue is that GO is not a T-SQL statement. It is a batch separator recognized by SQL Server tools (including some SMO methods), which execute the preceding batch of statements (without the GO) whenever a GO is encountered in the script.

One approach (without SMO) is to read the script file line-by-line to build a batch string and send the batch after a GO or EOF is encountered. The issue with this technique is if a GO appears in a comment block but that might not be an issue if you control the scripts being run.

KISHOREJAMALPURI on Tue, 26 Jun 2018 04:42:11


Thanks for your reply.

OK.

I have one more issue, With SQL DMO, we have interface ISQLDMOTable and it's function CheckTable(TableName)

If that return non zero value we are marking it as error. 

How can we achieve same functionality using ADO DB.? 

Just check Table is exists in the Database or not.

OR 

DBCC CheckTable(TableName) ? 

but this giving a more detail description.

My functionality is 

Check table is present of not. Is SQLDMOTable.CheckTable(...) function is also doing the same or it is checking any thing more.

Please clarify.

Dan Guzman on Tue, 26 Jun 2018 10:27:09


DBCC CheckTable(TableName) ? 

but this giving a more detail description.

My functionality is 

Check table is present of not. Is SQLDMOTable.CheckTable(...) function is also doing the same or it is checking any thing more.

DMO CheckTable is a wrapper of DBCC CHECKTABLE. This does much more than just check for table existence; it analyzes physical structural integrity of the table and reports errors found. 

To simply check for existence of a table (or any schema-scoped object), you could execute a query of the T-SQL OBJECT_ID function:

SELECT OBJECT_ID(N'dbo.YourTable') AS object_id;
This will return the integer object_id of the object if it exists, otherwise NULL. This query can be parameterized such that you can pass any object name rather than hard-coding the name like this example.

KISHOREJAMALPURI on Mon, 02 Jul 2018 07:40:11


Thanks for your reply.

Could you please let me know the Pros and Cons for

DMO ADO SMO

and limitations of ADO with SMO.

Looking for a clear comparison of DMO, ADO and SMO.