CJSmith dot me

I dump stuff I find useful here

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 […]

Powershell issue with output characters changing to ?

Published / by Chris Smith / Leave a Comment

On PowerShell command pipe to export CSV add Encoding Unicode if characters change to ? Credit: Idera execute xp_cmdshell ‘powershell -Command “Get-ChildItem -File -Path @path -filter *.* -Recurse | ForEach-Object {[PsCustomObject]@{Fullname = “$($_.FullName)”; LastWriteTime = “$($_.LastWriteTime)”}} | Sort Fullname -Descending | Export-Csv -Path @CSVFile -NoTypeInformation -Encoding unicode”‘ If using BULK INSERT ensure widechar is used […]

Windows 2008 – Alert on Scheduled Job Failure

Published / by Chris Smith / Leave a Comment

First we need to enable logging for the Windows Task Scheduler, open Event Viewer (Put “eventvwr.msc” in Run) and browse to TaskScheduler as below. Right click the Operational log and click properties and ensure the “Enable Logging” is checked Windows is now setup to log Scheduled tasks to the Event Viewer, now we need to […]

Mail on Login failure to a Microsoft SQL Server

Published / by Chris Smith / Leave a Comment

Ensure a mail profile is setup and make a note of its name. Run the following in Microsoft Query Analyser replacing the mail profile and email address. USE [msdb] GO /* Credit https://sqljunkieshare.com/2016/08/01/variable-wmi-not-found/ for issue with Variable WMI Not Found on newer versions of SQL Server */ EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1 GO USE [msdb] […]

Sage Line 500 V7.1 CSAgent TCR and Form appear to start but ports never open

Published / by Chris Smith / Leave a Comment

Recently we had this issue where after windows patches were applied, after a reboot the Sage Line 500 v7.1 Windows services no longer worked on our development and live servers. It was due to the Visual Studio dll msvcr71.dll being removed SYSWOW64 (32 Bit systems will be System32 and you can see in process monitor […]

Populate Sage table opheadm with credit note information for Cindercone Magma to send to Tungsten/OB10

Published / by Chris Smith / Leave a Comment

The below stored proc is used to populate two Sage fields shipper_code2 and shipper_code3 in the Order Header Table. Obiously if this is used already by another Sage Project do not use, as the fields won’t be updated. Here I look 30 days backwards and at particular invoice customers (There is no point updating every […]

Use a case to make sure provided data is a date

Published / by Chris Smith / Leave a Comment

I find this useful for using validating spreadsheet imports, using ISDATE can just blank the field if crap data is provided –Use Case to make sure a data field is a date –Will Work and produce the inserted field select CASE WHEN ISDATE(t1.datefield) =1 THEN t1.datefield ELSE ” END as datefield_full from (select ’14/12/2054′ as […]

In VBA strip out characters which cause TSQL to fail

Published / by Chris Smith / Leave a Comment

A simple way to strip out characters which can cause TSQL Code to break. Assumes the SQL commannd is in sqlstatement and you are passing a variable in me.comment, before executing the code. ‘Allows Characters such as , and ‘ which may break TSQL Code and Cause Injection sqlstatement = RTrim(sqlstatement) + “,’Me.comment & “‘” […]