CODEWITHSUNDEEP
c#sql-server-express
MyDaftQuestions
MyDaftQuestions

Reputation: 4691

bulkCopy.WriteToServer does not copy

I'm trying to use bulkCopy.WriteToServer() to copy data from my remote SQL Server Express to my local SQL Server Express, but no data is ever written to my local SQL Server Express database.

The code jumps over the WriteToServer() method instantly... I have no idea if it's failing on the inside and not showing an error message though

I have read How to duplicate a SQL Server 2000 table programatically using .NET 2.0? and I am using very similar code. Although I am using SQL Server 2008 Express on remote and a local to SQL Server 2014 Express:

using (SqlConnection remoteConnection = new SqlConnection(remoteConnectionString))
{
    var query = "SELECT * FROM information_schema.tables WHERE table_type = 'base table'";
    SqlCommand commandGetTables = new SqlCommand(query, remoteConnection);

    try
    {
        remoteConnection.Open();
        SqlDataReader results = commandGetTables.ExecuteReader();

        while (results.Read())
        {
            tables.Add(results.GetString(2));
        }

        results.Close();
    }
    catch (Exception ex)
    {
        //stuff
    }
    finally
    {
        remoteConnection.Close();
    }

    remoteConnection.Open();

    foreach (var table in tables)
    {                    
        // Get data from the source table as a SqlDataReader.
        var commandSourceData = new SqlCommand("SELECT * FROM " + table + ";", remoteConnection);
        var reader = commandSourceData.ExecuteReader();

        using (SqlConnection destinationConnection = new SqlConnection(destinationConnectionString))
        {
            destinationConnection.Open();

            using (var bulkCopy = new SqlBulkCopy(destinationConnection))
            {
                bulkCopy.DestinationTableName = table;

                try
                {
                    // Write from the source to the destination.
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    //stuff
                }
                finally
                {
                    //stuff removed for this post
                }
            }
        }
    }

    remoteConnection.Close();
}
return true;

I know this could be subject to SQL injection etc, but this app is only used by me and not the issue here.

What am I doing wrong?

Edit

I checked the value of reader (var reader = commandSourceData.ExecuteReader();) and it has entries as I would expect, meaning he reading from the remote is fine.

Upvotes: 4

Views: 2875

Answers (1)

ArchAngel
ArchAngel

Reputation: 644

bulkCopy.DestinationTableName = table;
bulkCopy.WriteToServer(reader);

these lines are wrong it's supposed to look like this..

bulkCopy.DestinationTableName = "dbo." + DataTable.TableName; 
bulkCopy.WriteToServer(DataTable);

Upvotes: 1

Related Questions