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;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE;
GO |
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE;
GO
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:
12.0
5
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 "" |
12.0
5
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:
10.0
1
1 SQLIMAGE 0 0 "" 1 PDF "" |
10.0
1
1 SQLIMAGE 0 0 "" 1 PDF ""
Some things to note here:
- 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
- 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
GO
--First Declaration of the Employee ID to use
DECLARE @EmployeeID VARCHAR(20) = '1234';
SELECT
ROW_NUMBER() OVER(ORDER BY [CHECK]) AS rownum
,p.PDF
,p.employeeid
,CONVERT(CHAR(10),[PERIODENDDATE],126) AS PeriodEnd
INTO tmp_for_export
FROM payrollcheck p
WHERE employeeid = @EmployeeID
GO
DECLARE @EmployeeID VARCHAR(20) = '1234';
DECLARE @cnt INT
DECLARE @i INT
DECLARE @filename VARCHAR(512)
DECLARE @extension VARCHAR(20)
DECLARE @SQL VARCHAR(4000)
SET @cnt = (SELECT COUNT(*) FROM Payrollcheck WHERE EmployeeID = @EmployeeID)
SET @i = 1
SET @extension = '.pdf'
while @i <= @cnt
BEGIN
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
end
GO |
IF OBJECT_ID('dbo.tmp_for_export', 'U') IS NOT NULL
DROP TABLE tmp_for_export
GO
--First Declaration of the Employee ID to use
Declare @EmployeeID varchar(20) = '1234';
select
row_number() over(order by [CHECK]) as rownum
,p.PDF
,p.employeeid
,convert(char(10),[PERIODENDDATE],126) as PeriodEnd
into tmp_for_export
from payrollcheck p
where employeeid = @EmployeeID
GO
Declare @EmployeeID varchar(20) = '1234';
Declare @cnt int
declare @i int
declare @filename varchar(512)
declare @extension varchar(20)
declare @sql varchar(4000)
set @cnt = (select count(*) from Payrollcheck where EmployeeID = @EmployeeID)
set @i = 1
set @extension = '.pdf'
while @i <= @cnt
begin
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
end
GO
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.
