Cftransaction issue
Posted on Jan 28, 2005
Extending on the DTS project I talked about a few posts ago, I was writing the page that would run the export. Without going into a long explanation as to the reasoning behind some of the structure, the steps were as follows:
- Run a stored procedure to move all purchase data into a "temporary" table of sorts
- Run the DTS package using the stored procedure as described here
- Update all the records moved with a timestamp denoting that they have been previously downloaded
The code was as follows:
<!--- first, move the data to the proper table --->
<cfstoredproc procedure="usp_purchases_export" datasource="#REQUEST.dsn#">
</cfstoredproc>
<!--- now export the data to access --->
<cfstoredproc procedure="usp_executeDTS" datasource="#REQUEST.dsn#" debug="no">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="Server" value="127.0.0.1" null="no">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="PkgName" value="dts_purchases_export" null="no">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="ServerPWD" value="1q2w3e" null="no">
<cfprocresult name="exportData">
</cfstoredproc>
<cfquery name="updateDownloaded" datasource="#REQUEST.dsn#">
UPDATE purchases_main
SET download = #now()#
WHERE download IS NULL
</cfquery>
</cftransaction>
Comments
Sorry I do not have an answer for you, but I, too, and playing with cftransaction and appreicate your sharing your information.
Roxane
Posted By Roxane Rinard / Posted on 11/10/2005 at 1:53 PM
<cfquery name="stProc" datasource="#dsn#">
EXEC hsp_prd_browse
@id='999'
</cfquery>
Posted By Brian Rinaldi / Posted on 11/10/2005 at 2:47 PM