我正在尝试将一个 SQL Server 表中的记录复制到另一个表中。两个表具有相同的结构，其中一个列的类型为 xml。
源表中有一行大约 2.5Mb 的大型 xml 内容。
我将 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的一个bug。 我想知道是否有其他人可以重现并确认。
更新: 已向Microsoft提交报告。
_https://connect.microsoft.com/VisualStudio/feedback/details/614046/sqlbulkcopy-fails-trying-to-copy-a-row-with-large-content-in-an-xml-column
他们确认这是他们的错误：
从迄今为止的调试来看，似乎是批量复制路径中服务器端处理XML的问题。 XML文件中的一个属性非常大，这会导致SQL Server在处理XML时因为分配大小限制而失败。