CJSmith dot me

I dump stuff I find useful here

Category: Windows

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

Tidy Sage Line 500 ERP Temporary Tables via TSQL

Published / by Chris Smith / Leave a Comment

The official Sage Help has a select statement to delete temporary tables, but doesn’t allow an automated way to remove.
This script will delete tables so you must take extra care, I recommend trialing this on your Development Sage ERP system first.
Review every step and when you are happy Commit the transactions (If run all in one go it will deliberatly Rollback)

Credits:
Chef Slagle
Sage Help

/*
This code is based on the Sage example to remove temporary tables.  Normally these are left when a binary has not exited in the normal way.
You must check to ensure the correct tables are selected as the query won't care if it picks up a user table.
Credits:
Chef Slagle - https://stackoverflow.com/questions/17901934/how-to-execute-sql-statements-saved-in-a-table-with-t-sql
Sage - https://ask.sage.co.uk/scripts/ask.cfg/php.exe/enduser/std_adp.php?p_faqid=25606&p_pv=1.790
*/
PRINT 'Warnings: THIS WILL DESTROY DATA, ENSURE NO USER DATA TABLES ARE IN THE FORMAT IN THE SELECT QUERY!!'
PRINT '--==YOU MUST ENSURE YOU HAVE A FULL BACKUP OF ALL TABLES AND YOUR RESTORE IS WORKING BEFORE RUNNING ANYTHING FROM THIS EXAMPLE!==--'
PRINT '--==RECOMMENDED TO BE DONE WHEN NO USERS ARE ON THE SYSTEM==--'
PRINT '--==TEST ALL SAGE MODULES AFTER THIS IS COMPLETE!!==--'
 
print 'Find out how many temp tables are on the system over 30 days old'
SELECT count(*) FROM sysobjects WHERE type = 'U' AND
(name LIKE 'pq[0-9]%t' OR name LIKE '[_]s[_][0-9a-z]%t' OR name LIKE '[_]p[_][a-z0-9]%t' OR name LIKE 'cbm[0-9]%t' OR name LIKE 'cbt[0-9]%t' OR name LIKE 'cibr[0-9]%t' OR
       name LIKE 'er[0-9]%t' OR name LIKE 'nla[0-9]%t' OR name LIKE 'nld[0-9]%t' OR name LIKE 'nle[0-9]%t' OR name LIKE 'nlh[0-9]%t' OR name LIKE 'nlr[0-9]%t' OR
       name LIKE 'nlsp[0-9]%t' OR name LIKE 'opi[0-9]%t' OR name LIKE 'plt[0-9]%t' OR name LIKE 'po[0-9]%at' OR name LIKE 'por[0-9]%t' OR name LIKE 'slt[0-9]%t' OR
       name LIKE 'tld[0-9]%t' OR name LIKE 'tlh[0-9]%t' OR name LIKE 'st[0-9]%t' OR name LIKE 'bu[0-9]%t' OR name LIKE 'cbx[0-9]%t' OR name LIKE 'mr[0-9]%t' OR
       name LIKE 'poi[0-9]%t')AND crdate < GETDATE() -30
 
print 'REVIEW THE TABLES WHICH WILL BE DELETED BY THE CODE BELOW!!  ENSURE NO USER TABLES PRESENT!!  DO NOT PROCEED IF USER TABLES PRESENT!!'
SELECT "name" FROM sysobjects WHERE type = 'U' AND
(name LIKE 'pq[0-9]%t' OR name LIKE '[_]s[_][0-9a-z]%t' OR name LIKE '[_]p[_][a-z0-9]%t' OR name LIKE 'cbm[0-9]%t' OR name LIKE 'cbt[0-9]%t' OR name LIKE 'cibr[0-9]%t' OR
       name LIKE 'er[0-9]%t' OR name LIKE 'nla[0-9]%t' OR name LIKE 'nld[0-9]%t' OR name LIKE 'nle[0-9]%t' OR name LIKE 'nlh[0-9]%t' OR name LIKE 'nlr[0-9]%t' OR
       name LIKE 'nlsp[0-9]%t' OR name LIKE 'opi[0-9]%t' OR name LIKE 'plt[0-9]%t' OR name LIKE 'po[0-9]%at' OR name LIKE 'por[0-9]%t' OR name LIKE 'slt[0-9]%t' OR
       name LIKE 'tld[0-9]%t' OR name LIKE 'tlh[0-9]%t' OR name LIKE 'st[0-9]%t' OR name LIKE 'bu[0-9]%t' OR name LIKE 'cbx[0-9]%t' OR name LIKE 'mr[0-9]%t' OR
       name LIKE 'poi[0-9]%t')AND crdate < GETDATE() -30
--Note the count above here and review the list of tables from the previous select statement.
print 'Run next step manually in BEGIN TRAN, job will Rollback just in case.  Only commit if number of tables deleted matches above!'
BEGIN TRAN
 
DECLARE @Table table (RID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
                        SQLText NVARCHAR(MAX) )
 
DECLARE  @StatementMax INT 
        ,@statementMin INT
        ,@isTest TINYINT = 1
        ,@SQLStatement NVARCHAR(MAX)
 
INSERT INTO @Table (SQLText)
(SELECT 'drop table scheme.' + "name" as SQLText FROM sysobjects WHERE type = 'U' AND
(name LIKE 'pq[0-9]%t' OR name LIKE '[_]s[_][0-9a-z]%t' OR name LIKE '[_]p[_][a-z0-9]%t' OR name LIKE 'cbm[0-9]%t' OR name LIKE 'cbt[0-9]%t' OR name LIKE 'cibr[0-9]%t' OR
       name LIKE 'er[0-9]%t' OR name LIKE 'nla[0-9]%t' OR name LIKE 'nld[0-9]%t' OR name LIKE 'nle[0-9]%t' OR name LIKE 'nlh[0-9]%t' OR name LIKE 'nlr[0-9]%t' OR
       name LIKE 'nlsp[0-9]%t' OR name LIKE 'opi[0-9]%t' OR name LIKE 'plt[0-9]%t' OR name LIKE 'po[0-9]%at' OR name LIKE 'por[0-9]%t' OR name LIKE 'slt[0-9]%t' OR
       name LIKE 'tld[0-9]%t' OR name LIKE 'tlh[0-9]%t' OR name LIKE 'st[0-9]%t' OR name LIKE 'bu[0-9]%t' OR name LIKE 'cbx[0-9]%t' OR name LIKE 'mr[0-9]%t' OR
       name LIKE 'poi[0-9]%t')AND crdate < GETDATE() -30)
--select * from @Table
SELECT @StatementMax = MAX(RID), @statementMin = MIN(RID)  FROM @Table
--IF @isTest = 1 BEGIN SELECT *, @StatementMax AS MaxVal, @statementMin AS MinVal FROM @Table END
-- Start the Loop
WHILE @StatementMax >= @statementMin
BEGIN
    SELECT @SQLStatement = SQLText FROM @Table WHERE RID = @statementMin        -- Get the SQL from the table 
    --IF @isTest = 1 BEGIN SELECT 'I am executing: ' + @SQLStatement AS theSqlBeingRun, GETDATE(), @statementMin, @StatementMax END  
    --ELSE 
    --BEGIN 
        EXECUTE sp_executesql @SQLStatement                 -- Execute the SQL 
    --END
              SELECT @statementMin = @statementMin + 1
        --DELETE FROM @Table WHERE RID = @statementMin        -- Delete the statement just run from the table
        --SELECT @statementMin = MIN(RID)  FROM @Table        -- Update to the next RID
    --IF @isTest = 1 BEGIN  SELECT * FROM @Table END
END
print 'Check number of executions matches expected value, check temp tables have been removed and if ok run COMMIT TRAN'
ROLLBACK TRAN
COMMIT TRAN

Avoid error “2427 You entered an expression that has no value” when passing a parameter to a form

Published / by Chris Smith / Leave a Comment

To avoid getting an error when passing a variable to a form, using If Not against the EOF (I believe means End of File) property.

If Not Me.Recordset.EOF And Not Me.Recordset.EOF Then
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "someform"
    stLinkCriteria = "[recordnumber]=" & Me.[recordnumber]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
    MsgBox "No Record Selected!"
End If

Arcsoft Totalmedia Extreme causing issues upgrading to Windows 10 Version 1909

Published / by Chris Smith / Leave a Comment

I recently had fun with being unable to upgrade to version 1909 for Window 10 failing with a BSOD and an error which pointed to a memory issue.
After using Nirsoft’s BSOD ViewNirsoft’s BSOD View it pointed to ArcSec.sys.
I uninstalled Arcsoft Totalmedia Extreme. however the uninstaller left the drivers installed. I then used SysInternals AutorunsSysInternals Autoruns to stop the drivers where I also removed a further driver ArcSec.sys and arc.sys.
This however stopped the CD Rom from showing and in device manager stated “Windows cannot start this hardware device because its configuration information (in the registry) is incomplete or damaged. (Code 19)”. Uninstalling and reinstalling didn’t change a thing.
As I didn’t want the ArcSoft driver to be used (As the software is uninstalled what’s the point of there being a driver).
Using Method 5 from Method 5 from Microsoft’s Windows Support article “Your CD or DVD drive is not recognized by Windows or other programs” I found the offending driver specified in LowerFilters. Once I removed arc.sys from this entry removing and reinstalling the driver lead to the drive coming back straight away.