CODEWITHSUNDEEP
c#sql-server-2008sqlbulkcopy
FDB
FDB

Reputation: 1058

SqlBulkCopy to temporary table in SqlServer 2008

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

Upvotes: 6

Views: 7593

Answers (2)

C-Pound Guru
C-Pound Guru

Reputation: 16368

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.

Upvotes: 4

Alberto Solano
Alberto Solano

Reputation: 8227

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.

Upvotes: 0

Related Questions