Today’s post will deal with something that I do not usually deal with, and that is SMO (Sql Server Management Objects).

The reason is that I helped a colleague the other day, and I was a bit confused to start with in regard to why the aptly named method TransferData on the Transfer class threw an exception.

See below for the full exception.

 

As usual, I’d like to show this by example.

 

So first create a database with a data table that will be transferred using the Transfer class.

Start SSMS and execute the following:

 

CREATE DATABASE TransferDataDemo

GO

USE TransferDataDemo

GO

CREATE table TestTable(cid INT PRIMARY KEY, cname VARCHAR(5))

INSERT INTO TestTable VALUES (1, 'ABCDE')

SELECT * FROM TestTable -- To verify

 

Then create a new .Net console application, add the necessary references. Microsoft.SqlServer.Smo for example.

The substitute the main method with this:

 

        static void Main(string[] args)

        {

            try

            {

                string srvName = @"<your server>\<your instance>";

                string srcDatabaseName = "TransferDataDemo";

                string dstDatabaseName = "BackedUp_" + srcDatabaseName;

 

                Server srv = new Server(srvName);

                Console.WriteLine("Connected to: {0}, Version: {1}", srv.Name, srv.Version);

 

                // Get source database from server.

                Database srcDb = srv.Databases[srcDatabaseName];

                // Create backup databse

                Database dstDb = new Database(srv, dstDatabaseName);

                dstDb.Create(); // This creates the new database on the 'srv' server.

                Console.WriteLine("Database [{0}] created...", dstDatabaseName);

 

                // Create transfer.

                Transfer t = new Transfer(srcDb);

                t.CopyAllObjects = true;

                t.CopySchema = true;

                t.CopyData = true;

                // Set the destination table for the transfer object.

                t.DestinationDatabase = dstDb.Name;

                // and to the transfer, this should do it. Or will it?

                t.TransferData();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

            }

        }

 

When running this, I expected the destination database to be created, and then that the data from the source database would be transferred.

After all, we have created the transfer instance by providing the source server database. But when running this, what we get is:

 

Connected to: your server\your instance, Version: 9.0.3239

Database [BackedUp_TransferDataDemo] created...

Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server .

---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnectionowningObject)

   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfoserverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)

   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

   at System.Data.SqlClient.SqlConnection.Open()

   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)

   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

   --- End of inner exception stack trace ---

   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

   at Microsoft.SqlServer.Management.Smo.Transfer.Microsoft.SqlServer.Management.Common.ITransferMetadataProvider.SaveMetadata()

   at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.Configure(ITransferMetadataProvider metadataProvider)

   at Microsoft.SqlServer.Management.Smo.Transfer.GetTransferProvider()

   at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()

 

From this exception I get the impression that there is a problem with the server instance or the network, after all, that it is what it says.

 

Well, the solution is simple; you have to specify the server\instance for the destination as well.

Obvious when you know it, but as mentioned, the error message is a bit misleading, it could say something along the lines “No server set for Destination database”.

 

But in short, what happens here is equivalent to doing this:

            

 try

            {

                string cStr = @";Initial Catalog=Master;Integrated Security=true";

                SqlConnection con = new SqlConnection(cStr);

                con.Open();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

                Console.ReadKey();

            }

 

This will give this because there is no server specified:

 

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

 

So, as an example on how to fix this is to always treat the destination server and source server as different entities, even if they are the same.

This will also give better preparation for when you decide to copy the data to another, remote, server.

 

Create a new .Net console application and replace the Main with this:

 

        static void Main(string[] args)

        {

            try

            {

                string srcServerName = @"<your source server>\<instance>";

                string dstServerName = @"<your destination server\<instance>";

 

                string srcDatabaseName = "TransferDataDemo";

                string dstDatabaseName = "BackedUp_" + srcDatabaseName;

 

                Server srcSrv = new Server(srcServerName);

                Console.WriteLine("Connected to Name: {0}, v.: {1} <-- Source Server", srcSrv.Name, srcSrv.Version);

 

                Server dstSrv = new Server(dstServerName);

                Console.WriteLine("Connected to Name: {0}, v.: {1} <-- Destination Server", dstSrv.Name, dstSrv.Version);

 

                // Get source database from the source server.

                Database srcDb = srcSrv.Databases[srcDatabaseName];

                // Create backup databse on the destination server

                Database dstDb = new Database(dstSrv, dstDatabaseName);

                dstDb.Create(); // This creates the new database on the 'destination' server.

                Console.WriteLine("Database [{0}] created on [{1}]...", dstDb.Name, dstSrv.Name);

 

                // Create transfer.

                Transfer t = new Transfer(srcDb);

                t.CopyAllObjects = true;

                t.CopySchema = true;

                t.CopyData = true;

                // Set the destination table for the transfer object.

                t.DestinationDatabase = dstDb.Name;

 

                // Now set up the destination server

                t.DestinationServer = dstSrv.Name;

 

                // and to the transfer, this should do it.

                t.TransferData();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

            }

        }

 

This should create a new database on the destination server and then transfer the data.

 

So to summarize, the exception is not bound to SMO, the exception comes from the fact that there is no server specified, and therefore the provider can’t connect.

But it may be a bit hard to spot immediately that this is the case when using SMO.

 

Some references on SMO /database/server/transfer classes:

SQL Server 2005 Books Online (September 2007) - SQL Server Management Objects (SMO)

   http://msdn.microsoft.com/en-us/library/ms162169(SQL.90).aspx

Microsoft.SqlServer.Management.Smo - Transfer class:

   http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer(SQL.90).aspx

Microsoft.SqlServer.Management.Smo - Server class:

   http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server(SQL.90).aspx

Microsoft.SqlServer.Management.Smo - Database class:

   http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database(SQL.90).aspx