0

I am attempting to export and import a database schema and the data within. I'm using SMO (Server Management Objects) to do so, but while the export of the schema is working just fine, setting ScriptData to true in the scripting options will produce an exception.

Server server = new Server(new ServerConnection(sqlCon));
Database database = server.Databases["SpectroGrass"];
ScriptingOptions options = new ScriptingOptions();
options.ScriptSchema = true;
options.ScriptData = false;
options.ScriptDrops = false;

string scriptData = String.Join("\r\n", database.Tables[tableName].EnumScript(options).ToList());

Will produce a schema and is working.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE TABLE [SpectroGrassAdmin].[Treatment]
(
    [TRM_ID] [int] NOT NULL,
    [TRM_Name] [varchar](150) COLLATE Latin1_General_CI_AS NULL,
    [TRM_Crop] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

But using the ScriptData option ...

options.ScriptSchema = false;
options.ScriptData = true;
options.ScriptDrops = false;

string scriptData = String.Join("\r\n", database.Tables[tableName].EnumScript(options).ToList());

will generate an exception.

Login failed for user 'SpectroGrassAdmin'.

I'm not sure why I'm getting this specific error. I can only assume the problem lies somewhere else, because logging into the server and connecting to the database is working fine if I only want the schema exported.

Am I missing some other options to be able to create a script for the table data?

Improve this question
4
  • could you check the AutoDisconnectMode setting of the connectioncontext? ($server.ConnectionContext , $server.ConnectionContext.AutoDisconnectMode) and maybe change it to 1 (:never disconnect?) What is the value/format of sqlCon? Also, set true for both script schema and data. Commented Jan 10, 2020 at 14:29
  • There is some additional information included in this answer: https://stackoverflow.com/questions/37003017/scriptingoptions-sql-smo-does-not-support-scripting-data Commented Jan 12, 2020 at 22:40
  • @lptr Setting the AutoDisconnectMode did not change anything. The connection string was built with the SqlConnectionStringBuilder class. It contains the data source, initial catalog, user id, password and has integrated security set to false as well as the connection timeout set to 0. Using schema and data still ends with the exception. Using all 3 (schema, data and drop) will generate only the drop statement, without schema and data. Commented Jan 13, 2020 at 6:23
  • @MartinCairney I'm using all references in the link you provided and tried the Scripter instead of ScriptingOptions just in case, but same result. The only difference is that I'm using SQL Sevrer 2017 instead of 2014. Commented Jan 13, 2020 at 6:25

1 Answer 1

Reset to default
0

Try to simplify it as much as possible. The peculiarity of smo is that by default it points/connects to the default sql instance.

Does it happen that you have a default instance on the machine you are running your app and you are trying to smo to another instance? (either secondary on your machine or a remote instance on another machine).

The following was taken from an example of smo using ServerConnection, which enables the connection information to be reused.

//  https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/create-program/connecting-to-an-instance-of-sql-server?view=sql-server-ver15#connecting-to-an-instance-of-sql-server-by-using-sql-server-authentication-in-visual-c

//// compile with:   
// /r:Microsoft.SqlServer.Smo.dll  
// /r:Microsoft.SqlServer.ConnectionInfo.dll  
// /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll   

using System;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SMOConsoleApp
{
    class Program
    {
        static void Main()
        {

            // For remote connection, remote server name / ServerInstance needs to be specified  
            ServerConnection srvConn2 = new ServerConnection("machinename"/* <--default sql instance on machinename*/);  // or (@"machinename\sqlinstance") for named instances
            srvConn2.LoginSecure = false;
            srvConn2.Login = "sql_login_goes_here";
            srvConn2.Password = "password_goes_here";
            Server srv3 = new Server(srvConn2);

            Console.WriteLine("servername:{0} ---- version:{1}", srv3.Name, srv3.Information.Version);   // connection is established  
            Console.WriteLine(srv3.ConnectionContext); //check connection context

            ScriptingOptions scriptoptions = new ScriptingOptions();
            scriptoptions.ScriptSchema = true;
            scriptoptions.ScriptData = true;
            scriptoptions.ScriptDrops = false;

            Database mydb = srv3.Databases["master"];
            /*
                --execute this in ssms, in the master db
                select *
                into dbo.testsysobjects
                from sys.objects
            */
            Table mytable = mydb.Tables["testsysobjects", "dbo"];

            Console.WriteLine("database: {0} ---- table: {1} ---- rowcount: {2}", mytable.Parent.Name, mytable.Name, mytable.RowCount); //check table

            string scriptData = String.Join("\r\n", mytable.EnumScript(scriptoptions).ToList());

            Console.Write(scriptData);

            srvConn2.Disconnect();

        }
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you, that worked, although I do not know why. I do indeed use a different sql instance, but I did provide it in the SqlConnectionString that I gave to the ServerConnection Object. It must not have set it properly or something. Giving the login credentials directly to the ServerConnection Object worked. I'm still confused why whatever went wrong would let me export the schema, but lead to a failed login attempt when trying to export the data.
smo handles the connections unless a serverconnection object is explicitly defined. Even in the code of the answer, Disconnect() is not needed (unless an explicit Connect() was used before).For exporting schema&data smo opens two different connections. The same happens when ScriptDrops = true : 2 connections although only a single drop statement is returned. A guesstimate would be that one connection is for the schema and the other for the data. Note that the connections are opened/closed multiple times (the first time not-pooled and later-on pooled).
Could it be that the first connection (about the schema) uses the SqlConnectionStringBuilder and the second connection is yet another server() instance which is initiated without a connection string. A server() without a defined connection connects to the default machine instance. You could test that in your code by try/catching the ScriptData and print the server.ConnectionContext in the catch, to check what sql instance server() tries to connect to.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.