[cfaussie] RE: CF to quick!

Taco Fleur Tue, 20 Jan 2004 16:37:37 -0800

Title: Message

Nick your a saviour,

A brilliant idea - I see what your doing (not looked at it in detail yet, but I get the idea).

I guess looking at current_execution_status will do the trick.

I reckon I will do something like

1. call the sp that checks how the job is going

2. if not finished executing then pause

3. check again etc....

4. when finished executing go back to cf and tell it to display the link

Thats how I now envision it after your help.

Cheers

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

-----Original Message-----
From: Nick Gorst [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 21 January 2004 9:13 AM
To: CFAussie Mailing List
Subject: [cfaussie] RE: CF to quick!

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



<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



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

<cfif NOT IsDefined("#SessVar#")>
<cfset "session.#Job_Name#" = "Unknown">
</cfif>

<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>

<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


<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>

<cfswitch _expression_="#qry_job_status.last_run_outcome#">
  <cfcase value="0">
   <cfif evaluate(#SessVar#) eq "Running"> 

    <cfset "#SessVar#" = "Failed">
    <script>
    
    </script>
   </cfif>
   Last Ran #DateFormat(LastDate,"dd/mmm/yyyy")# Failed
  </cfcase>
  <cfcase value="1">
   <cfif evaluate(#SessVar#) eq "Running">
    <cfset "#SessVar#" = "Succeeded">
    <script>
    
    </script>
   </cfif>
   Last Ran #DateFormat(LastDate,"dd/mmm/yyyy")# Succeeded
  </cfcase>
  <cfcase value="3">
   <cfif evaluate(#SessVar#) eq "Running">
    <cfset "#SessVar#" = "Cancelled">
    <script>
    
    </script>
   </cfif>
   Last Ran #DateFormat(LastDate,"dd/mmm/yyyy")# Cancelled By User
  </cfcase>
  <cfDefaultCase>
   Unknown
  </cfdefaultcase>
</cfswitch>
</font>
</cfoutput>
<cfelse>

<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

---
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

[cfaussie] RE: CF to quick!

Reply via email to