SqlBulkCopy truncating string in VARCHAR(MAX) column

Question

I have a simple table on SQL Server 2016:

 CREATE TABLE AgentDownload 
(
  Download VARCHAR(max)
)

I'm using the below code to populate this table using the SqlBulkCopy class in C#.

    var agentDataTable = new DataTable();
    agentDataTable.Clear();
    agentDataTable.Columns.Add("Download");

    var agentDownloadRow = agentDataTable.NewRow();
    
    var veryLongString = "aaa..." // 200,000 a's repeated
    agentDownloadRow["Download"] = veryLongString;

    agentDataTable.Rows.Add(agentDownloadRow);

    using (var connection = new SqlConnection("Data Source=Server;Initial Catalog=Database;Integrated Security=True;"))
    {
        connection.Open();
        var transaction = connection.BeginTransaction();
        
        using (var sbCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction))
        {
            sbCopy.BulkCopyTimeout = 0;
            sbCopy.BatchSize = 10000;
            sbCopy.DestinationTableName = "AgentDownload";
            sbCopy.WriteToServer(agentDataTable);
        }                    
        transaction.Commit();
    }

On the database when I retrieve this entry, the cell value is truncated at 65,535 characters. This is a limit of some sort but I'm not sure where this is coming from or if there is any way around this? The column can contain far more characters and a string type in C# can also contain far more characters. Is there any to do this operation?

I thought this might be a limitation of SqlBulkCopy class but using alternative code such as below also produces the same result:

    using (var connection = new SqlConnection("Data Source=Server;Initial Catalog=Database;Integrated Security=True;"))
    {
        connection.Open();
        var transaction = connection.BeginTransaction();
        
        var cmd = connection.CreateCommand();
        cmd.Transaction = transaction;
        cmd.CommandText = "insert into AgentDownload (Download) values (CAST('' AS VARCHAR(MAX)) + @testVariable)";

        var parameter = new SqlParameter
        {
            ParameterName = "testVariable",
            DbType = DbType.String,
            Size = -1,
            Value = veryLongString
        };
        cmd.Parameters.Add(parameter);
        cmd.ExecuteNonQuery();
        transaction.Commit();
    }

Answer 1

It seems you are using SSMS to view the bulk inserted data. SSMS truncates large values by default to 65535 characters.

To see the entire value in the results grid for the current query window, change the Max Characters Retrieved value from the SSMS menu under Query-->Query Options-->Results-->Grid.

One can also specify the value for all new query windows under Tools-->Options-->Query Results-->SQL Server-->Results to Grid. However, consider the implications on client memory requirements with many rows containing large values. I suggest one change the global option judiciously.