P1ST0LPETE
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try SQLDMO.Database db = server.Databases("NetCordD B");
Sorry
SQLDMO.Database db = server.Databases(NetCordDB );
SQLDMO.Database db = server.Databases(NetCordDB
ASKER
Got it to work by changing the database line to:
SQLDMO.Database db = new SQLDMO.Database();
db = (SQLDMO.Database)server.Da tabases.It em("NetCor dDB", 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?
SQLDMO.Database db = new SQLDMO.Database();
db = (SQLDMO.Database)server.Da
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
I think the points should be split
ASKER
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.
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.
ASKER
SQLDMO.Database db = server.Databases["NetCordD
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?
Open in new window