Posted on

Documenting Filenames in the Source Code with Python

Python logo

Troubleshooting a web application page that is made from a crazy quilt of source code files is not always easy. Whether your application is built using ASP, PHP, ColdFusion, or something completely different, the challenge here is the same. How do you find the line of code in the source that is causing the problem if you don’t know which file to look in?

If you’re lucky, and a particular piece of error handling text or other identifying information is only found in a couple of places, using a PowerShell script like the one below will find what you’re looking for.

Get-ChildItem -Path "C:\Root folder" -Recurse | Select-String -Pattern "text to search for" | group path | select name

However, if the error text itself is not embedded in the code, but you can find the offending line of code in the rendered web page using Chrome Dev Tools or the like, you may still have a problem locating the actual line of code in source. That’s where this technique comes in. Putting the source code filenames in comments at the beginning and end of each appropriate file can make it easier to determine the origin of the problem. 

I’ve visited this topic before, and used PowerShell to do this task then. Now, I’m using Python. I was able to cobble this script together from one that traverses folders and documents their contents and one that filters the file type since, for this exercise, I only need to alter the ColdFusion (.cfm) files. 

import os
import sys

walk_dir = sys.argv[1]

print('walk_dir = ' + walk_dir)

# If your current working directory may change during script execution, it's recommended to
# immediately convert program arguments to an absolute path. Then the variable root below will
# be an absolute path as well. Example:
# walk_dir = os.path.abspath(walk_dir)
print('walk_dir (absolute) = ' + os.path.abspath(walk_dir))

for root, subdirs, files in os.walk(walk_dir):
    files = [ fi for fi in files if fi.endswith(".cfm") ]    

    for filename in files:
        file_path = os.path.join(root, filename)
        adj_file_path = file_path.replace(walk_dir,'').replace('\\','/')
        print('\t- file %s (full path: %s)' % (filename, adj_file_path))
        
        with open(file_path, 'rb') as f:
            f_content = f.read().decode("utf-8")
            f_content = '<!-- file: ' + adj_file_path + ' -->\n' + f_content + '\n<!-- end of file: ' + adj_file_path + ' -->\n'                       
            
        with open(file_path, 'wb') as f:
            f.write(f_content.encode("utf-8"))
Posted on

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!