Scheduled data query not updating the data source

[pedrammobedi]

In SAS VA I am using the Data Builder (Import Data using ODBC) to read the contents of a database table into Public LASR.

I then create a data query to read the content of this resulted dataset. 

Next, I create a schedule for the data query so that it updates the dataset on an hourly basis.

All the above steps seem to work. The schedule is created and when I look in my Windows Task Scheduler (my SAS is installed on a Windows server), the task is successfully triggered. Except my data set is not updated.

FYI, my table contains only one column called number that, at the time of creating the dataset it contains the values 1, 2, 3, 4, 5.

I then went to the database and inserted new values (6,7,8,9 and 10) to the table. The task is triggered but my data set still contains 1,2,3,4,5 when I load the report.

[JuanS_OCS]

Hi,

I would advise you to find the logs of the execution of this query.

As far as I can recall, the windows scheduler task will point you to an vbs code that will be on your Lev1\SchedulingServer folder. This vbs code will have the command line that actually calls to the .sas file and the same command line will have the location and name of the related log file.

If you can find this log, you can also attach it tho this topic, and we can translate the problem for you.

[pedrammobedi]

Here is the contents of the .vbs file that the Task runs:

(queryPedramTests is the name of the data query I created)

' *** Start of script for flow vdb_queryPedramTests_1473160746881 ***

' Define constants needed for accessing files
Const ForReading = 1, ForWriting = 2, ForAppending = 8

flowStatus = 0

scriptFilename = "E:\sas\config\Lev1\SchedulingServer\K_pedmob\vdb_queryPedramTests_1473160746881\vdb_queryPedramTests_1473160746881.vbs"

' Update date and time variable
curDateTime = Now()

' Create timestamp used in naming the status file
timeStamp = Left("0000", 4 - Len(Year(curDateTime))) & Year(curDateTime) & Left("00", 2 - Len(Month(curDateTime))) & Month(curDateTime) & Left("00", 2 - Len(Day(curDateTime))) & Day(curDateTime) & Left("00", 2 - Len(Hour(curDateTime))) & Hour(curDateTime) & Left("00", 2 - Len(Minute(curDateTime))) & Minute(curDateTime) & Left("00", 2 - Len(Second(curDateTime))) & Second(curDateTime)

statusFilename = "E:\sas\config\Lev1\SchedulingServer\K_pedmob\vdb_queryPedramTests_1473160746881\" & timeStamp & "_vdb_queryPedramTests_1473160746881_status.log"

' Initialize references to FileSystem and Shell objects
Set fileSys = Wscript.CreateObject("Scripting.FileSystemObject")
Set shell = Wscript.CreateObject("Wscript.Shell")

' Open status file
Set statusFile = fileSys.OpenTextFile(statusFilename, ForWriting, True)

' *** Start of flow ***

' Log start of flow to status file
statusFile.WriteLine("Flow STARTING...")

' *** No Dependencies ***

' *** Begin Job Event ***

' Update date and time variables
curDateTime = Now()
curDate = Left("00", 2 - Len(Month(curDateTime))) & Month(curDateTime) & "/" & Left("00", 2 - Len(Day(curDateTime))) & Day(curDateTime) & "/" & Left("0000", 4 - Len(Year(curDateTime))) & Year(curDateTime)
curTime = Left("00", 2 - Len(Hour(curDateTime))) & Hour(curDateTime) & ":" & Left("00", 2 - Len(Minute(curDateTime))) & Minute(curDateTime) & ":" & Left("00", 2 - Len(Second(curDateTime))) & Second(curDateTime)

' Log start of job to status file
statusFile.WriteLine("Job vdb_queryPedramTests_1473160746881_A5EW3E0G_BV000005 STARTING " & curDate & " " & curTime)

' Enable error handling
On Error Resume Next

' Execute job
errorLevel = shell.Run("E:\sas\config\Lev1\SASApp\BatchServer\sasbatch.bat -log E:\sas\config\Lev1\SASApp\BatchServer\Logs\vdb_queryPedramTests_1473160746881_#Y.#m.#d_#H.#M.#s.log -print E:\sas\config\Lev1\SASApp\BatchServer\Output\vdb_queryPedramTests_1473160746881.lst -batch -noterminal -logparm ""rollover=session""  -sysin E:\sas\config\Lev1\SASApp\SASEnvironment\SASCode\Jobs\vdb_queryPedramTests_1473160746881.sas", , True)

If Err.Number <> 0 Then
  status_A5EW3E0G_BV000005 = Err.Number
  Err.Clear
Else
  status_A5EW3E0G_BV000005 = errorLevel
End If

' Disable error handling
On Error Goto 0

' Update date and time variables
curDateTime = Now()
curDate = Left("00", 2 - Len(Month(curDateTime))) & Month(curDateTime) & "/" & Left("00", 2 - Len(Day(curDateTime))) & Day(curDateTime) & "/" & Left("0000", 4 - Len(Year(curDateTime))) & Year(curDateTime)
curTime = Left("00", 2 - Len(Hour(curDateTime))) & Hour(curDateTime) & ":" & Left("00", 2 - Len(Minute(curDateTime))) & Minute(curDateTime) & ":" & Left("00", 2 - Len(Second(curDateTime))) & Second(curDateTime)

' Log completion of job and exit code to status file
statusFile.WriteLine("Job vdb_queryPedramTests_1473160746881_A5EW3E0G_BV000005 COMPLETE " & curDate & " " & curTime & " status=" & status_A5EW3E0G_BV000005 & ".")

' Set flag indicating that job has executed
exec_A5EW3E0G_BV000005 = True

' Update flow exit code
If flowStatus = 0 Then
  flowStatus = status_A5EW3E0G_BV000005
End If

' *** End Job Event ***

' Update date and time variables
curDateTime = Now()
curDate = Left("00", 2 - Len(Month(curDateTime))) & Month(curDateTime) & "/" & Left("00", 2 - Len(Day(curDateTime))) & Day(curDateTime) & "/" & Left("0000", 4 - Len(Year(curDateTime))) & Year(curDateTime)
curTime = Left("00", 2 - Len(Hour(curDateTime))) & Hour(curDateTime) & ":" & Left("00", 2 - Len(Minute(curDateTime))) & Minute(curDateTime) & ":" & Left("00", 2 - Len(Second(curDateTime))) & Second(curDateTime)

' Log completion of flow and exit code to status file
statusFile.WriteLine("Flow vdb_queryPedramTests_1473160746881 COMPLETE " & curDate & " " & curTime & " status=" & flowStatus & ".")

' Close status file
statusFile.Close

' Exit flow and return status
Wscript.Quit(flowStatus)

' *** End of script for flow vdb_queryPedramTests_1473160746881 ***

[JuanS_OCS]

Then, just reading this file and as mentioned those are the locatins of your logs we need to read:

-log E:\sas\config\Lev1\SASApp\BatchServer\Logs\vdb_queryPedramTests_1473160746881_#Y.#m.#d_#H.#M.#s.log 
-print E:\sas\config\Lev1\SASApp\BatchServer\Output\vdb_queryPedramTests_1473160746881.lst

 Select the latest date that failed, of course.

[pedrammobedi]

This is the contents of the latest log.

Flow STARTING...
Job vdb_queryPedramTests_1473160746881_A5EW3E0G_BV000005 STARTING 09/06/2016 14:20:59
Job vdb_queryPedramTests_1473160746881_A5EW3E0G_BV000005 COMPLETE 09/06/2016 14:21:04 status=0.
Flow vdb_queryPedramTests_1473160746881 COMPLETE 09/06/2016 14:21:04 status=0.

[JuanS_OCS]

You just checked one of the 2 logs that I refered, didn't you? Let's give a look to the other log.

[pedrammobedi]

Yes sorry! But that folder is currently empty.

E:\sas\config\Lev1\SASApp\BatchServer\Output

[Lorrie_SAS]

Hi pedrammobedi,

If I understand correctly, you are trying to using the scheduling feature to update the data that is being imported from ODBC. As far as I know, the scheduling feature can’t be used in this way. You can create a schedule to keep the data query up-to-date, but not the data sources that the data query is based on.

Best regards,

Lorrie