Microsoft Corporate Account
SQL Server Home
SQL Engine - BULK EXPORT command
as Won't Fix
10/11/2006 11:39:55 AM
There is no simple means to output data to the filesystem from within a stored procedure or sql script.
A new command "BULK EXPORT" should be created to compliment the existing "BULK INSERT" and the recently proposed "BULK UPDATE". BULK EXPORT would allow output of data to the filesystem from within a Stored Proc or TSQL script and would eliminate the need for solutions like xp_cmdshell + bcp or osql, sp_OA + DMO or COM object DLLs to accomplish this task .
to post a comment.
Please enter a comment.
on 5/21/2014 at 6:13 AM
Sunil of Microsoft wrote:
"we are faced with the challenges of customer ASKs"
"Customer ASKs"???? What's that and how do we get on THAT list?
Would you good folks reconsider this wonderful and very needed change if I made is so the number of upvotes on this change doubled?
on 1/8/2014 at 6:59 AM
Resolved as "Won't Fix" is disappointing at best - there are many valid use cases for this feature, can we get the suggestion re-opened and considered for a future release?
on 6/28/2012 at 3:38 AM
refusing to do something isn't the same as 'Resolved'.. Can I suggest you change this text from 'Resolved' to "Tough buns"? :D
Can you pleae advise if this will be considered again in the next version of SQL Server (whats that going to be SQL Server 2014?) as like Orlando said, the only way around this is to open up the use of xp_CmdShell which is insecure and against microsoft's own recommended best practices. SSIS is a capable product and has it's place but it is NOT the perfect solution to every single ETL task like microsoft's developers seem to think it is.
In my experience as an IT professional, 9 times out of 10 the simplest solution is going to be the best solution. Using a massive ETL package like SSIS to simply dump a table to flat file is most certainly not a simple solution.
That's my 2p anyway :)
on 6/17/2012 at 2:18 PM
The closure of this item as "Won't fix" leaves developers that want or need to keep all their code in T-SQL to enable xp_cmdshell and use either bcp or SSIS to get data from table to disk. Or possibly use the SQLCLR in some creative way. In any event, the lack of a native T-SQL method is a glaring hole in T-SQL. It would be nice to start closing the security loops we are compelled to open to do basic things like writing data from a table to disk. I for one am disappointed in the resolution of this item.
on 12/20/2011 at 12:09 PM
In any release, we are faced with the challenges of customer ASKs and match those with the resources and the available time. Unfortunately, there will be no support for Bulk Export in SQL 2012.
on 10/13/2011 at 6:17 PM
And here we are, just several months away from yet another release of SQL Server and, unless I missed something, there's no sign of such an improvement as "BULK EXPORT". If I asked for a "suite" of file handlers available through T-SQL, would that be cause for quicker action and more promising action? ;-)
on 2/13/2011 at 8:55 AM
What would really be cool is if you considered it for a retro-active backfit of SQL Server 2005 and up.
on 9/22/2010 at 11:13 AM
Almost 4 years since I suggested this and 3 since it was deemed useful - any thoughts if this may make it into the 2011 release?
on 1/28/2010 at 9:12 AM
I agree with your points.
on 12/31/2009 at 12:18 PM
I also can appreciate the BULK EXPORT. Right now bigger files are cut and the file become useless.
So this way the entire sql statement plus the export process SCRIPT is all encapsulated in one SP one place and one technology.
Also this way the entire process is done locally on the server and thus minimize any security issue.
on 9/27/2007 at 1:41 PM
This is a useful suggestion. We will consider this in the future release.
to post a workaround.
Please enter a workaround.
on 8/27/2015 at 7:46 PM
I forgot to mention two things:
1) The DB_BulkExport stored procedure mentioned in my initial Workaround runs on every version of SQL Server, starting with SQL Server 2005. So, even if this feature is ever implemented (but doesn't look likely after 9 years and 107 votes), you don't need to wait on regression testing, etc.
2) There needs to be a way to edit these Comments and Workarounds so people don't have to submit additional entries ;-).
on 8/27/2015 at 7:42 PM
Since Orlando mentioned using SQLCLR in a creative way (opc.three on 6/17/2012 at 2:18 PM), it does not seem out of place for me to share that such a SQLCLR Stored Procedure already exists (and has for quite some time, actually, but I haven't put much effort into advertising ;-). The SQL# ( <a href="http://SQLsharp.com">http://SQLsharp.com</a> ) library (that I wrote) has a stored procedure called DB_BulkExport. This stored procedure combines quite a bit of the main functionality of both BCP and SSIS and can handle dynamic text-qualification, configurable conversions of dates and bit fields, etc and without requiring a separate configuration file on the file system (i.e. Format Files). I should also mention that while there is a Free version of SQL#, the DB_BulkExport stored procedure is only available in the Full version. However, it also lets you optionally specify a connection string to connect to another instance. So, if you work in one of those "no SQLCLR on my watch" shops, you can just set up an instance of SQL Server Express (regular or even LocalDB) and run it from there instead of the server where the data resides.
© 2016 Microsoft