Attempting to duplicate a row with significant content in an XML column using SqlBulkCopy fails.

sql sqlbulkcopy sql-server xml


I am trying to copy a record from one SQL Server table to another. Both tables have the same structure, one of the columns is of type xml. Source table has a large xml content in of the rows, about 2.5Mb.

I saved the content of the xml column into a file, see attached or download it from

Simplified version of my code:

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

using (SqlConnection targetConnection = new SqlConnection(connectionStringTarget))

using (SqlConnection sourceConnection = new SqlConnection(connectionStringSource))

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

Exception that occurs on bulkCopy.WriteToServer:

System.Data.SqlClient.SqlException was unhandled
Message=XML parsing: Document parsing required too much memory
Source=.Net SqlClient Data Provider
     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()

It looks like a SqlBulkCopy bug. I wonder if somebody else can reproduce and confirm that.

Update: Filed it to Microsoft,


they confirmed this is their bug:

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.

11/3/2010 7:30:54 PM

Popular Answer

Change your select query to retrieve the xml column as a [n]varchar(max) column type, to avoid the xml processing overhead, and probably the bug as well.

1/28/2011 11:34:24 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow