Sage 300 PayStub Self-Service PDF export

In April we deployed an add-on for our Sage 300 payroll software, which provides a self-service web interface for employees to access their pay stubs.

We recently discovered that when an employee is set to Inactive or terminated within Sage 300, their access to the portal stops working, which is a good thing. However, in our case this employee had simply transferred to the parent company, and still required access.

The self-service module is self-contained code which I can’t modify, so instead I sought out a method of extracting the PDF paystub from the database for a particular user.

Most of the information below was derived from this StackOverflow question and answer.

First, configure your SQL server to allow advanced command line commands:

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell',1  

Next, you need to export a format file for the specific table we’re looking for. I used this command from my own client computer which has SQL Management Studio installed:

bcp dbo.PAYROLLCHECK format nul -S servername -T -d COMDat -n -f formatfile.fmt

This produced a file which looked like the following:

1       SQLCHAR             0       12      ""   1     EMPLOYEEID                     SQL_Latin1_General_CP1_CI_AS
2       SQLDATETIME         0       8       ""   2     PERIODENDDATE                  ""
3       SQLINT              0       4       ""   3     ENTRYSEQ                       ""
4       SQLIMAGE            4       0       ""   4     PDF                            ""
5       SQLDECIMAL          1       19      ""   5     CHECK                          ""

I trimmed this file down so that it looked exactly like this:

1       SQLIMAGE            0       0       ""   1     PDF                            ""

Some things to note here:

  1. The first row needs to match the SQL native client version that your SQL server is running; it was 12.0 when I ran it on my Windows 10 machine with SQL Server 2012 Management Studio, but my actual server is running SQL Server 2008 R2. Thus, I changed the digit to 10.0
  2. The column with “PDF” needs to match the column name that contains your file data. Some examples I found on the Internet weren’t clear here

The last step was to build the query to do the export. It is important to note that in this query the BCD command runs from the server, so any local paths where you’re going to store the format file or output PDFs must be accessible on the SQL Server itself.

This query fills a temporary table with all rows matching a specific employee ID.
Then the while loop iterates through each of those rows, and exports the PDF, naming it based on the Period End date as an increment value.

I declare the employee ID we’re looking for twice in between each ‘GO’ statement, because I’m lazy and didn’t look for a better way.

IF OBJECT_ID('dbo.tmp_for_export', 'U') IS NOT NULL
 DROP TABLE tmp_for_export
 --First Declaration of the Employee ID to use
 DECLARE @EmployeeID VARCHAR(20) = '1234';
 INTO tmp_for_export
 FROM payrollcheck p
 WHERE employeeid = @EmployeeID
DECLARE @EmployeeID VARCHAR(20) = '1234';
 DECLARE @filename VARCHAR(512)
 DECLARE @extension VARCHAR(20)
 SET @cnt = (SELECT COUNT(*) FROM Payrollcheck WHERE EmployeeID = @EmployeeID)
 SET @i = 1
 SET @extension = '.pdf'
while @i <= @cnt
 SET @filename = (SELECT PeriodEnd FROM tmp_for_export WHERE rownum = @i)
 SET @SQL = 'BCP "SELECT PDF from dbo.tmp_for_export WHERE rownum = '+str(@i)+'" QUERYOUT C:\Temp\'+@EmployeeID+'-'+@filename+@extension+' -f C:\Temp\formatfile.fmt -S servername -d COMDAT -T '
 EXEC master.dbo.xp_CmdShell @sql
 set @i = @i+1


This resulted in a bunch of PDFs in C:\Temp on my SQL Server, which I could then go and grab, and give to the payroll department.


Replace Network Printers

I recently went through a Print Server replacement, and wanted to simplify the process for my staff. Our printer connections are quite variable, so we do not push them out through Group Policy but instead let staff connect to printers they have permission for on an as-needed basis.

I wanted a method of re-assigning connections from the old print server to the new one, and I found such a method here:

Chris (above) in turn had sourced his script from Boe here and added a line for retaining the default printer.


However, I ran into a problem with Chris’ script where it would keep reporting and error that the SetDefaultPrinter vbscript couldn’t find a printer with the specified name.

A few quick tests showed that the result produced in the $default variable can’t be referenced by ShareName, but must rather use the “Name” of the printer to set the default.

Thus, I modified the last line to look like this:

(New-Object -ComObject WScript.Network).SetDefaultPrinter("\\$($newPrintServer)\" + $($default.ShareName))

I see now that the second comment on Chris’ post by Andy produces this same result.

Azure Application Proxy permission requirement

My current role has recently expanded to include interaction with Azure, and in this process I came across a permission issue in being able to fully manage Azure AD Application Proxy components.

My administrative account was granted “Co-Administrator” permissions across the Azure subscription by our global team, which was thought to be all that is required to fully managed the components I needed access to.

However, in attempting to set up a new Azure AD Application Proxy, I found that some things were out of place.

Using the Classic portal, I navigated to the Azure AD application list, and clicked Add. Based on documentation and other’s experience, I expected to see a window posing the “What do you want to do?” question. Instead, I was only give the very next screen for a standard application, “Tell us about your application”:


In troubleshooting this issue, I eventually found that I could not use the new portal ( to view Enterprise Applications, with an error of “You don’t have access to this data. Please contact your global administrator to get access.”

A bit further research eventually led me to this Azure article regarding user roles within AD:

This seemed to be the root of the problem, as when I viewed my administrative user account within Azure AD, it’s Directory Role was listed as a Limited Administrator, with “User Account Administrator” as the granted role:


My global team Changed this to “Global Administrator”, which has opened up the Enterprise Applications blade to me and allowed me to configure Application Proxy. I’m not sure which individual role would also grant the same privileges; perhaps none of them.

Commvault Network Throttle

I’ve been struggling for a while trying to get my configuration set properly to avoid WAN saturation with Commvault. There are two key areas I’ve been focusing on:

  1. Aux Copies between media agents
  2. Client to Media Agent backups

I wasn’t fully thinking about the conflict between these two goals which was the root of my problem.

MediaAgent_1 and MediaAgent_2 are geographically separate across the WAN, with a 30Mbps effective connectivity.

Client_1 and Client_2 are geographically separate, and back up to MediaAgent_1. Each has a 6Mbps link.

In my previous configuration, I applied absolute Send/Receive throttling on the Media Agents to accomodate the Aux Copy, at 20Mbps limit, and similar throttling on the Client configuration at 3Mbps.

As much as it doesn’t make sense, I believe what was causing the problem is that the MediaAgent was set to receive at 20Mbps which overrode the “send” setting on the Client.

So here’s what I have successfully configured now:

MediaAgent_1: Select MediaAgent_2 as “remote client”, with absolute SEND throttling of 20Mbps

MediaAgent_2: Select MediaAgent_1 as “remote client”, with absolute SEND throttling of 20Mbps

Client_1: Select MediaAgent_1 as “remote client”, with absolute SEND throttling of 3Mbps

Client_2: Select MediaAgent_1 as “remote client”, with absolute SEND throttling of 3Mbps

Commvault Network Throttle settings

Now running backups during the day isn’t causing extreme saturation of my WAN links!

DWG icon in Windows Explorer missing

I finally resolved a long-standing issue for one user that was minor but quite annoying.

At some point the icon within Windows Explorer for DWG files stopped appearing; instead it gave the odd image when an icon is assigned but not found.

There had been many attempts to reset this using the “Open With” dialog, and the File Associations within Windows, to no avail.

Next I turned to the FileTypesMan program to see if I could reset the association. I could see that the .dwg file type was associated with AutoCAD.Drawing.20 and had an appropriate icon assigned, however the UserChoice setting was stuck on AutoCAD.Drawing.19 and whenever I tried to change this, it would provide an “Access is denied” error.

Eventually I discovered the UserChoice setting is stored in the registry here:


The permissions on that key had a special entry to Deny access for my user account; when removed I was able to use the FileTypesMan to re-associate the UserChoice with AutoCAD.Drawing.20 and the icon re-appeared.