Fixing a SQL Server Job Status Without Restarting Services

SQL Server logo

Yesterday morning I received notice that a SQL Server job for an application I support that normally ran fairly regularly was not working. This job was not kicked off by a manual schedule, but was called from elsewhere.

I was able to run the job manually with no problem at all. Nevertheless, the job did not start running “automatically” as expected. After some digging, I found out that the job was kicked off in a database trigger on INSERT.

The section of code that kicked off the job looked like this:

SET @JOB_NAME = N'My job name'; 
 
IF NOT EXISTS(     
		select 1 
		from msdb.dbo.sysjobs_view job  
		inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id 
		where  
			activity.run_Requested_date is not null  
		and activity.stop_execution_date is null  
		and job.name = @JOB_NAME 
		) 
	BEGIN      
		PRINT 'Starting job ''' + @JOB_NAME + ''''; 
		EXEC msdb.dbo.sp_start_job @JOB_NAME; 
	END 
ELSE 
	BEGIN 
		PRINT 'Job ''' + @JOB_NAME + ''' is already started '; 
	END 

So this code should only return a row (and prevent job execution if the job is already running. The job was not running, and yet a row came back saying that the job had started two days before and never finished.

It just so happened that the SQL Server cluster had to be rebooted at just that time. The job appeared to be running, but was not.

The first solution: Restart the SQL Server Agent. This had no effect on the sysjobactivity table.

The second solution: Restart the entire SQL Server instance. This was not done, due to critical operations at work.

What else could be done?

Manually updating the table to reflect that the job was no longer running!

DECLARE @job_name VARCHAR(2000) = 'Your job name'; 

DECLARE @job_id uniqueidentifier =
    (SELECT job_id
    FROM msdb.dbo.sysjobs
    WHERE NAME LIKE @job_name); 

UPDATE msdb.dbo.sysjobactivity
SET stop_execution_date = GETDATE()
WHERE job_id = @job_id
AND start_execution_date =
    (SELECT MAX(start_execution_date)
    FROM msdb.dbo.sysjobactivity
    WHERE job_id = @job_id
AND run_Requested_date IS NOT NULL  
AND stop_execution_date IS NULL);

EXEC sp_stop_job @job_id = @job_id;

Running this block of code enters a time (now) for job execution end, and also manually sends a job stop command in case the job had started in the meantime.

After this, the trigger worked as expected!

Widgets in Temenos Quantum Visualizer for iOS and Android

Temenos / Kony Quamtum Visualizer

Over the last few weeks, I have been building an application using Temenos (formerly Kony) Quantum Visualizer version 9. This app will eventually be pushed to both Apple and Android phones and tablets, which is the main reason someone would use Visualizer instead of developing natively with XCode and Android Studio.

In theory, the JavaScript code written in the Visualizer IDE will result in the same UI on both the iOS and Android platforms, but that isn’t always the case.

For the first time, I got an actual JavaScript error on the Android app, whereas the iOS app worked perfectly. Apparently, Android isn’t as forgiving when accidentally leaving out the “new” keyword when instantiating a widget – in my case, a RadioButtonGroup. (The error I got on the Android side was “invalid operation : trying to create object without ‘new’ keyword”.)

The RadioButtonGroup widgets, when set in Toggle mode, are rendered differently on iOS and Android. That wasn’t really a problem in and of itself. The problem was that the iOS widgets looked good and took up the entire width of the parent container (a FlexScrollContainer), and the Android screen had the radio buttons bunched up on the left side of the screen.

RadioGroupButton differences on iOS vs. Android

For whatever reason, the Android app required that the hExpand property be set to true, and it didn’t matter on the iOS version.

The moral of the story: If your UI looks different in unexpected ways on one platform, it probably will require explicitly detailed widget property attributes to make them look more similar.

 

Automate the Export of Primavera P6 Projects using Python – Part 2

Primavera P6 and Python logos

Happy New Year!

The result of Part 1 was a list of XER files, all of which were in one folder. It is not clear from where in the Enterprise Project Structure (EPS) that each file belonged.

P6 EPPM EPS example

Perhaps you would like to create a hierarchy of folders similar to the project screen in P6?

I have modified my original Python script to build out a folder structure that is similar to the EPS. I have not been able to find all levels of the hierarchy within the P6 database, but I was able to get most of the levels represented.

import cx_Oracle
import datetime
import fileinput
import os
import subprocess
import sys
from codecs import open

arguments = len(sys.argv)-1
if arguments < 6:
	print ("The script is called with %i arguments - 6 arguments are required. (DBUsername, DBpassword, DBalias, APPusername, APPpassword, OneXer)" % (arguments))
	sys.exit()
	
DBusername= sys.argv[1] 
DBpassword=sys.argv[2] 
DBalias=sys.argv[3] 
APPusername=sys.argv[4] 
APPpassword=sys.argv[5]
OneXer=sys.argv[6]

if OneXer != "0":
	OneXer = True
else:
	OneXer = False

"""
Url: https://gist.github.com/wassname/1393c4a57cfcbf03641dbc31886123b8
"""
import unicodedata
import string

valid_filename_chars = "-_.() %s%s" % (string.ascii_letters, string.digits)
char_limit = 255

def clean_filename(filename, whitelist=valid_filename_chars, replace=' '):
    # replace spaces
    for r in replace:
        filename = filename.replace(r,'_')
    
    # keep only valid ascii chars
    cleaned_filename = unicodedata.normalize('NFKD', filename).encode('ASCII', 'ignore').decode()
    
    # keep only whitelisted chars
    cleaned_filename = ''.join(c for c in cleaned_filename if c in whitelist)
    if len(cleaned_filename)>char_limit:
        print("Warning, filename truncated because it was over {}. Filenames may no longer be unique".format(char_limit))
    return cleaned_filename[:char_limit]  
"""
End of function
"""

def secure_log():
	if os.path.exists('myLog.txt'):
		with fileinput.FileInput('myLog.txt', inplace=True) as file:
			for line in file:			
				print(line.replace(APPpassword, len(APPpassword)*'X'), end='')

ospath = os.path.dirname(os.path.abspath(__file__))
pmpath = subprocess.check_output("WHERE PM.EXE", shell=True) 


def begin_process():
	secure_log()
	numtodo = 0
	proj_name_str_first = ''
	if os.path.exists("actions.xml"):
		os.rename("actions.xml", "actions" + datetime.datetime.now().strftime("%Y%m%d%H%M%S") + ".xml")	

	con = cx_Oracle.connect("{}/{}@{}".format(DBusername, DBpassword, DBalias))
	#print (con.version)

	cursor = con.cursor()
	if OneXer:
		cursor.execute("""
			SELECT DISTINCT PROJ_SHORT_NAME FROM PROJECT 
			WHERE ORIG_PROJ_ID IS NULL			
			AND PROJ_SHORT_NAME NOT LIKE '%^%'
			AND PROJ_SHORT_NAME NOT LIKE '%&%'
			ORDER BY PROJ_SHORT_NAME
			""")
	else:
		cursor.execute("""
			SELECT PROJ_SHORT_NAME, FILEPATH FROM (
			SELECT PROJ_SHORT_NAME,
			LTRIM(MAX(SYS_CONNECT_BY_PATH(OBS_NAME,'\\'))
			KEEP (DENSE_RANK LAST ORDER BY OBS_ID),',') AS FILEPATH
			FROM
			(SELECT REPLACE(REPLACE(REPLACE(REPLACE(o.OBS_NAME,',',''),' ','_'),'&','and'),'/','-') AS OBS_NAME, p.PROJ_SHORT_NAME, o.PARENT_OBS_ID, o.OBS_ID
			FROM OBSPROJ op
			JOIN OBS o ON op.OBS_ID = o.OBS_ID
			JOIN PROJECT p ON p.PROJ_ID = op.PROJ_ID
			WHERE p.ORIG_PROJ_ID IS NULL
			AND p.RISK_LEVEL IS NULL
			AND p.PROJ_SHORT_NAME NOT LIKE '%^%'
			AND p.PROJ_SHORT_NAME NOT LIKE '%&%')
			GROUP BY PROJ_SHORT_NAME
			CONNECT BY PARENT_OBS_ID = PRIOR OBS_ID AND PROJ_SHORT_NAME = PRIOR PROJ_SHORT_NAME
			START WITH PARENT_OBS_ID IS NULL)
			ORDER BY PROJ_SHORT_NAME ASC
			""")
			
	f = open(ospath + '\\actions.xml','a')
	xml_file_begin = '<actions>\n'
	f.write(xml_file_begin)
	
	if OneXer:
		xml_file = '\t<action>\n\t\t<type>export</type>\n\t\t<Projects>'
		f.write(xml_file)
			
	for row in cursor:
		proj_name_actual = row[0]				
		filepath = (ospath + row[1])
		proj_name_str = filepath + '\\' + clean_filename(proj_name_actual)
		#print(proj_name_str)		
		#print("Project:", proj_name_actual)
		if OneXer:
			xml_file = '\n\t\t\t<ProjectID>' + proj_name_actual + '</ProjectID>'
			f.write(xml_file)
		else:
			if not os.path.exists(proj_name_str + '.xer'):
				if not os.path.exists(filepath):
					#print('mkdir ' + '\"' + filepath + '\"')
					os.system('mkdir ' + filepath)
				if proj_name_str_first == '':
					proj_name_str_first = proj_name_str
				numtodo += 1
				
				xml_file = '\t<action>\n\t\t<type>export</type>\n\t\t<Projects>\n\t\t\t<ProjectID>' + proj_name_actual + '</ProjectID>\n\t\t</Projects>\n\t\t<outputFile>' + proj_name_str +'.xer</outputFile>\n\t</action>\n'	
				f.write(xml_file)	
			else:
				print("Skipped: Project "+ proj_name_actual + " already exported or errored.")
			
	if OneXer:
		xml_file = '\n\t\t</Projects>\n\t\t<outputFile>' + ospath + '\\OneFile.xer</outputFile>\n\t</action>\n'
		f.write(xml_file)	
	
	xml_file_end = '</actions>\n'
	f.write(xml_file_end)
	f.close()
	if not OneXer:
		print("Number of projects left to process:", numtodo)		
	os.system('PM.EXE /username=' + APPusername + ' /password=' + APPpassword + ' /actionScript=\"' + ospath + '\\actions.xml\" /logfile="' + ospath + '\\_myLog.txt\"')
	con.close()
	secure_log()
	
	if numtodo > 0:
		if not os.path.exists(proj_name_str_first + '.xer'):
			print("Errored on: ", proj_name_str_first)
			g = open(proj_name_str_first + '.xer','w')
			g.write('Error!!')
			g.close()
			h = open(proj_name_str_first + '-ERRROR.txt','w')
			h.write('Error!!')
			h.close()
		begin_process()		
		
begin_process()

The result of running this script should be a folder structure with names that are similar to the headings in the EPS. The exported files will be put into these folders. Strange characters in the names may need to be escaped or replaced (as is done above in the SQL query).

Automate the Export of Primavera P6 Projects using Python

Primavera P6 and Python logos

With a switch from an old version of P6 (8.2) to a new one (18.8), I needed to export our old projects from the 8.2 database into XER files. After discovering that the number of projects in that old database was in the thousands, I knew I’d have to automate the process if I wanted to be at all productive for the next couple of weeks.

Oracle does provide a way for exporting projects using the command line. Once I discovered this, I knew there must be some way to script this for an indefinite number of projects.

Using Python’s cx_Oracle module, I could connect to the P6 Oracle Database to get the project names, and begin building the required XML file that would direct P6 what to do.

The script below receives six (6) parameters: the database username and password (what you would log onto Oracle with to look at the database), the database alias (which must be in TNSnames.ora or LDAP), an application username and password, and either a 0 (for multiple XER files) or a 1 (for one XER file for all projects).

Nota bene: The “one XER” option has not been fully tested – unless your database is small and/or you are running a 64-bit client of P6, you will likely get an out of memory error using this one.

#written by David Young on 2019/12/6
import cx_Oracle
import datetime
import fileinput
import os
import subprocess
import sys
from codecs import open

arguments = len(sys.argv)-1
if arguments < 6:
	print ("The script is called with %i arguments - 6 arguments are required. (DBUsername, DBpassword, DBalias, APPusername, APPpassword, OneXer)" % (arguments))
	sys.exit()
	
DBusername= sys.argv[1] 
DBpassword=sys.argv[2] 
DBalias=sys.argv[3] 
APPusername=sys.argv[4] 
APPpassword=sys.argv[5]
OneXer=sys.argv[6]

if OneXer != "0":
	OneXer = True
else:
	OneXer = False

"""
Url: https://gist.github.com/wassname/1393c4a57cfcbf03641dbc31886123b8
"""
import unicodedata
import string

valid_filename_chars = "-_.() %s%s" % (string.ascii_letters, string.digits)
char_limit = 255

def clean_filename(filename, whitelist=valid_filename_chars, replace=' '):
    # replace spaces
    for r in replace:
        filename = filename.replace(r,'_')
    
    # keep only valid ascii chars
    cleaned_filename = unicodedata.normalize('NFKD', filename).encode('ASCII', 'ignore').decode()
    
    # keep only whitelisted chars
    cleaned_filename = ''.join(c for c in cleaned_filename if c in whitelist)
    if len(cleaned_filename)>char_limit:
        print("Warning, filename truncated because it was over {}. Filenames may no longer be unique".format(char_limit))
    return cleaned_filename[:char_limit]  
"""
End of function
"""

def secure_log():
	if os.path.exists('myLog.txt'):
		with fileinput.FileInput('myLog.txt', inplace=True) as file:
			for line in file:			
				print(line.replace(APPpassword, len(APPpassword)*'X'), end='')

ospath = os.path.dirname(os.path.abspath(__file__))
pmpath = subprocess.check_output("WHERE PM.EXE", shell=True) 


def begin_process():
	secure_log()
	numtodo = 0
	proj_name_str_first = ''
	if os.path.exists("actions.xml"):
		os.rename("actions.xml", "actions" + datetime.datetime.now().strftime("%Y%m%d%H%M%S") + ".xml")	

	con = cx_Oracle.connect("{}/{}@{}".format(DBusername, DBpassword, DBalias))
	print (con.version)

	cursor = con.cursor()
	if OneXer:
		cursor.execute("""
			SELECT DISTINCT PROJ_SHORT_NAME FROM PROJECT 
			WHERE ORIG_PROJ_ID IS NULL			
			AND PROJ_SHORT_NAME NOT LIKE '%^%'
			AND PROJ_SHORT_NAME NOT LIKE '%&%'
			ORDER BY PROJ_SHORT_NAME
			""")
	else:
		cursor.execute("""
			SELECT DISTINCT PROJ_SHORT_NAME FROM PROJECT 
			WHERE ORIG_PROJ_ID IS NULL
			AND RISK_LEVEL IS NULL
			AND PROJ_SHORT_NAME NOT LIKE '%^%'
			AND PROJ_SHORT_NAME NOT LIKE '%&%'
			ORDER BY PROJ_SHORT_NAME
			""")
			
	f = open(ospath + '\\actions.xml','a')
	xml_file_begin = '<actions>\n'
	f.write(xml_file_begin)
	
	if OneXer:
		xml_file = '\t<action>\n\t\t<type>export</type>\n\t\t<Projects>'
		f.write(xml_file)
			
	for proj_name in cursor:	
		proj_name_actual = ''.join(proj_name)
		proj_name_str = clean_filename(proj_name_actual)
		print("Project:", proj_name_actual)
		if OneXer:
			xml_file = '\n\t\t\t<ProjectID>' + proj_name_actual + '</ProjectID>'
			f.write(xml_file)
		else:
			if not os.path.exists(ospath + '\\' + proj_name_str + '.xer'):
				if proj_name_str_first == '':
					proj_name_str_first = proj_name_str
				numtodo += 1
				xml_file = '\t<action>\n\t\t<type>export</type>\n\t\t<Projects>\n\t\t\t<ProjectID>' + proj_name_actual + '</ProjectID>\n\t\t</Projects>\n\t\t<outputFile>' + ospath + '\\'+ proj_name_str +'.xer</outputFile>\n\t</action>\n'	
				f.write(xml_file)	
			else:
				print("Skipped: Project "+ proj_name_actual + " already exported or errored.")
			
	if OneXer:
		xml_file = '\n\t\t</Projects>\n\t\t<outputFile>' + ospath + '\\OneFile.xer</outputFile>\n\t</action>\n'
		f.write(xml_file)	
	
	xml_file_end = '</actions>\n'
	f.write(xml_file_end)
	f.close()
	if not OneXer:
		print("Number of projects left to process:", numtodo)		
	os.system('PM.EXE /username=' + APPusername + ' /password=' + APPpassword + ' /actionScript=\"' + ospath + '\\actions.xml\" /logfile="' + ospath + '\\myLog.txt\"')
	con.close()
	secure_log()
	
	if numtodo > 0:
		if not os.path.exists(ospath + '\\' + proj_name_str_first + '.xer'):
			print("Errored on: ", proj_name_str_first)
			g = open(ospath + '\\' + proj_name_str_first + '.xer','w')
			g.write('Error!!')
			g.close()
			h = open(ospath + '\\ERROR - ' + proj_name_str_first + '.txt','w')
			h.write('Error!!')
			h.close()
		begin_process()		
		
begin_process()

This script does have some error handling such that if an error is encountered in the export process – which is almost guaranteed – that the process should begin again without trying to re-export projects that were already done or caused errors.