Using SqlBulkCopy to insert rows to a table having column of type 'xml' in SQL Server 2008 raises exception

c# sqlbulkcopy sql-server

Question

I have simple table in SQL Server with two columns Id (BIGINT), PublishData (XML).

I am interested in using SqlBulkCopy but when I try to so using SqlBulkCopy.WriteToServer(DataTable), it gives me below exception. I am using SqlXml data type in C# when I create datatable and add column for PublishData. I think it mismatches with column type xml in SQL Server. I need to know what is the correct datatype I need to use while creating the datatable for xml column and then use it with SqlBulkCopy.

Any help would be appreciated?

Unhandled Exception: System.InvalidCastException: Specified cast is not valid.
at System.Data.SqlClient.TdsParser.WriteBulkCopyValue(Object value, SqlMetaDa taPriv metadata, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount )
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowSt ate rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

1
0
7/8/2012 8:42:43 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

need to know what is the correct datatype I need to use while creating the datatable for xml column and then use it with SqlBulkCopy

What about tying it out instead of asking here? I mean, seriously, how many data types ARE there?

I would suggest you try a STRING in .NET ;) Kind of obvious, isn't it - you can hardly store XML in a Bool or Integer data type, so the ONLY alternative to SqlXml I can see would be string.

1
7/8/2012 8:51:15 AM

Related Questions

Is it feasible to use SqlBulkCopy without the db datawriter permission in SQL 2008?

The error described, following an attempt at sp_addrolemember(), is related to the current user not having the necessary privileges to alter account and security settings....With regards to running SqlBulkCopy, it requires write privileges, and the db_datawriter role is a convenient way to provide these, but maybe you can consider alternatives. Fo...
security sqlbulkcopy sql-server sql-server-2008
asked by mjv

SQL Server 2008 database: insert relational(normalized) data tables quickly

I would still use ...SqlBulkCopy... to quickly copy your data from the external file into a ...staging table... that has the same (flat) structure as the file (you'll need to create that table ahead of time)...Once it's loaded, you can split up the data across multiple tables using e.g. a stored procedure or something - should be pretty fast since ...
c# linq-to-sql performance sqlbulkcopy sql-server-2008
asked by marc_s

To Insert/Update in Main Table from Staging Table in SQL Server 2008

I would probably do something like this:...make your transformations on that staging table (add additional columns as needed etc.)...do the INSERT/UPDATE into the actual data table using the ...SQL Server 2008 ...MERGE... command... which is ideally suited for just this - update some existing rows, insert some new rows (and possibly delete some old...
join sqlbulkcopy sql-server-2008
asked by marc_s

Using sqlbulkcopy in C# asp.net to write xml data to a SQL server

You need some way to get your XML data into the DataSet before you can call .Tables[0]. Try using the ....ReadXml()... function of the DataSet class to load the data into your dataset:...FROM: ...http://msdn.microsoft.com/en-us/library/fx29c3yd(v=VS.100).aspx...DataSet dataSet = new DataSet(); DataTable dataTable = new DataTable("table1"); dataTabl...
c# sqlbulkcopy sql-server xml
asked by Kiley Naro

When using SqlBulkCopy to insert into a sql variant column, how do I specify the column type?

According to the MSDN page for ...SqlBulkCopy... (under "Remarks"):...SqlBulkCopy will fail when bulk loading a DataTable column of type SqlDateTime into a SQL Server column whose type is one of the date/time types added in SQL Server 2008....So, ...SqlBulkCopy... won't be able to handle ...DateTime2... values. Instead, I'd suggest one of two ...
ado.net datatable sqlbulkcopy sql-server sql-variant
asked by chezy525

SQL Server inserting a single row with a significant number of columns

I am afraid there isn't a good shortcut for inserting this number of columns, but using parameters will likely save some debugging time. By using parameters you do not need to worry about things likes apostrophes in strings and type conversions. Here's a sample:...public static void TryThis() { try { using (SqlConnection con = new SqlConn...
ado.net bulkinsert sqlbulkcopy sql-server
asked by ron tornambe

Insert xml data into SQL server via SqlBulkCopy with powershell (casting error)

I often use string data types for DataColumns while keeping my SQL Server table an XML datatype. This works fine for me....Instead of converting your xml file to xml just use (cat C:\test\4625_forForums.xml -raw) And define your [System.Xml.XmlNode] as [System.String]
.net powershell sqlbulkcopy sql-server xml
asked by Chad Miller

Insert Dictionary elements into SQL table as rows using IDatareader and SqlBulkCopy

As you already have figured out, you need to implement custom ...IDataReader.... As your source is Dictionary - only few relatively simple method/properties such as int FieldCount, bool Read(), object Get(int i) should be implemented....You may find useful examples (...a simple one...) of custom IDataReader implementations by googling ...sqlbulkcop...
c# datareader dictionary sql sqlbulkcopy
asked by kasitan

Timeout Expired using SqlBulkCopy when there are 300Million rows in SQL Server Database

You have 2 properties on your sqlBulkCopy object that can help you...BulkCopyTimeout: This is the value of the timeout, 30 seconds by default. Set this value to 0 to disable the timeout entirely....BatchSize: Instead of inserting 300MM straight into the table, inserts the records at a batched size, maybe 1MM/time to prevent the timeout issue (and e...
sql sqlbulkcopy timeout
asked by Jason W

Retryable SQLBulkCopy for SQL Server 2008 R2

A simple approach is to:...Implement the batches yourself. This results in a minor inefficiency as ...SqlBulkCopy... needs to query meta data for each ...WriteToServer... call. So don't make the batches too small. Experiment....Insert into a temporary table (not a ...#temp... table but a durable one so that you can lose the connection and continue)...
.net c# sqlbulkcopy sql-server sql-server-2008-r2
asked by usr

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

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