I was inserting bulk data from C# code using sqlbulkcopy.There were 15000 records in temp_upload table.Now somehow the datatable in WriteToServer() method had just one column and 37 rows. After running it I found that the table just had 37 records.Initially it had 152 columns but after this only 32 columns were left. What could be the ...
Hi all, I am working on a project where I need to extract data from a MSSQL database table, and then insert the data into a table of the same structure on an AS400. The SqlBulkCopy would be the ideal candidate for this operation, but fails (understandably) because the AS400 uses an ODBC connection. Any help or suggestions on this woul...
I'm trying to use SqlBulkCopy to insert new rows into my DB table by manually populating a DataTable w/in my application. This works fine for all tables except the table that has a composite primary key made up of 3 columns. Whenever I try to SqlBulkCopy anything into this table, I get the following error: Violation of PRIMARY KEY con...
I have a requirement for a project to move data from SQL Server to Oracle in bulk mode. There is OracleBulkCopy from DataDirect and ODP .net but to use that I have to first convert the data reader from SQL server into a CSV file and then can export that using bulk copy.This is a very inefficient process and I was wondering if there is an...
Is it possible to use SqlBulkcopy with Sql Compact Edition e.g. (*.sdf) files? I know it works with SQL Server 200 Up, but wanted to check CE compatibility. If it doesnt does anyone else know the fastest way of getting a CSV type file into SQL Server CE without using DataSets (puke here)? ...
I’m having an issue using SqlBulkCopy when nullable columns are involved. It sounds like SqlBulkCopy doesn't know how to deal with nullable columns and throws an illegal size error when it encounters a zero length column. The error is "Received an invalid column length from the bcp client..." I’m wondering what the best practice is for ...
Hi, Recently I've been tasked with creating an automated ETL process that pumps the data into tables based on the flat file name by reading a master mapping file. I've decided to go with SqlBulkCopy and everything seemed to be fine. IDataReader interface was implemented to read flat-files, SQL Server's meta-data provided with number of ...
I want to limit my database possible access ways to only using stored procedures. Everything works fine except System.Data.SqlClient.SqlBulkCopy. I'm using it only in one class for massive data import. Is it possible to avoid this problem? I tried to grant a right before calling SqlBulkCopy and remove it after: EXEC [db_mod].[sys].[sp...
I need to grant a db_datawriter before executing SqlBulkCopy and remove it after: try { "EXEC [db_mod].[sys].[sp_addrolemember] N'db_datawriter', N'my_user'" // via SqlCommand bulk.WriteToServer(table); } finally { "EXEC [db_mod].[sys].[sp_droprolemember] N'db_datawriter', N'my_user'" // via another SqlCommand } but I'm getti...
Hi, I'm new to windows services and... you guessed it, I’m a bit stuck. Let me paint the picture – I’m running a timed service that use an OdbcDataReader and SqlBulkCopy to (1) archive the data (2) normalize the data on a SQL box. When I run this code in a windows form proj. it works fine. Then, when I change the DNS’s Data Directory P...
Can anyone help in converting string value in C# to bit equivalent in Sql. I'm trying to bulkcopy the values of a datatable into Sql table. All the values I've in the datatable are in string format. When I try to bulkcopy to SQL table I'm getting an error for bit datatype columns.Can anyone please post the C# code to convert string to...
Hi, I have a datatable with the records.I'm inserting records into Sql table using SqlBulkCopy.It works fine.Next time when get the datatable with same records with few changed values SqlBulkCopy is inserting another set of records without updating the previous details.How can I update the Sql table using SqlBulkCopy ?? Please help. Th...
Is there a way to use SqlBulkCopy without converting the data to a DataTable? I have a list of objects (List) in RAM and I really don't want to use more memory to create the DataTable. Could it be possible to implement IDataReader on a List? Thanks! ...
We are currently trying out the SQLBulkCopy API on the new SQL Azure CTP. While we have been able to consistently migrate tables with about a million rows, we are facing connection errors when working with larger tables. We keep getting (after random row transfers) the following error: A transport-level error has occurred when receivin...
i use SqlBulkCopy to insert data from OleDbDataReader (contains data from xls) to mssql-2005 i have a cloumn on the OleDbDataReader that contains number stored as text (in the xls) when i look into the mssql data i see null in that column all other columns are move ok. link text ...
Hi, we have 2 DataTables in a .NET application having a Client / Parent relationship with millions of rows. This data should be inserted into a SQL Server database via SQL BulkCopy. It is possible that multiple instances of this .NET application run in parallel inserting different data to the same tables. For the automatic generation o...
I have a view which generates a number of columns which I want to bulk load into another table which has identically named columns. This procedure worked fine when I was looping over the SqlDataReader and doing an insert with SqlParameters using a SqlCommand each time. Obviously for many 100000s of rows, this was too slow. I switched t...
Dear guys I insert data from a typed dataset into my MSSQL database by using SqlBuldCopy class: foreach (DataTable dt in ds.Tables) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn)) { bulkCopy.DestinationTableName = "dbo." + dt.TableName + "_neu"; try { bulkCopy.WriteToServer(dt); ...
I've installed DataAccessApplicationBlock.msi and I got the Microsoft.ApplicationBlocks.Data.dll file into my bin folder. I found every other sqlhelper methods except ExecuteBulkCopy. How do I add ExecuteBulkCopy function to the SqlHelper class? ...
I got the following error when executing bulkcopy. System.InvalidOperationException The given value of type String from the data source cannot be converted to type decimal of the specified target column. I use the following code. DataTable empTable = DataTemplate.GetEmployees(); DataRow row; for (int i = 0; i < gv.Rows.Count...