SqlBulkCopy 无法尝试复制 XML 列中包含大量内容的行

Question

我正在尝试将一条记录从一个 SQL Server 表复制到另一个。两个表具有相同的结构，其中一列是 xml 类型。源表的行中有大量 xml 内容，大约 2.5Mb。

我把xml栏的内容保存到一个文件里，看附件的map.zip或者从下面下载 https://docs.google.com/leaf?id=0Bz4PXXEQL5TpM2U5MWJhM2MtMTI0Yi00ODg0LTk4OWItMzJiNjVjMDIzNjc2&hl=en&authkey=CLT5i8oP

我的代码的简化版本:

string query = "select * from MyTableSource where id = 1";

using (SqlConnection targetConnection = new SqlConnection(connectionStringTarget))
{
targetConnection.Open();

using (SqlConnection sourceConnection = new SqlConnection(connectionStringSource))
{
    sourceConnection.Open();

    using (SqlCommand command = new SqlCommand(query, sourceConnection))
    {
    using (IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult))
    {
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(targetConnection))
        {
        bulkCopy.DestinationTableName = "MyTableTarget";
        bulkCopy.WriteToServer(reader);
        }
    }
    }
} 
}

发生在 bulkCopy.WriteToServer 上的异常:

System.Data.SqlClient.SqlException was unhandled
Message=XML parsing: Document parsing required too much memory
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=6303
Procedure=""
Server=myserver
State=1
StackTrace:
     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
     at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
     at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
     at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
     at SyncTest.Form1.buttonCopyXml_Click(Object sender, EventArgs e) in C:\..\Form1.cs:line 2251
     at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
     at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
     at System.Windows.Forms.Control.WndProc(Message& m)
     at System.Windows.Forms.ButtonBase.WndProc(Message& m)
     at System.Windows.Forms.Button.WndProc(Message& m)
     at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
     at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
     at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
     at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
     at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
     at SyncTest.Program.Main() in C:\..\Program.cs:line 18
     at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
     at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
     at System.Threading.ThreadHelper.ThreadStart()
InnerException: 

它看起来像一个 SqlBulkCopy 错误。我想知道是否有人可以重现并确认这一点。

更新:提交给微软，

_https://connect.microsoft.com/VisualStudio/feedback/details/614046/sqlbulkcopy-fails-trying-to-copy-a-row-with-large-content-in-an-xml-column

他们确认这是他们的错误:

From debugging thus far, looks like an issue with server side processing of XML in the bulk copy path. One of the attributes in the XML file is really large and this is causing SQL Server to fail when processing the XML because of an allocation size limit.

Answer 1

更改您的选择查询以将 xml 列检索为 [n]varchar(max) 列类型，以避免 xml 处理开销，也可能避免错误。