title: SqlBulkCopy to temporary table in SqlServer 2008
body: <p>i need to move a large amount of data to a sql server 2008 database. I get data from a source server and write using SqlBulkCopy to destination server. this data have to be parsed from a stored procedure and then deleted. I'd like to create a temporary data but, if i create the temp data on the client using SqlCommand the SqlBulkCopy can access the table and works fine, if i execute the same script on the server with a Stored Procedure the SqlBulkCopy.WriteToServer returns the InvalidOperationException "Cannot access destination table '#Tax'"</p>
  
  <p>this is the code perfectly working:</p>
  
  <pre><code>SqlDataReader oSqlDataReader -&gt; read form server source
  SqlConnection oSqlConnection = new SqlConnection(_ConnectionTarget)
  SqlCommand oSqlCommand = new SqlCommand("Create Table #Tax (Id int)", oSqlConnection);
  oSqlCommand.CommandType = CommandType.Text;
  oSqlCommand.CommandTimeout = 0;
  oSqlCommand.ExecuteNonQuery();
  SqlBulkCopy oSqlBulkCopy = new SqlBulkCopy(oSqlConnection)
  oSqlBulkCopy.DestinationTableName = "#Tax";
  oSqlBulkCopy.WriteToServer(oSqlDataReader);
  </code></pre>
  
  <p>this is the code throwing the InvalidOperationException exception:</p>
  
  <pre><code>SqlDataReader oSqlDataReader -&gt; read form server a
  SqlConnection oSqlConnection = new SqlConnection(_ConnectionTarget)
  SqlCommand oSqlCommand = new SqlCommand("SP_CreateTax", oSqlConnection);
  oSqlCommand.CommandType = CommandType.StoredProcedure;
  oSqlCommand.CommandTimeout = 0;
  oSqlCommand.ExecuteNonQuery();
  SqlBulkCopy oSqlBulkCopy = new SqlBulkCopy(oSqlConnection)
  oSqlBulkCopy.DestinationTableName = "#Tax";
  oSqlBulkCopy.WriteToServer(oSqlDataReader);
  </code></pre>
  
  <p>SP_CreateTax:</p>
  
  <pre><code>Create Procedure SP_CreateTax 
  AS
  Begin
      Create Table #Tax (Id int)    
  End
  </code></pre>