running sqlbulkcopy in c# and I get an error: WriteToServer: Connection property has not been initialized. it happens at the WriteToServer command. The connection is open. using (SqlBulkCopy s = new SqlBulkCopy(conn)) { foreach (DataTable dt in ds.Tables) { s.DestinationTableName = "tmp_" + dt.TableName; s.NotifyAfter = 5...
Can somebody shed some lights on how SQL Server 2005 deals with may request issued by a client using ADO.NET 2.0. Below is the shortend output of SQL Trace. I can see that connection pooling is working (I believe there is only one connection being pooled). What is not clear to me is why we have so many sp_reset_connection calls i.e a ser...
I hope you will bear with me. I wanted to provide as much information as I can. The main problem is how to create a structure (like a stack) that will be used by multiple threads that will pop a value and use it to process one big flat file and possibly do cycling again and again until the whole file is processed. If a file has 100.000...
I'm using SqlBulkCopy to bulk insert some records from one table into another table. The query is using a SqlDataReader to get the data. The one difference that matters (besides column order which is handled in the mappings) between the tables is that the destination table has a date column into which the current date needs to be added...
I need to count sequential timeout exceptions from SqlBulkCopy. To test this, I use an external app to start a transaction & lock up the target table. Only on the first call does SqlBulkCopy throw a timeout exception when expected. We've tried using an external connection & transaction, as well as using a connection string and internal...
I have an application that make a copy from my database by bulk copy class in c#. Can I rollback the bulk copy action in sql server when occur an exception? ...
This might be long, but I want to explain my example I got this Code: #region [parent table] DataTable dtParent = new DataTable(); DataColumn dc; dc = new DataColumn(); dc.DataType = System.Type.GetType("System.Int32"); dc.ColumnName = "Id"; dc.Unique = true; dc.AutoIncre...
I have a List<MyObject> with a million elements. (It is actually a SubSonic Collection but it is not loaded from the database). I'm currently using SqlBulkCopy as follows: private string FastInsertCollection(string tableName, DataTable tableData) { string sqlConn = ConfigurationManager.ConnectionStrings[SubSonicConfig.DefaultDataP...
What is the best database engine independent way to load a huge amount of data. While I'm using SQL Server I use SqlBulkCopy but want to untie from SQL Server ...
I execute a bcp command in 2 computer. The first computer has windows xp and second one has windows server 2003. when I execute command in first, the bcp run successfully but when I run this on second one, occur this error: Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Code pag...
I have a gridview which will contain some 'n' number of rows.... Now i want to add all rows of the gridview to a datatable which will be used for bulkcopy operation... I have found this http://www.codeproject.com/KB/aspnet/GridView_To_DataTable.aspx But i want all columns of my gridview to be added to the datarow of the datatable I ...
I have created a datatable and trying to insert that datatable through SqlBulkCopy but somehow it doesn't seem to work for me.... I got the error, The given value of type DateTime from the data source cannot be converted to type decimal of the specified target column. My Datasource is, DataTable dt = new DataTable(); dt.Columns...
I am working with VB.NET.. i have a DataTable called "QUESTION", containing 3 fields: QuestionNumber (unique integer key) QuestionText QuestionType In my SQL Server database I created a Table called "QUESTION" with the same fields. QuestionNumber is defined as integer unique key, auto increment Now, when i make a bulk copy to inser...
I have an ADO.NET DataTable with about 100,000 records. In this table there is a column xyID which has no values in it, because the column is an auto-generated IDENTITY in my SQL Server database. I need to retrieve the generated IDs for other processes. I am looking for a way to bulk copy this DataTable into the SQL Server database, an...
My current project consists of 3 standard layers: data, business, and presentation. I would like to use data entities for all my data access needs. Part of the functionality of the app will that it will need to copy all data within a flat file into a database. The file is not so big so I can use SqlBulkCopy. I have found several arti...
I'm facing a dilemma (!). In a first scenario, I implemented a solution that replicates data from one data base to another using SQLBulkCopy synchronously and I had no problem at all. Now, using ThreadPool, I implemented the same in a assynchronously scenario, a thread per table, and all works fine, but past some time (usualy 1 hour b...
I receive a daily XML file that contains thousands of records, each being a business transaction that I need to store in an internal database for use in reporting and billing. I was under the impression that each day's file contained only unique records, but have discovered that my definition of unique is not exactly the same as the prov...
Our team needs to insert a cruel amount of data into our SQL Server 2008 database. We're looking for a good solution. Now we came up with one, but I have doubts with it, simply because it doesn't feel right. So I'm asking here if this seems like a good solution. Extra challange is that it's a peer-to-peer replicated database over 4 serve...
I'm looking for the fastest way to load bulk data via c#. I have this script that does the job but slow. I read testimonies that SqlBulkCopy is the fastest. 1000 records 2.5 seconds. files contain anywhere near 5000 records to 250k What are some of the things that can slow it down? Table Def: CREATE TABLE [dbo].[tempDispositions]( ...
I'm using the MySql connector for .NET to copy data from MySql servers to SQL Server 2008. Has anyone experienced better performance using one of the following, versus the other? DataAdapter and calling Fill to a DataTable in chunks of 500 DataReader.Read to a DataTable in a loop of 500 I am then using SqlBulkCopy to load the 500 Da...