Well I got code :) You probably will want to read a quick intro to dts somewhere as well. Enterprise Manager (EM) does most of the work though. Run through the export wizard and create the excel spreadsheet you need, and check the checkbox that says "Save DTS Package" or something like that, and give it a name. Now when you go the Data Transformation Services tree in EM you will see the package you saved. If you right click and execute it will create your excel spreadsheet without any input.
Now we need a stored procedure that executes this DTS package so that we can use CF to directly execute it. It is possible to use cfexecute to run dtsrun.exe, but there are complex (to me) permission issues involved. Here is an example SP to fire the dts. CREATE PROCEDURE execDTSPackage AS DECLARE @dtscmd varchar(500) SELECT @dtscmd = "exec master..xp_cmdshell 'dtsrun /SServerName /NDTSPackageName /E EXEC(@dtscmd) GO All the SP does it shell out and call dtsrun.exe, the advantage this has over cfexecute is that it will execute dtsrun.exe on the database server instead of the web server, so you can bypass any permission issues. The SP does need to be run as SA though... If you don't need to send any parameters to the DTS package it's one line of CF code to do the export. Something like: <cfstoredproc procedure="ExcelExport" datasource="#variables.dsn#"></cfstoredproc> If you need to send parameters, perhaps because you need to export a lot of different spreadsheets and can't create a DTS for each, there is a way. You would need to tell the DTS package what piece of data is dynamic and where it is coming from. If you need to do this I can send some code off list. Lots of words for such a quick process, but once you get it down, it really is a piece of cake. -- jon mailto:jonhall@;ozline.net Tuesday, November 5, 2002, 12:25:04 PM, you wrote: BD> Do you happen to have some sample code I could use as a guide. I haven't BD> really done much with DTS. BD> Thanks, BD> Dave BD> -----Original Message----- BD> From: jon hall [mailto:jonhall@;ozline.net] BD> Sent: Tuesday, November 05, 2002 11:49 AM BD> To: CF-Talk BD> Subject: Re: Dump a SQL table into an Excel spreadsheet BD> If you are familiar with DTS, the easiest way would be to create a dts BD> package and call it from a stored proc on the page to write out the BD> excel file. BD> 15 minutes to get the dts right and 3 lines of cf code to call the sp. BD> I'd really recommend at least trying it this way, dts can be quite BD> rewarding in terms of time saved. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
