BookmarkSubscribeRSS Feed
pedrammobedi
Quartz | Level 8

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.

7 REPLIES 7
JuanS_OCS
Amethyst | Level 16

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
Quartz | Level 8

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
Amethyst | Level 16

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
Quartz | Level 8

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
Amethyst | Level 16

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

pedrammobedi
Quartz | Level 8

Yes sorry! But that folder is currently empty.

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

Lorrie_SAS
SAS Employee

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

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2377 views
  • 0 likes
  • 3 in conversation