October 20, 2006 at 8:25 am
A pair of questions in this one actually.
First, is there a way to export data to a file, using a fixed query, that a user could run on demand? I was currently going to use a dts package and have it scheduled to run every X, but would rather allow the user to run it when they need it. From what I've read setting up 2000 to allow DTS packages to be run from stored procedures is a pain and can open up security risks.
Second, when exporting, can you change the destination file name? Like appending a timestamp to it? Instead of C:\data.cvs have C:\data-11-10-2006.cvs or some such thing?
Thanks for any suggestions!
October 20, 2006 at 9:29 am
Darren Green has some suggestions for executing DTS packages from applications. See http://www.sqldts.com/default.aspx?104.
I've used a solution for this that involves the having a request table that has a row inserted by the application when a user wants to execute a package. There's a scheduled job that runs every 5 minutes and queries the request table. When a new row is found, the job executes the DTS package using DTSRUN. Since the application only needs access to the request table, there's no problem with security as far as running the job or the package.
The answer to your second question is "yes". Use an ActiveX task to set the name of the output file. See http://www.sqldts.com/default.aspx?213.
Greg
Greg
October 24, 2006 at 8:04 pm
A good ol' batch file that uses OSQL or BCP would be easy enough... and, then, the user could provide whatever filename they wanted as a parameter.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2006 at 11:01 am
If its a simple on demand query u can set up an excel with "Import External Data" query and they can run on demand.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply