It can also be called via COM. ----- Original Message ----- From: "Paul Wille" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, November 16, 2001 1:53 PM Subject: RE: SQL-Server DTS through CF
> Richard: > > I have recently tackled the same task for a project that I was working > on. Mind you, my usage was with SQL Server 2000, so any > incompatibilities (of which I believe there are none) with 7.0 are not > accounted for here. > > First of all, DTS packages can only be called via command prompt using > the DTSRun utility that comes with SQL Server. To execute this through > CF, you can leverage an Extended Stored Procedure that comes with SQL > Server, called xp_cmdshell. Since it's a stored procedure, it can be > invoked via CFSTOREDPROC or even through CFQUERY. Keep in mind that > only SQL Server logins with the sysadmin roll can execute Extended > Stored Procs. > > My need was to use DTS to import data from an Excel file uploaded by the > user into SQL Server, and perform some manipulation to that data. I > wrote my own stored procedure to invoke the extended stored procedure, > which then ran the DTS Run Command: > > Stored Proc --invokes---> xp_cmdShell --invokes---> DTSRun > > I then pass in the necessary parameters for the DTSRun utility into my > stored proc, as shown in the below example: > > ----------------------------------------------------------- > BEGIN MY STORED PROC > ----------------------------------------------------------- > CREATE PROC sp_Survey_import > > @ImportFilePath nvarchar(100), > @DTSName nvarchar(20), > @server nvarchar(30), > @username nvarchar(15), > @password nvarchar(15) > AS > > DECLARE @dtsScript nvarchar(100); > SET @dtsScript = "dtsrun /n " + @DTSName + " /s " + @server + " > /u " + @username + " /p " + @password > PRINT @DTSName > EXEC master.dbo.xp_cmdshell @dtsScript > > GO > ----------------------------------------------------------- > END MY STORED PROC > ----------------------------------------------------------- > > The import path must be publicly accessible to the Database server, > since that is where the procedure is invoked. > > If you need CF to handle DTS, this is the best method that I have found. > > --Paul > > Paul W. Wille [EMAIL PROTECTED] > -------------------------------------------------- > Certified Advanced ColdFusion 5.0 Developer > -------------------------------------------------- > ISITE Design, Inc. - Solutions Architect > www.isitedesign.com > 503.221.9860 x110 > 888.269.9103 > 503.221.9865 fax > > > > > > > -----Original Message----- > From: Richard Colman [mailto:[EMAIL PROTECTED]] > Sent: Friday, November 16, 2001 11:32 AM > To: CF-Talk > Subject: SQL-Server DTS through CF > > I would like to be able to import an ACCESS data table into SQL-Server > 2000 (using DTS?), and do a reverse export, under CF control. This needs > to happen every month. Does anyone know of any documentation or sample > code for this kind of task? Any pointers would be much appreciated. > > Thank you. > > Rick Colman > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
