title: SqlBulkInsert with a DataTable to a Linked Server
body: <p>I'm working with 2 SQL 2008 Servers on different machines. The server names are <code>source.ex.com</code>, and <code>destination.ex.com</code>.</p>
  
  <p><code>destination.ex.com</code> is linked to <code>source.ex.com</code> and the appropriate permissions are in place for <code>source.ex.com</code> to write to a database called <code>bacon-wrench</code> on  <code>destination.ex.com</code></p>
  
  <p>I've logged into <code>source.ex.com</code> via SMS and tested this query (successfully):</p>
  
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
  (PunchID, BaconID) VALUES (4,6);
  </code></pre>
  
  <p>In a C# .NET 4.0 WebPage I connect to <code>source.ex.com</code> and perform a similar query (successfully):</p>
  
  <pre class="lang-cs prettyprint-override"><code>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();
      }
  }
  </code></pre>
  
  <p>For small sets of insert statements (say 20 or less) doing something like this performs fine:</p>
  
  <pre class="lang-cs prettyprint-override"><code>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();
      }
  }
  </code></pre>
  
  <p>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 <code>SqlBulkCopy</code> class was recommended and it takes as a parameter <code>DataTable</code>, perfect!</p>
  
  <p>So I build a DataTable and attempt to write it to the server (fail):</p>
  
  <pre class="lang-cs prettyprint-override"><code>DataTable dt = new DataTable();
  dt.Columns.Add("PunchID", typeof(int));
  dt.Columns.Add("BaconID", typeof(int));
  for(int i = 0; i &lt; 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);
      }
  }
  </code></pre>
  
  <p>EDIT2: The below message is what I'm attempting to fix:</p>
  
  <p>The web page crashes at <code>bulk.WriteToServer(dt);</code> with an error message <code>Database bacon-wrench does not exist please ensure it is typed correctly.</code> What am I doing wrong? How do I change this to get it to work?</p>
  
  <p>EDIT1:</p>
  
  <p>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.</p>
  
  <pre class="lang-cs prettyprint-override"><code>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();
      }
  }
  </code></pre>