6

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'"

this is the code perfectly working:

SqlDataReader oSqlDataReader -> 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);

this is the code throwing the InvalidOperationException exception:

SqlDataReader oSqlDataReader -> 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);

SP_CreateTax:

Create Procedure SP_CreateTax 
AS
Begin
    Create Table #Tax (Id int)    
End
FDB
  • 971
  • 16
  • 32

2 Answers2

4

The problem is that the temp table created in your stored procedure is only valid within the scope of that stored proc. Once it's done, the temp table is dropped.

Create the temp table the way you have (the way it works) via the inline sql and move on.

C-Pound Guru
  • 15,967
  • 6
  • 46
  • 67
0

It seems that your stored procedure isn't executed and then your temporary table isn't created. The reason is this:

SqlCommand oSqlCommand = new SqlCommand("SP_CreateTax", oSqlConnection);

Your stored procedure isn't executed, because to execute a procedure, you have to invoke: EXEC SP_CreateTax.

Note: It's not suggested to create stored procedures named with SP, because the names of reserved system stored procedures of SQL Server start with SP. For this reason, the names of custom stored procedures must be distinguished from the system ones, naming them, for example, as uSP_CreateTax.

Alberto Solano
  • 7,972
  • 3
  • 38
  • 61
  • Hi thanks for answer, using CommandType.StoredProcedure you have to set the name of the SP, and also there is no exception on ExecuteNonQuery(), so the sp has been executed. Also to have a confirmation i renamed from #Tax to Tax the table name in the SP and the table was created on the server. – FDB Feb 19 '14 at 16:09
  • @FDB Mmm..I understood. Make sure then that the SQL Server user you're using for the connection has sufficient permissions. – Alberto Solano Feb 19 '14 at 22:03