[Bug]: BulkCopy to temporary table does not work

[PauloHMattos]

What happened?

While testing babelfish with an old dotnet code base we discovered that performing a bulk copy into an temporary table throws an exception:

System.InvalidOperationException
  HResult=0x80131509
  Message=Cannot access destination table '#TemporaryTable'.
  Source=Microsoft.Data.SqlClient
  StackTrace:
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
   at Inoa.DbInfra.Test.SqlServer.UnitOfWorkTest.Test() in D:\_INOA\Dev\inoa-common\dbinfra\test\SqlServer\UnitOfWorkTest.cs:line 69

  This exception was originally thrown at this call stack:
    [External Code]

Inner Exception 1:
SqlException: procedure tempdb_dbo.sp_tablecollations_100(nvarchar) does not exist

The following code is a minimal repro for the test we performed.

public void Works() // This works as expected
{
    using var connection = new SqlConnection(_env.ConnectionString);
    connection.Open();

    using var createTableCommand = connection.CreateCommand();
    createTableCommand.CommandText = "CREATE TABLE PersistentTable (ColumnA INT NOT NULL)";
    createTableCommand.ExecuteNonQuery();

    using (var bulkCopy = new SqlBulkCopy(connection)) {
        bulkCopy.DestinationTableName = "PersistentTable";

        var dataTable = new DataTable();
        dataTable.Columns.Add("ColumnA", typeof(int));
        for (var i = 0; i < 10; i++)
        {
            dataTable.Rows.Add(i);
        }
        bulkCopy.WriteToServer(dataTable);
    }
}

public void Fails() // Throws InvalidOperationException
{
    using var connection = new SqlConnection(_env.ConnectionString);
    connection.Open();

    using var createTempTableCommand = connection.CreateCommand();
    createTempTableCommand.CommandText = "CREATE TABLE #TemporaryTable (ColumnA INT NOT NULL)";
    createTempTableCommand.ExecuteNonQuery();

    using (var bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "#TemporaryTable";

        var dataTable = new DataTable();
        dataTable.Columns.Add("ColumnA", typeof(int));
        for (var i = 0; i < 10; i++)
        {
            dataTable.Rows.Add(i);
        }
        bulkCopy.WriteToServer(dataTable);
    }
}

This is a know limitation? If so, there are any plans to implement this in Babelfish?

Version

BABEL_4_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Ubuntu (Default)

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.

[Deepesh125]

Hi @PauloHMattos, Thank you for reporting this issue. We are able to repro this issue and have filed internal issue to investigate and fix this issue.

[PauloHMattos]

Hi @Deepesh125, is there any update on this issue? We started migrating a new service that relies heavily in bulk copy operations to temp tables.

Depending on the time frame to fix this issue we can put the migration on hold or try to refactor the code