[cfaussie] RE: CF to quick!

[Nick Gorst]

Title: Message

Taco

 

Ugly code to follow :)

 

I'm not sure that this will help But I had to do some heavy duty DST transfers from a web interface and I had a page that displayed the status of the Job using the following stored procedure The following code may be pretty ugly but it worked and given the time I had to develop this solution it was good enough. The code may take a little reading as I developed it in a hurry but if you have any questions let me know.

 

The point here is you could disable the download function until the job status is "complete"

 

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE sp_Job_Status 

 

@P_Job_Name VARCHAR(150)

 

AS

 

EXEC msdb..sp_help_job
@job_name =  @P_Job_Name,
@job_aspect = 'Job'

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

That Procedure was called by a program that processed the result string

 

<!--- Call the Stored Proc --->

<cfstoredproc
 procedure="sp_Job_Status"
 datasource="#Application.DSN#"
 username="#Application.DSNUser#"
 password="#Application.DSNPWD#">
  <cfprocparam
   type="In"
   cfsqltype="CF_SQL_VARCHAR"
   dbvarname="@P_Job_Name"
   value="#Job_Name#"
   null="No">
  <cfprocresult
   name="qry_job_status">
</cfstoredproc>

 

NB: this was called as acustom tag it is a bit clumsy the

reason for using session variables is to do a comparison

over time of the status of the job.

 

If it was running and has

stopped with a completion status of 0 it failed

 

<!--- Process the string --->

<cfset Job_Name = Attributes.Job_Name>
<cfset SessVar = "session."& "#Job_Name#">

 

<cfif NOT IsDefined("#SessVar#")>
 <cfset "session.#Job_Name#" = "Unknown">
</cfif>
<!--- List the Job status information --->
<cfinclude template="/cf_tools/query/qry_Job_Status.cfm">
<cfif qry_job_status.last_run_date neq "" AND Len(qry_job_status.last_run_date) eq 8>
 <!--- Convert the last run date into a date parameter --->
 <cfset LastDate =
  CreateDate (MID(qry_job_status.last_run_date,1,4),
     MID(qry_job_status.last_run_date,5,2),
     MID(qry_job_status.last_run_date,7,2))>
</cfif>
    <cfset Temp = Job_Name
    
<!--- If the job is not currently running --->
<cfif TRIM(qry_job_status.current_execution_step) eq "0 (unknown)">
 <cfoutput>
 <cfif DateFormat(LastDate,"DDMMYYYY") eq DATEFORMAT(NOW(),"DDMMYYYY")>
  <FONT COLOR="Navy">
 <cfelse>
  <font Color=Black>
 </cfif>
 <!--- Display appropriate message based on Jobs last outcome --->
 <cfswitch _expression_="#qry_job_status.last_run_outcome#">
  <cfcase value="0">
   <cfif evaluate(#SessVar#) eq "Running"> <!--- The last status was "running" --->

    <cfset "#SessVar#"  = "Failed">
    <script>
    <!--
     self.focus()
     alert("A Job Failed")
    //-->
    </script>
   </cfif>
   Last Ran #DateFormat(LastDate,"dd/mmm/yyyy")# Failed
  </cfcase>
  <cfcase value="1">
   <cfif  evaluate(#SessVar#)  eq "Running">
    <cfset "#SessVar#"  = "Succeeded">
    <script>
    <!--
     self.focus()
     alert("A Job finished Successfully")
    //-->
    </script>
   </cfif>
   Last Ran #DateFormat(LastDate,"dd/mmm/yyyy")#  Succeeded
  </cfcase>
  <cfcase value="3">
   <cfif  evaluate(#SessVar#)  eq "Running">
    <cfset "#SessVar#"  = "Cancelled">
    <script>
    <!--
     alert("A Job Was Cancelled")
    //-->
    </script>
   </cfif>
   Last Ran #DateFormat(LastDate,"dd/mmm/yyyy")#  Cancelled By User
  </cfcase>
  <cfDefaultCase>
   Unknown
  </cfdefaultcase>
 </cfswitch>
 </font>
</cfoutput>
<cfelse>
 <!--- Display a running message --->
 <cfset "session.#Job_Name#"  = "Running">
 <cfoutput><Font color="RED">Running...</FONT></cfoutput>
</cfif>

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Taco Fleur
Sent: Wednesday, 21 January 2004 8:38 AM
To: CFAussie Mailing List
Subject: [cfaussie] CF to quick!

I have a process that goes as follows:

1. Copy template file (xls or mdb) to new file name
2. Run DTS package via cfexecute
3. DTS exports records to the new file we copied in step 1
4. The user downloads the file or has it send to him via email

When the user clicks export a link to the file appears, but the export is not finished.

What do you reckon is the best way of handling this?

1. I can check if the file exists, but then I'd first have to move it away from where I store it now, and have DTS to do the actual moving of the file, most likely I will have to copy it (due to locking issues) which brings along other work.

2. Your brilliant idea!

Taco Fleur
07 3535 5072
Blog: http://www.tacofleur.com/index/blog/
Methodology: http://www.tacofleur.com/index/methodology/
Tell me and I will forget
Show me and I will remember
Teach me and I will learn

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004