Neo Neo - 7 months ago 31
SQL Question

ScriptingOptions sql smo does not support scripting data

I'm generating sql database script using c# code.

following code works fine for

create table
but when I try to use
scriptOptions.ScriptData = true;
it is throwing 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 scripting data.


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);
}

Answer

The following works on my machine (Win 7.0, .NET 4.0, VS 2010) I created a simple Win forms app with OK button and called below function.
Note: I have initially added if( myTable.IsSystemObject == true ) continue; to ensure that system views are not being scripted; I have not tested if this is actually needed:

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 = "";
        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')