SqlBulkCopy.WriteToServer fails on SQL Azure if destination table is temporary
SqlBulkCopy.WriteToServer fails on SQL Azure if destination table is temporary.
Upvotes: 30<=-=Oct 25 2011 2:38PM=-=>
Thanks for reporting this issue. SqlBulkCopy.WriteToServer does not work in this case because SQL Azure currently does not support temp table. We will route this bug to SQL Azure team so that they can follow up with you.
Young Gah Kim
ADO.NET Managed Providers and DataSet Team
Thanks for reporting the issue. The problem is due to the fact that SQL Azure doesn’t support 3-part names for stored procedure calls. In this case, the driver/provider use tempdb.sys.sp_* syntax to access certain stored procedures that provide metadata about objects and these calls fail. We are looking at resolving the issue & enabling 3-part names for tempdb calls.
Otherwise, you can use temporary tables in SQL Azure fine – we do support it with the restriction mentioned above. Additionally, you can’t create temporary tables using SELECT…INTO statement since SQL Azure doesn’t support heaps.
Umachandar, SQL Programmability Team
it’s been 6 months since I reported the issue (again) and was instructed, that the bug would be brought to the attention of the language team. I have checked if this feature has been made available on SQL Azure recently, and from my understanding, it’s still waiting to be fixed.
This Connect bug was submitted in late 2010 (more than two years ago as of this writing), and still isn’t resolved despite numerous releases of SQL Azure and numerous customers requests.
Our team was quite surprised about this bug (it’s not a missing feature; it’s a bug). With a shared codebase for deployment on premise and Windows Azure, we’re looking at supporting the lowest common denominator – SQL Azure, and especially with lack of temp tables in bulk scenarios, we’re looking at ackward implementation (physical table, with each bulk working set identified by a seperate column).
Here’s a reference to the discussion on the Windows Azure SQL Database forum:
Software Architect at MailTalk (enlisted in the BizSpark One program)
Would you be so kind to update the status of this issue? A simple “yes, we are working on a fix and it will be available in the next version or SQL Azure”, or “no, this is by design and will never be fixed”?<=-=Sep 18 2014 10:20AM=-=>
What’s the status on this? I can create local temp table in Azure but SqlBulkCopy.WriteToServer fails.<=-=Oct 16 2014 12:22AM=-=>
I just ran into this issue myself. Any updates? Why has it taken so long to not fix?<=-=Jun 3 2016 6:56AM=-=>
5 (five) years later and you still haven’t figured out how to support 3-part sproc names for TempDB, so SqlBulkCopy can work on Azure with temp tables? Not impressed with Microsofts support.