Azure - SqlBulkCopy throwing a timeout expired exception

azure c# sqlbulkcopy sqlcommand sql-server

Question

I'm using a v12 Azure SQL database on a virtual machine. I have separate instances of two databases, one for staging and one for production. With the press of a button, I want to take the data out of staging and enter it into production. This code "sometimes" succeeds, which means it will happen at random. Otherwise, I get the following error:

BULK COPY Commit Exception Type: {0}System.Data.SqlClient.SqlException BULK COPY Message: {0}Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=1; handshake=17; [Login] initialization=0; authentication=0; [Post-Login] complete=0;

The code I'm using to do this job is shown below; maybe there is a bug that I'm not aware of. I can see that the SELECT and DELETE queries function well by dumping the StringBuilder out, but when I try to replicate the data using SqlBulkCopy, an error is raised. I would be very grateful for any assistance. I've previously read through a lot of the MSDN documentation without success, trying to re-configure my firewall's ports and add longer CommandTimeouts and BulkCopyTimeouts. I've had no success.

I've utilized https://social.msdn.microsoft.com/Forums/en-US/1467d64f-69ae-4c1f-91a2-349fc5d514ae/sqlbulkcopy-fails-with-timeout-expired-error?forum=adodotnetdataproviders resources.

https://azure.microsoft.com/nb-no/documentation/articles/sql-database-develop-direct-route-ports-adonet-v12/

SqlBulkCopy's timeout has elapsed.

public static object SyncData()
{
    StringBuilder sb = new StringBuilder();
    sb.AppendLine("Internal Connection...");
    string internalConnectionString = GetConnectionString("ConnectionString");
    using (SqlConnection internalConnection = new SqlConnection(internalConnectionString))
    {
        internalConnection.Open();              
        SqlCommand selectCommand = internalConnection.CreateCommand();
        selectCommand.CommandTimeout = 180;
        try
        {
            selectCommand.CommandText = "SELECT * FROM dbo.test";
            SqlDataReader reader = selectCommand.ExecuteReader();

            sb.AppendLine("External Connection...");
            string externalConnectionString = GetConnectionString("ExternalConnectionString");
            using (SqlConnection externalConnection = new SqlConnection(externalConnectionString))
            {
                externalConnection.Open();              
                SqlCommand CRUDCommand = externalConnection.CreateCommand();
                CRUDCommand.CommandTimeout = 180;
                SqlTransaction transaction = externalConnection.BeginTransaction("test");
                CRUDCommand.Connection = externalConnection;
                CRUDCommand.Transaction = transaction;
                try
                {
                    CRUDCommand.CommandText = "DELETE FROM dbo.test";
                    sb.AppendLine("DELETE: Number of rows affected = " + CRUDCommand.ExecuteNonQuery());
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(externalConnection, SqlBulkCopyOptions.KeepIdentity, transaction))
                    {
                        try
                        {
                            bulkCopy.DestinationTableName = "dbo.test";
                            bulkCopy.BatchSize = 100;
                            bulkCopy.BulkCopyTimeout = 180;
                            bulkCopy.WriteToServer(reader);

                            sb.AppendLine("Table data copied successfully");

                            transaction.Commit();
                            sb.AppendLine("Transaction committed.");
                        }
                        catch (Exception ex)
                        {
                            sb.AppendLine("BULK COPY Commit Exception Type: {0}" + ex.GetType());
                            sb.AppendLine("  BULK COPY Message: {0}" + ex.Message);
                            try
                            {
                                transaction.Rollback();
                            }
                            catch (Exception ex2)
                            {
                                sb.AppendLine("Rollback Exception Type: {0}" + ex2.GetType());
                                sb.AppendLine("  Message: {0}" + ex2.Message);
                            }
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                }
                catch (Exception ex)
                {
                    sb.AppendLine("Commit Exception Type: {0}" + ex.GetType());
                    sb.AppendLine("  Message: {0}" + ex.Message);

                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {
                        sb.AppendLine("Rollback Exception Type: {0}" + ex2.GetType());
                        sb.AppendLine("  Message: {0}" + ex2.Message);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            sb.AppendLine("Commit Exception Type: {0}" + ex.GetType());
            sb.AppendLine("  Message: {0}" + ex.Message);
        }
    }
    return sb.ToString();
}
1
2
5/23/2017 11:48:33 AM

Accepted Answer

You supply the connection string while constructing your SqlBulkCopy object.externalConnectionString and establishing a new link. As a result of both connections attempting to alter the same table, there can be a deadlock situation.

Have you tried transferring your current connection?externalConnection rather than the connection string, to the SqlBulkCopy constructor?

1
10/7/2016 7:23:39 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow