i snatched mine from www.swynk.com.
in the interest of saving you time, here's mine. as a side note, the the user running
the package needs to have execute rights to the sp_OA* SPs.
---
CREATE PROC TH_OnsiteLoad4 as
DECLARE @hr int, @oPKG int
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
--Loading the Package:
-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("<servername>", "<user>", "<password>", 0, , , , "<DTS Package
Name>")',
NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN
END
--Cleaning up:
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
---
christopher olive
cto, vp of web development, vp it security
atnet solutions, inc.
410.931.4092
http://www.atnetsolutions.com
-----Original Message-----
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 08, 2002 2:24 PM
To: CF-Talk
Subject: RE: SQL Statement dumping data
PS - Chris, do you have an example of an SP that executes a DTS package? I
would find that useful if you do.
Mark
-----Original Message-----
From: Jason Larson [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 08, 2002 9:01 AM
To: CF-Talk
Subject: RE: SQL Statement dumping data
WOW!!, I haven't worked with DTS packages and not much with stored
procedures. I will do some research. You think that this is a better
solution then building a cf template and setup an automated task to run
the cf template (in order to process the csv file)?
Thanks,
Jason Larson
-----Original Message-----
From: Christopher Olive [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 08, 2002 9:50 AM
To: CF-Talk
Subject: RE: SQL Statement dumping data
set up a DTS package to import the CSV file.
create an SP to run the DTS package.
use SQL Agent to schedule the SP.
christopher olive
cto, vp of web development, vp it security
atnet solutions, inc.
410.931.4092
http://www.atnetsolutions.com
-----Original Message-----
From: Jason Larson [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 08, 2002 11:31 AM
To: CF-Talk
Subject: RE: SQL Statement dumping data
What I am trying to accomplish is:
I have a client that will be pushing up a csv file to a site and then I
will setup a autmoated task that will dump all the data in a table and
then refresh the data with the data in the csv file.
Thanks for the quick reply
Jason
-----Original Message-----
From: Clint Tredway [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 08, 2002 9:26 AM
To: CF-Talk
Subject: Re: SQL Statement dumping data
Do you mean delete all the data or Export the data?
Clint
----- Original Message -----
From: "Jason Larson" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, March 08, 2002 10:22 AM
Subject: OT: SQL Statement dumping data
> I was wondering if anybody had the sql statement that will allow me to
> dump all the data out of the table in a database?
>
> Thanks for the help
> Jason
>
>
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
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
