cmd.prompt cmd.prompt - 5 months ago 25
SQL Question

SqlBulkInsert with a DataTable to a Linked Server

I'm working with 2 SQL 2008 Servers on different machines. The server names are

source.ex.com
, and
destination.ex.com
.

destination.ex.com
is linked to
source.ex.com
and the appropriate permissions are in place for
source.ex.com
to write to a database called
bacon-wrench
on
destination.ex.com


I've logged into
source.ex.com
via SMS and tested this query (successfully):

INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
(PunchID, BaconID) VALUES (4,6);


In a C# .NET 4.0 WebPage I connect to
source.ex.com
and perform a similar query (successfully):

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
c.Open();
String sql = @"
INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
(PunchID, BaconID) VALUES (34,56);";
using(SqlCommand cmd = new SqlCommand(sql, c))
{
cmd.ExecuteNonQuery();
}
}


For small sets of insert statements (say 20 or less) doing something like this performs fine:

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
c.Open();
String sql = @"
INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
(PunchID, BaconID) VALUES (34,56);
INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
(PunchID, BaconID) VALUES (22,11);
INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
(PunchID, BaconID) VALUES (33,55);
INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
(PunchID, BaconID) VALUES (1,2);";
using(SqlCommand cmd = new SqlCommand(sql, c))
{
cmd.ExecuteNonQuery();
}
}


I'm trying to do something like this with around 20000 records. The above method takes 11 minutes to complete -- which I assume is the server sreaming at me to make it some kind of bulk operation. From other StackOverflow threads the
SqlBulkCopy
class was recommended and it takes as a parameter
DataTable
, perfect!

So I build a DataTable and attempt to write it to the server (fail):

DataTable dt = new DataTable();
dt.Columns.Add("PunchID", typeof(int));
dt.Columns.Add("BaconID", typeof(int));
for(int i = 0; i < 20000; i++)
{
//I realize this would make 20000 duplicate
//rows but its not important
dt.Rows.Add(new object[] {
11, 33
});
}

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
c.Open();
using(SqlBulkCopy bulk = new SqlBulkCopy(c))
{
bulk.DestinationTableName = "[destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]";
bulk.ColumnMappings.Add("PunchID", "PunchID");
bulk.ColumnMappings.Add("BaconID", "BaconID");
bulk.WriteToServer(dt);
}
}


EDIT2: The below message is what I'm attempting to fix:

The web page crashes at
bulk.WriteToServer(dt);
with an error message
Database bacon-wrench does not exist please ensure it is typed correctly.
What am I doing wrong? How do I change this to get it to work?

EDIT1:

I was able to speed up the query significantly using the below syntax. But it is still very slow for such a small record set.

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
c.Open();
String sql = @"
INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
(PunchID, BaconID) VALUES
(34,56),
(22,11),
(33,55),
(1,2);";
using(SqlCommand cmd = new SqlCommand(sql, c))
{
cmd.ExecuteNonQuery();
}
}

Answer

After trying a number of things including linked server settings, collations, synonyms, etc., I eventually got to this error message:

Inserting into remote tables or views is not allowed by using the BCP utility or by using BULK INSERT.

Perhaps you can bulk insert to a staging table on your local server (your code works fine for this) and then insert from that staging table to your linked server from there, followed by a local delete of the staging table. You'll have to test for performance.

Comments