The temp table is not being created in the database. I verified that the credentials have access to create a temp table. Copy and pasted the SQL command and it works in SSMS.
No exceptions are thrown when debugging. The cmd variable has the proper connection and SQL text before executing.
I've used the same connection string in other parts of the app to query the server successfully, so there is no issue there.
My goal is to create a temp table, then populate it via SqlBulkCopy then do a merge update then drop the temp table.
EDIT: My error was referencing the wrong table in the DestinationTableName but moreso that I was checking the progress in SSMS with a separate connection that could not see the temp table. Also, the finally statement is redundant. Thanks all!
string tmpTable = @"create table #TempTable
(
[Column1] [varchar](50) NOT NULL,
[Column2] [varchar](50) NOT NULL,
[Column3] [varchar](50) NOT NULL
)";
string connString = "Data Source=AzureDBServer;" +
"Initial Catalog=Database;" +
"User id=UserId;" +
"Password=Password;";
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(tmpTable, connection);
cmd.ExecuteNonQuery();
try
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "#TempTable";
bulkCopy.WriteToServer(dataTable);
string mergeSql = "<Will eventually have merge statement here>";
cmd.CommandText = mergeSql;
int results = cmd.ExecuteNonQuery();
cmd.CommandText = "DROP TABLE #TempTable";
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
finally
{
SqlCommand final = new SqlCommand("DROP TABLE #TempTable", connection);
final.ExecuteNonQuery();
}
}
I'm checking in SSMS using the same credentials, getting the "Invalid object name '#TempTable'" error after the code completes
That's because SSMS is using a different connection, and temp-tables like #Foo are per-connection. You cannot access a #Foo temp-table from any other connection.
It sounds like you want a global temp-table. This is as simple as naming it ##Foo instead of #Foo. Global temp-tables are shared over all connections.
If I comment out the merge portion and go directly to the Drop table command, I get the error "Cannot drop the table ... because it does not exist or you do not have permission." I figured that was because the table wasn't created. What am I missing?
@BenV: you drop the table twice -- once in your main body, then again in your
finally. The latter one will fail.@Jeroen Mostert Ohhh of course, I knew it had to be something stupid simple. Thanks!