CJSmith dot me

I dump stuff I find useful here

Author: Chris Smith

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, ",", ""), "'", "") & "'"

Sage EDI IN Intermediary Email

Published / by Chris Smith / Leave a Comment

I have found users don’t always read the logs from the Adaptus EDI Module anymore in Sage Line 500.
There are a few reasons why orders get stuck, the most common is a customer has sent a duplicate EDI file although one was caused by incorrect product supersession setup causing an endless loop.

USE <Affected Sage Company MSSQL Database>
GO
CREATE procedure [dbo].[usp_email_edi_held_orders]
AS
-- =============================================
-- Author:		Christopher Smith
-- Create date: 01/08/2017
-- Description:	The procedure looks for orders held in the EDI IN Intermediary Tables
-- Changes:
-- 1.0.1 - 02/08/2017 - Added dateformat dmy as when run was reporting "Conversion failed when converting date and/or time from character string.".  Ensured all date values were cast as datetime as well.
-- 1.0.2 - 11/04/2019 - Corrected the Sales Order lookup to check the invoice customer instead of delivery customer on opheadm
-- =============================================
-- exec [dbo].[usp_email_edi_held_orders]
-- =============================================
--------------------------------------------------------------------------------------------------------------------------
print 'Change to DMY format'
--------------------------------------------------------------------------------------------------------------------------
SET DATEFORMAT dmy;
--------------------------------------------------------------------------------------------------------------------------
print 'Populate temp table'
--------------------------------------------------------------------------------------------------------------------------
Create Table #results(
	customer VARCHAR(8),
	existingorderno varchar(10),
	received_date char(8),
	customer_order VARCHAR(20),
	"filename" char(30),
	warehouse char(2),
	product char(20),
	long_description char(40),
	required_quantity int
	)
insert into #results (customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity) 
	SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
	FROM "scheme"."eiorhdm" t1
		INNER JOIN "scheme"."eiordtm" t2
			ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))
	WHERE t1.update_status = 'h' and CAST(received_date as datetime) BETWEEN CAST(GETDATE() -7 as datetime) and CAST(GETDATE() as datetime)
--------------------------------------------------------------------------------------------------------------------------
print 'Check if need to send email'
--------------------------------------------------------------------------------------------------------------------------
if (select count(*) AS nos from #results) = 0 
begin
print 'No Email to Send'
goto END1
end 
--------------------------------------------------------------------------------------------------------------------------
print 'Check if existing SOP number exists and update table'
--------------------------------------------------------------------------------------------------------------------------
update t1
	set t1.existingorderno = t2.order_no
		from #results t1
			INNER JOIN scheme.opheadm(nolock) t2
				ON t1.customer_order = t2.customer_order_no and t1.customer = t2.invoice_customer
update #results
	set existingorderno = 'NOMATCH'
		from #results
		where existingorderno IS NULL
--------------------------------------------------------------------------------------------------------------------------
print 'Create HTML for Email'
--------------------------------------------------------------------------------------------------------------------------

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Orders held in the EDI IN Intermediary Tables</H1>' +
	N'<font size="5" face="arial" color="red"> <H2>Please review orders held in the EDI IN module, if the order held does not match an existing order further checks may need to be done</H2></font>' +
    N'<table border="1">' +
    N'<tr><th>Customer No</th><th>Existing SO No</th>' +
    N'<th>Customer Order No</th><th>EDI Filename</th><th>Received Date</th>' +
	N'<th>WH</th><th>Product</th>' +
	N'<th>Description</th><th>Qty</th>' +
    CAST ( ( select td =   customer,'',
	td = existingorderno,'',
	td = customer_order,'',
	td = "filename",'',
	td = received_date,'',
	td = warehouse,'',
	td = product,'',
	td = long_description,'',
	td = required_quantity
from #results
order by customer, customer_order
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + 
N'</table>'  + 
N'<p> </p>' + 
N'<p> </p>' + 
N'<p>Regards.</p>' + 
N'<p> </p>' +
N'<p>usp_email_edi_held_orders</p>' 
--------------------------------------------------------------------------------------------------------------------------
print 'Send Email'
--------------------------------------------------------------------------------------------------------------------------
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '<SQL Email Profile>',
	@recipients = '<Recipients>',
    @copy_recipients = '<Copy_Address>',
	@body = @tableHTML,
    @body_format = 'HTML',
	@subject = 'Orders held in the EDI IN Intermediary Tables'
END1:

Maintain regtrans-ms and blf files generated by Georgia UTS sessions

Published / by Chris Smith / Leave a Comment

A little script I schedule for servers using Georgia UTS.
Below I use Robocopy to move files older than 3 days, to avoid anyone currently logged in. For me I expect users to be logged on for no more than a day, active user files should therefore be untouched.

echo This Batch Files removes .regtrans-ms and .TM.blf which can take up space if unmaintained
echo Change to C drive to ensure correct
%systemdrive%
cd \
echo Create temporary directory
mkdir deltempfiles
echo Copy temporary files into temp directory
ROBOCOPY C:\Users\ C:\deltempfiles\ *.regtrans-ms /move /minage:3 /S /R:1 /W:1 /XJ
ROBOCOPY C:\Users\ C:\deltempfiles\ *.TM.blf /move /minage:3 /S /R:1 /W:1 /XJ
echo Cleanup
rmdir C:\deltempfiles\ /S /Q
echo Complete
exit

Force launch IE with a Shortcut

Published / by Chris Smith / Leave a Comment

I needed to force Windows 10 to open a web browser in IE for some internal applications as they still use ActiveX add-ins.
Rather simply instead of using a URL shortcut, use a standard shortcut calling IE and passing the URL as a parameter. Here I call the 32bit version of IE (As I don’t know of any add-ins for IE that would be used by most companies that are 64 bit)

"%ProgramFiles(x86)%/internet explorer/iexplore.exe" <Web Address>

Firefox supports the same type of shortcut

"%ProgramFiles(x86)%\Mozilla Firefox\firefox.exe" https://www.cjsmith.me

Disable Powerpoint 2016 extended display mode

Published / by Chris Smith / Leave a Comment

Credit: http://tips.timscomputer.com/disable-powerpoint-2013-extended-display-mode-via-registry/
Disable the extended display mode upon login, same as the link just modified the version from 15 to 16

Run the following as a batch file upon login

@echo off
reg add HKCU\Software\Microsoft\Office\16.0\PowerPoint\Options /v UseMonMgr /t REG_DWORD /d 0 /f

Windows 2008: Task Scheduler causes account lockout when policy locks account after one attempt

Published / by Chris Smith / Leave a Comment

I encountered a very odd issue where I was attempting to amend a scheduled task on a Windows 2008 R2 server. When attempting to amend to a “service” account it locked out immediately. This account has a policy where it is locked out if the password is incorrect once. After several attempts and having to unlock the account every time, we spotted that every time a task was changed two event log entries were added instead of one.
It turns out the Task Scheduler in Windows 2008/2008 R2 was causing this, it first attempts to login with a blank password and then a second attempt is made with the valid password.
This causes the below (An error has occured for task . Error message: The following error was reported: 2147944309.) as the first attempt locks the account out.

The Event Log also has the following entries

An error has occured for task <SCHEDULEDTASKNAME>.  Error message: The following error was reported: 2147944309.
Audit Failure      26/11/2018 13:48:39       Microsoft Windows security auditing.     4625       Logon
 
An account failed to log on.
 
Subject:
                Security ID:                         SYSTEM
                Account Name:                 <MACHINENAME$>
                Account Domain:                              <DOMAIN>
                Logon ID:                             0x3e7
 
Logon Type:                                       4
 
Account For Which Logon Failed:
                Security ID:                         NULL SID
                Account Name:                 <USERNAME>
                Account Domain:                              <DOMAIN>
 
Failure Information:
                Failure Reason:                 Unknown user name or bad password.
                Status:                                  0xc000006d
                Sub Status:                         0xc000006a
 
Process Information:
                Caller Process ID:              0x14c
                Caller Process Name:      C:\Windows\System32\svchost.exe
 
Network Information:
                Workstation Name:         <MACHINENAME>
                Source Network Address:             -
                Source Port:                       -
 
Detailed Authentication Information:
                Logon Process:                  Advapi  
                Authentication Package:               Negotiate
                Transited Services:          -
                Package Name (NTLM only):        -
                Key Length:                        0
 
This event is generated when a logon request fails. It is generated on the computer where access was attempted.
 
The Subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.
 
The Logon Type field indicates the kind of logon that was requested. The most common types are 2 (interactive) and 3 (network).
 
The Process Information fields indicate which account and process on the system requested the logon.
 
The Network Information fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.
 
The authentication information fields provide detailed information about this specific logon request.
                - Transited services indicate which intermediate services have participated in this logon request.
                - Package name indicates which sub-protocol was used among the NTLM protocols.
                - Key length indicates the length of the generated session key. This will be 0 if no session key was requested.
 
 
 
An account failed to log on.
 
Subject:
                Security ID:                         SYSTEM
                Account Name:                 <MACHINENAME$>
                Account Domain:                              <DOMAIN>
                Logon ID:                             0x3e7
 
Logon Type:                                       4
 
Account For Which Logon Failed:
                Security ID:                         NULL SID
                Account Name:                 <USERNAME>
                Account Domain:                              <DOMAIN>
 
Failure Information:
                Failure Reason:                 Account locked out.
                Status:                                  0xc0000234
                Sub Status:                         0x0
 
Process Information:
                Caller Process ID:              0x14c
                Caller Process Name:      C:\Windows\System32\svchost.exe
 
Network Information:
                Workstation Name:         <MACHINENAME>
                Source Network Address:             -
                Source Port:                       -
 
Detailed Authentication Information:
                Logon Process:                  Advapi  
                Authentication Package:               Negotiate
                Transited Services:          -
                Package Name (NTLM only):        -
                Key Length:                        0
 
This event is generated when a logon request fails. It is generated on the computer where access was attempted.
 
The Subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.
 
The Logon Type field indicates the kind of logon that was requested. The most common types are 2 (interactive) and 3 (network).
 
The Process Information fields indicate which account and process on the system requested the logon.
 
The Network Information fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.
 
The authentication information fields provide detailed information about this specific logon request.
                - Transited services indicate which intermediate services have participated in this logon request.
                - Package name indicates which sub-protocol was used among the NTLM protocols.
                - Key length indicates the length of the generated session key. This will be 0 if no session key was requested.

Rather annoyingly the fix is no longer available, it is now included in a Convenience Rollup available from Microsoft Support. I can’t however see any mention of it in this. Thanks to Andrew Bainger from ABF for pointing me towards this.