CJSmith dot me

I dump stuff I find useful here

Author: Chris Smith

V1 DBAuthorise grab a list of Purchase Orders

Published / by Chris Smith / Leave a Comment

V1 DBAuthorise can be setup to have multiple Purchase orders, so I use a Common Table Expression in a view for any time I need to get a list of Purchase Orders and Sage Company that is not in ~Accepted and State OK (1024).
user5 for us is the PO number and user7 the Sage Company Name, change if different on your own system.
I encountered a looping issue so use maxrecursion 10 to ensure there is nothing wrong. 10 because there may be multiple invoices per Purchase Order.

with cte (user5,user7) as
  select cast(left([user5], charindex(',',[user5]+',')-1) as varchar(10)) [user5],LEFT(user7,3) as user7
  from .[dbo].[DBAUTH]
                where stateNumber != '1024' and user5 != '' --and user7 = ''
  union all
    cast(left([user5], charindex(',',[user5]+',')-1) as varchar(10)),LEFT(user7,3) as user7
  from cte
  where RTRIM([user5]) !='' and [user5] IS NOT NULL and [user5] LIKE '%,%'
select [user5],user7
from cte
group by [user5],user7
option (maxrecursion 10)

Simple method to see what AD Users SQL Server can see in AD Groups

Published / by Chris Smith / Leave a Comment

Credit: Microsoft
StackOverflow user Saranya Kumaran

Simple method to see what AD Users SQL Server can see in AD Groups

exec xp_logininfo 'Domain/AD Group', 'all'
exec xp_logininfo 'Domain/AD Group', 'members'

Non ad group first command returns error, second command returns no output
Msg 15404, Level 16, State 10, Procedure xp_logininfo, Line 64 [Batch Start Line 0]
Could not obtain information about Windows NT group/user , error code 0xffff0002.

Run against AD user not group first command works, second does not
Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 43 [Batch Start Line 1]
Could not obtain information about Windows NT group/user , error code 0x8ac.

Powershell script to manage V1 dbadeposit issues in pull config

Published / by Chris Smith / Leave a Comment

Microsoft Docs

V1 dbadeposit can exit and stop working when in a pull configuration (Normally V1 dbadeposit is setup in a push to configuration) if a tag file (Metadata) is not produced along with the arq file and binary blob file. If you check and find you are actually missing no scans like ourselves then feel free to use. If you find you are missing scans DO NOT USE, fix the problem first. Errored entries which produce an .err file are kept as they are genuine errors that don’t cause the software to fall over, file so I always look for the tag file. The batch can actually be changed from deleting files to moving files, however for us the arq file simply contains [PCL]

rem change drive to required drive letter
$Afiles = ls *.arq
$Bfiles = ls *.tag
$Alist = @()
$Blist = @()

foreach( $A in $Afiles)
    $Alist += $A.BaseName
foreach( $B in $Bfiles)
    $Blist += $B.BaseName
foreach($A in $Alist)
    if($Blist -notcontains $a)
        rm ("$A.arq")

Maximum History Reached in Code & Post Registered Invoices

Published / by Chris Smith / Leave a Comment

Credit: Datel

Code & Post Registered Invoices has a database limit on the plreghstm table and can hold 79 rows before displaying the error Maximum History Reached (Even though the field supports 4 characters). The last row holds a ~ (Tilda) in the page number field causing the error message and subsequently the automation routine to fail. Solution is to remove the last 5 or 6 entries in the table so removing the ~001 (Tilda). Although in theory this should be very hard to do some Automation programs such as Datel Automation might not handle some boxes and therefore may fail.

Code and Post Error Message

Recode the invoices if they are incorrect and then remove the last 3 records so it can be re-attempted a few times if something is still wrong.

SELECT * FROM scheme.plreghstm where supplier = '' and item = '' and page_no IN ('|001','}001','~001') order by rowstamp DESC
--Backup table
select * into scheme.plreghstm from scheme.plreghstm
--Do not commit if more records appear to be deleted

  delete from scheme.plreghstm
    where supplier = '' and item = '' and page_no IN ('|001','}001','~001')

Microsoft Message Queuing Service – Correct Permission Issue

Published / by Chris Smith / Leave a Comment

Credit: Aggregated Intelligence
We had a request after software was installed to give permissions to a group in Windows AD.
This was failing because the only account with access was the SYSTEM account
When making any changes it would fail with 2 errors
Example of permissions tab
Example of first error
example of second error
On the permission screen click Advanced, change the location to the local machine and change the owner to the the local administrators group
File Permissions
Changing Owner
Administrators can now make changes to the affected Microsoft Message Queuing Service queue.
It is not recommended to use Everyone, use proper groups.

Sage Line 500 password policy implementation and force password reset

Published / by Chris Smith / Leave a Comment

I wrote a simple script to aid users moving to a new password policy, users were put on a policy which would expire a password after a day. Once they had changed their password the policy is amended to the new policy. I now use this for forcing password resets. Replace and with your Sage Line 500/1000 password policies

-- =============================================
-- Author:           Christopher Smith
-- Create date: 18/07/2018
-- Description:      Implement the new password policy in stages.
-- 1. Change the password policy first to enforce a password change in a short time period
-- 2. Backup and truncate the table [csmaster].[scheme].[pwdhistm]
-- 3. Run this routine nightly, it will pickup users who have changed their password and set the new policy
-- =============================================
-- Change Log:
-- 1.0 - 18/07/2018 - First Version
-- =============================================
-- exec dbo.usp_SageImplementPWPolicy
-- exec [dbo].[usp_ForceSagePWChange] 'csmithte'
-- =============================================
CREATE PROCEDURE [dbo].[usp_SageImplementPWPolicy]
-- =============================================
print 'Declare Variables and setup'
-- =============================================
  declare @spdate1 date
  declare @spdate2 int
  declare @secgroup char(3)
  declare @oldsecgroup char(4)
-- Set variable to 2 days ago
  set @spdate1 = CAST(GETDATE() as date)
  print @spdate1
-- Convert the date to one which matches the Sage table
  set @spdate2 = DATEDIFF(d,'01 jan 1900',@spdate1)
  print 'Sage Date Code for Today'
  print @spdate2
  print 'New user security group'
  set @secgroup = ''
  set @oldsecgroup = ''
  print @secgroup
  print @oldsecgroup
-- =============================================
print 'Populate working table'
-- =============================================
       select t1.name, dateadd(d,t2."setup",'01 jan 1900') as pw_date, t1.sec_group, 'NO ' as pw_changed into #pwworking
              from scheme.[usermastm](nolock) t1
                     INNER JOIN scheme.passexpm(nolock) t2
                           on t1.name = t2.user_id
              where t1.sec_group = @oldsecgroup
       select * from #pwworking
-- =============================================
print 'Check if password recently changed'
-- =============================================
       update #pwworking
              set pw_changed = 'YES'
                     where name IN
                           (select "username" from [csmaster].[scheme].[pwdhistm] group by "username")
       select * from #pwworking
-- =============================================
print 'Change security group for those who have a new password on the new policy'
-- =============================================
       update scheme.[usermastm]
              set sec_group = @secgroup
                     where name IN (select name from #pwworking where pw_changed = 'YES')
drop table #pwworking

Below is the code used to force users to enter a new password upon login

USE [csmaster]
CREATE procedure [dbo].[usp_ForceSagePWChange] @user char(8)
--EXEC usp_ForceSagePWChange ''
update scheme.passexpm
set setup = '10001'

update csmaster.scheme.passexpm
set setup = '10001'
where user_id = @user
update csmaster.[scheme].[usermastm]
set [sec_group] = ''
where name = @user

Test Pop3

Published / by Chris Smith / Leave a Comment

Pop3 can be tested via Command line using the telnet executable (Will need to be selected from Windows features).
Telnet to the Pop3 server in question, this will test Pop3 is accessible, then enter the username and password. As long as the status is OK then it should work. If not the error should provide more information such as invalid username or password.

telnet pop.yourdomain.co.uk 110
user your-emailusername
--Returns Status, should be something like +OK--
pass youremailpassword
--Returns Status, should be something like +OK--

My Windows 10 Desktop Settings

Published / by Chris Smith / Leave a Comment

I hate the default Windows settings, I normally run the following to customise the profile to my liking (I don’t like programs grouped and like to see seconds on the clock, I like to show all folders in Explorer, launch Explorer to This PC not Quick Access)

Set-ItemProperty -Path HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name LaunchTo -Value 1
Set-ItemProperty -Path HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name TaskbarGlomLevel -Value 2
Set-ItemProperty -Path HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name ShowSecondsInSystemClock -Value 1
Set-ItemProperty -Path HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name NavPaneShowAllFolders -Value 1
Set-ItemProperty -Path HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name ShowSyncProviderNotifications  -Value 1
Set-ItemProperty -Path HKLM:\Software\Microsoft\Windows\CurrentVersion\Explorer -Name HubMode -Value 1
Get-Process -Name explorer | Stop-Process

Remove document protection from PDF files for ABBYY FineReader Engine 10 SDK

Published / by Chris Smith / Leave a Comment

We came across an issue where PDF’s with any sort of protection failed to process via ABBYY FineReader Engine 10 SDK. The PDF’s were placed in a network share and then failed to process.
To get around this I implemented a program called Foldermill (Which has a trial to test it with and a very reasonable price for the main product).
The first thing we did was to reprocess the file into a Tiff file, set the DPI and change to Monochrome (As we don’t require colour).
A second job is then setup to convert it back to a PDF file with the PDF/A format. This would pickup files from the first job and then place them into the original share for ABBYY FineReader Engine 10 SDK to then process (Reducing DPI and setting to monochrome means it should be less likely to fail on complex scans).
Both jobs were amended to 60 seconds as we found 1 second would cause failures for those with very slow internet connection when working from home.
We then simply added a new share and removed the old share.

Preview of Foldermill Settings