Databases
StackOverflow2013
Postsdbo
POProgrammatic Export with Indeterminate Table Structure from SQL Server 2008 using T-SQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProgrammatic Export with Indeterminate Table Structure from SQL Server 2008 using T-SQL
    text
    copied!<p>I have searched for an answer to this, and one seems not to exist. Problem: A website is querying a database and unable to return results (as an export to Excel) in a timely fashion. This is primarily due to result set size. I'd like to set up a background process to 'ping' for waiting queries and execute them one-by-one, dumping data into a location to be downloaded from. The 'pinging' task can be handled a whole host of ways. My original ideal solution was a trigger (alternatively, a SQL Server Agent task) that exported the data to the filesystem. But I have run into an issue where I don't know how to set up an amorphous output to the filesystem with a simple T-SQL statement.</p> <p>SSIS is apparently the standard solution to this. I don't know enough about SSIS to know whether it will handle what I want it to do, but I have been told the queries are too great in number / various in output for that to be a feasible solution.</p> <p>xp_cmdshell can be run to do a BCP export. This works fine, but apparently opens a security hole.</p> <p>Previous solutions: A solution I used years ago, DTS passing data straight to the operating system, seems to have been disabled in SQL Server 2008/ 2012. I also used to be able to use sp_makewebtask to export data directly to the filesystem but no longer can do that either.</p> <p>Current solution I am writing a PowerShell script tied to some SQL tables and stored procedures to manage execution. This seems like a non-ideal solution; I'm curious as to whether I have missed something. Is there an easy way to set up SSIS to export data without a structure? A way to create an Excel file on the fly and fill it with data?</p> <p>The answer seems to be No.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload