Link to home
Start Free TrialLog in
Avatar of P1ST0LPETE
P1ST0LPETEFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of reb73
reb73
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of P1ST0LPETE

ASKER

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

try SQLDMO.Database db = server.Databases("NetCordDB");
Sorry
SQLDMO.Database db = server.Databases(NetCordDB);
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

I think the points should be split
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.