SqlBulkCopy.WriteToServerAsync(reader) fails to write after upgrading Microsoft.Data.SqlClient to 7.0.0

[anilkmrn7]

Describe the bug

After upgrading the Microsoft.Data.SqlClient to 7.0.0, SqlBulkCopy.WriteToServerAsync(reader) fails to write, however the same code works fine without any changes in 6.1.4 version.

Cannot access destination table 'dbo.TestTable'.. A variable that has been assigned in a SELECT statement cannot be included in a expression or assignment when used in conjunction with a from clause

To reproduce

Below code tries to read from the table and writes to the similar table with the same schema.

CREATE TABLE [dbo].[TestTable]
(
Column1 varchar(255) NULL,
Column2 varchar(255) NOT NULL,
PRIMARY KEY NONCLUSTERED (TableName) NOT ENFORCED
) WITH (CLUSTERED COLUMNSTORE INDEX)
GO

var table = "dbo.TestTable";
var query = "select top 1 [Column1], [Column2] from [dbo].[TestTable]";

using (var command = new SqlCommand())
{
 var sqlConnectionString = _connectionStringProvider.GetConnectionString();
 var sourceSqlConnection = new SqlConnection(sqlConnectionString);
 await sourceSqlConnection.OpenAsync();

 command.Connection = sourceSqlConnection;
 command.CommandTimeout = 3600;
 command.CommandText = query;

 using (var reader = command.ExecuteReader())
 {
	 using var targetSqlConnection = new SqlConnection(sqlConnectionString);
	 targetSqlConnection.Open();

	 using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(targetSqlConnection))
	 {
		 sqlBulkCopy.DestinationTableName = table;

		 await sqlBulkCopy.WriteToServerAsync(reader);
	 }
 }
}

Expected behavior

sqlBulkCopy.WriteToServerAsync(reader) should write the rows read from the source table or the query.

Further technical details

Microsoft.Data.SqlClient version: 7.0.0
.NET target: .Net 10
SQL Server version: Microsoft Azure SQL Data Warehouse - 10.0.31510.0

[edwardneal]

It looks like this is the result of #3590, apologies. I shifted the metadata retrieval from running a static SELECT * FROM [TargetTable] to building up a query using a column list using:

SELECT @Column_Names = COALESCE(@Column_Names + ', ', '') + QUOTENAME([name])
FROM [sys].[all_columns]
WHERE [object_id] = @Object_ID
    AND COALESCE([graph_type], 0) NOT IN (1, 3, 4, 6, 7)
ORDER BY [column_id] ASC

STRING_AGG is available in Azure Synapse and SQL 2017+ and I think using this in the query would fix it. I'll test that shortly.

Edit: I've just reproduced this when connecting to a dedicated SQL pool in Azure Synapse Analytics.