• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2185
  • Last Modified:

Generate SQL Script using SQLDMO in C#

Hi,

I'm currently working on a C# program that needs to generate an SQL script.  I've discovered that the SQLDMO API will allow me to do everything that the SQL Management Studio can do.  The SQL Management Studio can generate scripts by doing the following:

Right click on database object >> Tasks >> Generate Scripts.

In my program I need to create a script file just like the above would produce.
Can anyone help explain how to do this, or point me in the direction of a good tutorial.

Thanks
0
P1ST0LPETE
Asked:
P1ST0LPETE
  • 4
  • 3
2 Solutions
 
reb73Commented:
0
 
tbsgadiCommented:
0
 
P1ST0LPETEAuthor Commented:
After reading through the links you guys posted, I've come up with the following method (see code snippet).  Upon compiling, I'm getting an error on the following line:

SQLDMO.Database db = server.Databases["NetCordDB"];

The error is: "Cannot apply indexing with [ ] to an expression of type 'SQLDMO.Databases'".

In the examples online, that line of code looks like it should work.
What am I doing wrong?
private void btnScript_Click(object sender, EventArgs e)
{
    SQLDMO.SQLServer server = new SQLDMO.SQLServer();
    server.Connect(cboxServerList.Text, tbUsername.Text, tbPassword.Text);
    SQLDMO.Database db = server.Databases["NetCordDB"];
 
    SQLDMO.Transfer trans = new SQLDMO.Transfer();
    trans.CopyAllTables = true;
    trans.CopyAllDefaults = true;
    trans.CopyAllObjects = true;
    trans.CopyAllRules = true;
    trans.CopyAllStoredProcedures = true;
 
    string scriptPath = @"C:\Documents and Settings\User5\Desktop\TestScript.sql";
 
    db.ScriptTransfer(trans, SQLDMO.SQLDMO_XFRSCRIPTMODE_TYPE.SQLDMOXfrFile_SingleFile, scriptPath); 
}

Open in new window

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
tbsgadiCommented:
try SQLDMO.Database db = server.Databases("NetCordDB");
0
 
tbsgadiCommented:
Sorry
SQLDMO.Database db = server.Databases(NetCordDB);
0
 
P1ST0LPETEAuthor Commented:
Got it to work by changing the database line to:

SQLDMO.Database db = new SQLDMO.Database();
db = (SQLDMO.Database)server.Databases.Item("NetCordDB", null);

Final problem: My generated script is not matching the script generated by SQL Server Management Studio.  Code snippet below shows the two scripts.  As you can see, my script contains a lot of extra stuff that I don't want.  What do I need to do to create a matching script?
-------------------------------------
-- SCRIPT GENERATED BY MY PROGRAM: --
-------------------------------------
 
set quoted_identifier  OFF 
GO
 
/****** Object:  Table [dbo].[BootConfig]    Script Date: 04/21/2009 11:04:23 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BootConfig]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BootConfig]
GO
 
/****** Object:  Table [dbo].[BootConfig]    Script Date: 04/21/2009 11:04:23 AM ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BootConfig]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[BootConfig] (
	[NodeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NodeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NodeID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RxChannel] [numeric](18, 0) NULL ,
	[MasterID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NCID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
END
 
GO
 
------------------------------------------
-- SCRIPT GENERATED BY SQL MGMT STUDIO: --
------------------------------------------
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BootConfig]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BootConfig](
	[NodeType] [varchar](50) NULL,
	[NodeName] [varchar](50) NULL,
	[NodeID] [varchar](50) NULL,
	[RxChannel] [numeric](18, 0) NULL,
	[MasterID] [varchar](50) NULL,
	[NCID] [varchar](50) NULL
) ON [PRIMARY]
END

Open in new window

0
 
tbsgadiCommented:
I think the points should be split
0
 
P1ST0LPETEAuthor Commented:
Ok, sorry for not replying to the answer.  The root of the problem, is the project I was working on that this question was associated with, has been put on the back burner while I work on something else. (Ever hear of projects that get started, but then never finished? :-)  )

Anyway, if you re-read my original post, you'll see that the root of my question was for direction on how to recreate the script that SQL Management Studio creates.  Through the experts posts, I was able to figure out (to a degree) how to go about doing it (i.e. using SQL-DMO or SQL-SMO).  However, as you read my last post, you'll see that I was unsuccessful in actually matching the output script and I was asking for further help to which I received no response.

As far as points are concerned, it really doesn't bother me either way.  If the points are split between the two experts - fine; if the points are refuned - that's fine too.  The experts did point me in the right direction with their links (which I appriciate as I had no previous SQLDMO/SMO experience), but failed to continue to offer help/advice until the problem was solved.

The argument could be made that:

 "I need to create a script file just like the above would produce"

from my original post doesn't mean:

"I need a script exactly like SQL Management Studio would produce"

however, that is what I was gunning for and received no help/solution for - as when I made my final post stating this, the experts aparently abandoned the question.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now