Home Dashboard Directory Help

SQL Engine - BULK EXPORT command by SQLJack


 as Won't Fix Help for as Won't Fix

Sign in
to vote
Type: Suggestion
ID: 224026
Opened: 10/11/2006 11:39:55 AM
Access Restriction: Public
Duplicates: 512367


There is no simple means to output data to the filesystem from within a stored procedure or sql script.
Sign in to post a comment.
Posted by Jeff Moden 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?
Posted by SQLJack 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?
Posted by Bappy on 6/28/2012 at 3:38 AM
'Resolved' ?

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 :)
Posted by opc.three 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.

Posted by Sunil [MSFT] 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.

Posted by Jeff Moden 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? ;-)
Posted by Jeff Moden on 2/13/2011 at 8:55 AM
Hi Micorsoft...

What would really be cool is if you considered it for a retro-active backfit of SQL Server 2005 and up.
Posted by SQLJack 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?
Posted by Sunil [MSFT] on 1/28/2010 at 9:12 AM
I agree with your points.

Posted by Doron_F 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.
Posted by Sunil [MSFT] on 9/27/2007 at 1:41 PM
This is a useful suggestion. We will consider this in the future release.
Sign in to post a workaround.
Posted by Solomon Rutzky 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 ;-).
Posted by Solomon Rutzky 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.