SqlBulkCopy

dbembic · Post by **dbembic** » Mon 05 Sep 2011 11:58

I have problem with SqlBulkCopy class. Here is code sample:

public static void ExportToTableDevart(Devart.Data.SqlServer.SqlConnection connection, string tableName, DataTable dataTable)
{
  // create table
  Devart.Data.SqlServer.SqlBulkCopy bcp = new Devart.Data.SqlServer.SqlBulkCopy(connection);
  bcp.DestinationTableName = tableName;
  bcp.WriteToServer(dataTable);
}

public static void ExportToTable(System.Data.SqlClient.SqlConnection connection, string tableName, DataTable dataTable)
{
  // create table
  System.Data.SqlClient.SqlBulkCopy bcp = new Devart.Data.SqlServer.SqlBulkCopy(connection);
  bcp.DestinationTableName = tableName;
  bcp.WriteToServer(dataTable);
}

If I use Devart component SqlBulkCopy opens new connection (different SPID in Sql Server Profiler) and executes "INSERT BULK", while System.Data.SqlClient.SqlBulkCopy uses the same connection. It is a problem because I cannot do SqlBulkCopy into local temporary table and use that table somwhere else in application.
There is a second problem. If I want this procedure to work Devart.Data.SqlServer.SqlConnection ConnectionString must have: "Integrated Security=True" while System.Data.SqlClient.SqlConnection does not require that flag.
Is there some solution of my problem?

Components:
Devart.Data.SqlServer version 2.40.307.0

Shalex · Post by **Shalex** » Fri 09 Sep 2011 13:55

dbembic wrote:If I use Devart component SqlBulkCopy opens new connection (different SPID in Sql Server Profiler) and executes "INSERT BULK", while System.Data.SqlClient.SqlBulkCopy uses the same connection.

Thank you for your report. We have reproduced this behaviour. We will investigate it and notify you about the results.

dbembic wrote:If I want this procedure to work Devart.Data.SqlServer.SqlConnection ConnectionString must have: "Integrated Security=True" while System.Data.SqlClient.SqlConnection does not require that flag.

We can use Devart.Data.SqlServer.SqlBulkCopy successfully in our environment when "Integrated Security=false;" in the connection string with dotConnect for SQL Server v 2.40.347. Please upgrade to this latest version. If this doesn't help, specify:
1) the exact text of the exception and your call stack;
2) do you obtain this exception on conn.Open() or on bcp.WriteToServer()?
3) the version of your SQL Server.

Shalex · Post by **Shalex** » Thu 22 Sep 2011 15:53

The bug with opening additional connection by SqlBulkCopy is fixed. We will post here when the corresponding build of dotConnect for SQL Server is available for download.

Shalex · Post by **Shalex** » Fri 30 Sep 2011 13:08

New build of dotConnect for SQL Server 2.40.361 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/sqlser ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=22162 .