Geek Answers Handbook

ScriptingOptions sql smo does not support script data

I am creating a sql script database using C # code.

The following code works fine for create table , but when I try to use scriptOptions.ScriptData = true; he throws the following exception.

An unhandled exception of type "Microsoft.SqlServer.Management.Smo.FailedOperationException" occurred in Microsoft.SqlServer.Smo.dll

Additional Information: This method does not support script data.

the code

  public static string ScriptDatabase(string dbConnectionString, string databaseName) { SqlConnection conn = new SqlConnection(dbConnectionString); ServerConnection serverConn = new ServerConnection(conn); var server = new Server(serverConn); var database = server.Databases[databaseName]; var scripter = new Scripter(server); // I tried this code also // scripter.Options.ScriptData = true; ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.ScriptDrops = false; scriptOptions.ScriptData = true; scriptOptions.ScriptSchema = true; scriptOptions.IncludeIfNotExists = true; string scrs = ""; string tbScr = ""; foreach (Table myTable in database.Tables) { /* Generating IF EXISTS and DROP command for tables */ StringCollection tableScripts = myTable.Script(scriptOptions); foreach (string script in tableScripts) scrs += script + "\n\n"; /* Generating CREATE TABLE command */ tableScripts = myTable.Script(); foreach (string script in tableScripts) tbScr += script + "\n\n"; } return (scrs + "\n\n" + tbScr); }
+8
c # sql sql-server sql-server-2008
source share
1 answer

The following works on my machine (Win 7.0, .NET 4.0, VS 2010) I created a simple application for Win forms with the OK button and called the function below.
Note. I first added if (myTable.IsSystemObject == true) continues; To ensure that system views are not scripted I have not tested if this is really necessary:

 public static string ScriptDatabase( string dbConnectionString, string databaseName ) { SqlConnection conn = new SqlConnection( dbConnectionString ); ServerConnection serverConn = new ServerConnection( conn ); var server = new Server( serverConn ); var database = server.Databases[ databaseName ]; var scripter = new Scripter( server ); scripter.Options.IncludeIfNotExists = true; scripter.Options.ScriptSchema = true; scripter.Options.ScriptData = true; string scrs = ""; string tbScr = ""; //no longer needed foreach( Table myTable in database.Tables ) { if( myTable.IsSystemObject == true ) continue; foreach( string s in scripter.EnumScript( new Urn[] { myTable.Urn } ) ) scrs += s + "\n\n"; ; } return ( scrs + "\n\n" + tbScr ); }

Output:

 SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tBlah]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tBlah]( [ID] [int] IDENTITY(1,1) NOT NULL, [tID] [varchar](20) COLLATE Latin1_General_CI_AS NULL, [Value] [varchar](20) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] END SET IDENTITY_INSERT [dbo].[tBlah] ON INSERT [dbo].[tBlah] ([ID], [tID], [Value]) VALUES (1, N'2', N'1234') INSERT [dbo].[tBlah] ([ID], [tID], [Value]) VALUES (2, N'2', N'345.6')

MSDN Links:

+10
source share

More articles:


All Articles