SqlBulkCopy and protection from SQL injection

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...

OLEDB IRowsetFastLoad vs. Ado.Net SqlBulkCopy for SQL Bulk Data Insertion

SqlBulkCopy... is the managed equivalent of ...IRowsetFastLoad..., they should perform similarly. In the client, as a general rule, OleDB is faster than ADO.Net due to the availability of bindings, which allow for a faster transfer of data in and out the API (less memcopy required because the buffers are known ahead, fixed and pre-allocated). ADO.N...

Need recommendations on pushing the envelope with SqlBulkCopy on SQL Server

For recommendations on tuning SQL Server for bulk loads, see the ...Data Loading and Performance Guide... paper from MS, and also ...Guidelines for Optimising Bulk Import... from books online. Although they focus on bulk loading from SQL Server, most of the advice applies to bulk loading using the client API. This papers apply to SQL 2008 - you don...

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]

Getting an exception writing a boolean to sql using a datatable and SqlBulkCopy

Encountered the same issue: setting bool column to true/false was ignored by bulk copy, all values were set to null. The previous answer helped to resolve it, but I had to add mappings for all columns to make it work:...foreach (DataColumn column in table.Columns) { sqlBulk.ColumnMappings.Add(column.ColumnName, column.ColumnName); } sqlBulk.Wri...

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...

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...

How to use SqlBulkCopy with transaction on ReliableSqlConnection in sql azure

How much data are you passing in Bulk? You might be able to use a Table-Valued-Parameter approach instead of Bulk Copy, and that is simple to include in a transaction. The table you assemble on the client is sent to SQL as a parameter very quickly, and inserts are performed with BULK optimizations on the server. This is appropriate for ~O(1000...

SSIS - OleDB connection not updating data, inserted thru SQLBulkCopy with SQL Connection

My bad....Instead of passing data type as long (int in SQL), i was passing it as Varchar....I was looking from last few hours and as soon as i post question here, it strikes me to check the data type....Hope it will help somebody.

SQL Trigger Update to Uppercase doesn't work using SQLBulkCopy Insertion

When you are creating your ...SqlBulkCopy... object add ...SqlBulkCopyOptions.FireTriggers... like so:...SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.FireTriggers); ...FireTriggers... When specified, cause the server to fire the insert triggers for the rows being inserted into the database. - ...SqlBulkCopyOptions...