CJSmith dot me

I dump stuff I find useful here

Author: Chris Smith

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

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

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]
AS
BEGIN
       SET NOCOUNT ON;
-- =============================================
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
END
GO

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

USE [csmaster]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_ForceSagePWChange] @user char(8)
--EXEC usp_ForceSagePWChange ''
AS
--IF @user = 'ALL' GOTO ALLUSERS
--IF @user != 'ALL' GOTO SINGLEUSER
BEGIN
/*ALLUSERS:
update scheme.passexpm
set setup = '10001'
GOTO FINISH
SINGLEUSER:*/

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

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)

;https://ss64.com/nt/syntax-reghacks.html
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

Remove invalid Datalinx WHM Report Records

Published / by Chris Smith / Leave a Comment

Where printers have been removed can cause issues with the user report program where you can control where users print to.
The code below simply looks at the printer table and compares with the report control table.
BEGIN TRAN
--Check number of records which have invalid printers
SELECT *
FROM "scheme"."wh_rctl"
WHERE NOT EXISTS
(SELECT *
FROM "scheme"."wh_pctl"
WHERE "scheme"."wh_pctl"."dp_printer" = "scheme"."wh_rctl"."dr_printer")
--THIS WILL DELETE RECORDS BE CAREFUL!
DELETE
FROM "scheme"."wh_rctl"
WHERE NOT EXISTS
(SELECT *
FROM "scheme"."wh_pctl"
WHERE "scheme"."wh_pctl"."dp_printer" = "scheme"."wh_rctl"."dr_printer")
--Check records no longer exist then commit tran
SELECT *
FROM "scheme"."wh_rctl"
WHERE NOT EXISTS
(SELECT *
FROM "scheme"."wh_pctl"
WHERE "scheme"."wh_pctl"."dp_printer" = "scheme"."wh_rctl"."dr_printer")
--Just in case
COMMIT TRAN

Setup MS SQL Server Alerts via TSQL

Published / by Chris Smith / Leave a Comment

Amend TSQL to your environment, run the TSQL, restart the agent service and test (I find it easy to enter an incorrect username and password).

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,                              @databasemail_profile=N'<TSQL_MAIL_PROFILE>',                              @use_databasemail=1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Login Mismatch',                               @message_id=18456,                               @severity=0,                               @enabled=1,                               @delay_between_responses=0,                               @include_event_description_in=1,                              @job_id=N'00000000-0000-0000-0000-000000000000'GO

USE [msdb]
GO 
/****** Object:  Alert [Login failed : State 5]    Script Date: 24/03/2020 12:50:49 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Login failed : State 5',                               @message_id=0,                               @severity=0,                               @enabled=1,                               @delay_between_responses=0,                               @include_event_description_in=5,                              @category_name=N'[Uncategorized]',                              @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',                              @wmi_query=N'SELECT * FROM AUDIT_LOGIN_FAILED WHERE State = 1 OR State=  5 OR State =  8 OR State= 14 OR State=  38',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object:  Alert [Error 823]    Script Date: 24/03/2020 12:51:12 ******/EXEC msdb.dbo.sp_add_alert @name=N'Error 823',                               @message_id=823,                               @severity=0,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Error 824]    Script Date: 24/03/2020 12:51:33 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error 824',                               @message_id=824,                               @severity=0,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Error 825]    Script Date: 24/03/2020 12:51:51 ******/EXEC msdb.dbo.sp_add_alert @name=N'Error 825',                               @message_id=825,                               @severity=0,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 016]    Script Date: 24/03/2020 12:52:05 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',                               @message_id=0,                               @severity=16,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Severity 017]    Script Date: 24/03/2020 12:52:14 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',                               @message_id=0,                               @severity=17,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 018]    Script Date: 24/03/2020 12:52:22 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',                               @message_id=0,                               @severity=18,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 019]    Script Date: 24/03/2020 12:52:34 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',                               @message_id=0,                               @severity=19,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Severity 020]    Script Date: 24/03/2020 12:52:44 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',                               @message_id=0,                               @severity=20,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 021]    Script Date: 24/03/2020 12:52:52 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',                               @message_id=0,                               @severity=21,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO   USE [msdb]GO /****** Object:  Alert [Severity 022]    Script Date: 24/03/2020 12:53:02 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',                               @message_id=0,                               @severity=22,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Severity 023]    Script Date: 24/03/2020 12:53:23 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',                               @message_id=0,                               @severity=23,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 024]    Script Date: 24/03/2020 12:53:32 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',                               @message_id=0,                               @severity=24,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 025]    Script Date: 24/03/2020 12:53:52 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',                               @message_id=0,                               @severity=25,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO