CJSmith dot me

I dump stuff I find useful here

Author: Chris Smith

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.

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 (It will work but TSQL will add notices which may add pointless text to log files)

BULK INSERT #bcp_file_mem
                FROM @CSVFile
                                WITH (FORMAT = 'CSV', DATAFILETYPE = 'widechar', FIELDQUOTE = '"', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK);

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 setup a scheduled task.
Open Task Scheduler and Create a “New Basic Task”

Create a task with a relevant name, ensure the task is run as the “SYSTEM” user and Run with highest privileges ticked.

Create two triggers as below with Event ID’s 329 and 111

Create an action to send an email as below using an smtp server which whitelists this server and only allows internal emails

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]
GO
 
/****** Object:  Job [Mail on login failed: State 5]    Script Date: 29/07/2019 10:40:56 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 29/07/2019 10:40:56 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Mail on login failed: State 5', 
              @enabled=1, 
              @notify_level_eventlog=2, 
              @notify_level_email=0, 
              @notify_level_netsend=0, 
              @notify_level_page=0, 
              @delete_level=0, 
              @description=N'Send e-mail on WMI event', 
              @category_name=N'[Uncategorized (Local)]', 
              @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step 1 - send e-mail]    Script Date: 29/07/2019 10:40:56 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1 - send e-mail', 
              @step_id=1, 
              @cmdexec_success_code=0, 
              @on_success_action=1, 
              @on_success_step_id=0, 
              @on_fail_action=2, 
              @on_fail_step_id=0, 
              @retry_attempts=0, 
              @retry_interval=0, 
              @os_run_priority=0, @subsystem=N'TSQL', 
              @command=N'DECLARE @msg NVARCHAR(MAX) = ''From job: Login failed for $(ESCAPE_SQUOTE(WMI(LoginName))). Full error message follows:
$(ESCAPE_SQUOTE(WMI(TextData)))''; 
EXEC msdb.dbo.sp_send_dbmail  
              @recipients = ''<email address>'',  
              @profile_name = ''<mail profile>'',  
              @body = @msg,  
              @subject = ''There was a login failed event on $(ESCAPE_SQUOTE(A-SVR)).'';', 
              @database_name=N'msdb', 
              @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

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 where the processes try to access the dll file and are unable to find the dll)

This can be copied back from a client Sage Line 500 install, as I found the dll was actually still registered

One possible cause I can think of is the registry entry for the dll in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs is set to 1, an installer upgrades a program to a newer version of Visual Studio and takes one away from the dll entry in the registry, where by it thinks it is no longer used and deletes the dll.

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 invoice entry), for an initial run you may want to increase the amount of days on both update queries

USE <Sage_Company_DB>
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:           Christopher Smith
-- Create date: 01/11/2017
-- Description:      Populate opheadm fields shipper_code2 and shipper_code3 with related invoice information.
-- Magma EDI then uses this information to provide Tungsten with required information.
-- =============================================
-- Changes
-- 1.0 - 01/11/2017 - Initial Version
-- 1.0.1 - 08/11/2017 - Improve behaviour when encountering locked records
-- =============================================
-- exec dbo.usp_tungsten_credit_notes
-- =============================================
CREATE PROCEDURE [dbo].[usp_tungsten_credit_notes] 
AS
BEGIN
--Whole tran will rollback if timeout occurs
print 'Set Lock Timeout to 10 Seconds'
SET LOCK_TIMEOUT 1000;  
--SELECT @@LOCK_TIMEOUT AS [Lock Timeout];
BEGIN TRAN
print 'work out invoice number number Credit Note Order Number'
update scheme.opheadm
       set shipper_code2 = 'OP/' + SUBSTRING(order_no,3,7)
       where invoice_customer IN ('<Sage_Invoice_Customers>') and order_no LIKE 'CNI%'
              and invoice_no LIKE 'OP/C%' and invoice_date > GETDATE() -30  and shipper_code2 = ''
print 'Use the new invoice number to pull across the invoice date'
update t1
	set shipper_code3 = replace(convert(varchar, t2.invoice_date,103),'/','')
       FROM scheme.opheadm t1
              INNER JOIN scheme.opheadm t2
                     ON t1.shipper_code2 = t2.invoice_no and t1.customer = t2.customer
       where t1.invoice_customer IN ('<Sage_Invoice_Customers>') and t1.order_no LIKE 'CNI%' and t1.invoice_no LIKE 'OP/C%' and t1.invoice_date > GETDATE() -30 and t1.shipper_code3 = ''
COMMIT TRAN
END
GO

Force TSQL dateformat

Published / by Chris Smith / Leave a Comment

If you get the following error on scheduled stored procedures, force the dateformat (The Microsoft SQL Server scheduler will probably be running the default mdy), you can use Set Language but I tend to change the dateformat.
[SQLSTATE 01000] (Message 0) The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. [SQLSTATE 22007] (Error 242) The statement has been terminated.

As I am in the UK we tend to use dmy

print 'Force dateformat to dmy'
set dateformat 'dmy'

Microsoft Docs

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 datefield) t1
--Won't Work and will produce Blank
select CASE WHEN ISDATE(t1.datefield) =1 THEN t1.datefield ELSE '' END  as datefield_full
from (select '14/122054' as datefield) t1

Check the audit on Business Objects XIR3 SP7 for logons

Published / by Chris Smith / Leave a Comment

This is useful to see who is using their licenses which are very expensive. I tend to check every few months and look at 90 days.
Its a simple count excluding the System Account and look at a date range.
I have only run this against XIR3 SP7 where auditing has been enabled on an environment running Windows and Microsoft SQL Server (Obviously it can run on almost most OS and database configs).

USE <XIR3AuditDatabase>
SELECT MAX(AUDIT_EVENT.User_Name) as "User_Name", COUNT(*) as Logins
FROM
  AUDIT_EVENT
WHERE
   AUDIT_EVENT.User_Name  <>  'System Account'   AND
   AUDIT_EVENT.Event_Type_ID  IN  ( 65537, 65538  )   AND
   AUDIT_EVENT.Start_Timestamp  BETWEEN  GETDATE() -90  AND GETDATE()
  GROUP BY AUDIT_EVENT.User_Name
  order by AUDIT_EVENT.User_Name

I have no idea where Business Objects stores the user list, so unfortunatly I can’t easily use a join to show where users havent logged in.

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 & "'"
'Strips out characters such as , and '
sqlstatement = RTrim(sqlstatement) + ",'Replace(Replace(Me.comment, ",", ""), "'", "") & "'"