Category: Windows

Tidy Sage Line 500 ERP Temporary Tables via TSQL

Published February 16, 2020 / 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

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

Posted In Sage Line 500 ERP | SQL Server

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

Published February 16, 2020 / 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

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

Posted In Access 2010 | Visual Basic for Applications

Arcsoft Totalmedia Extreme causing issues upgrading to Windows 10 Version 1909

Published December 29, 2019 / 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.

Posted In Windows 10

Powershell issue with output characters changing to ?

Published September 29, 2019 / 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"'

1	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);

BULK INSERT #bcp_file_mem

FROM @CSVFile

WITH (FORMAT = 'CSV', DATAFILETYPE = 'widechar', FIELDQUOTE = '"', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK);

Posted In Batch File Scripting | SQL Server | SQL Server 2008 | SQL Server 2008 Express | SQL Server 2012

Windows 2008 – Alert on Scheduled Job Failure

Published August 11, 2019 / 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

Posted In Windows Server 2008

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

Published July 20, 2019 / 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.

Posted In Sage Line 500 ERP

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

Published June 09, 2019 / 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

USE <Sage_Company_DB>

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

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

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

Posted In Cindercone Magma | Sage Line 500 ERP | SQL Server

Check the audit on Business Objects XIR3 SP7 for logons

Published June 03, 2019 / 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

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.

Posted In Business Objects

In VBA strip out characters which cause TSQL to fail

Published May 20, 2019 / 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, ",", ""), "'", "") & "'"

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

Posted In Visual Basic for Applications

Sage EDI IN Intermediary Email

Published May 01, 2019 / 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:

100

101

USE <Affected Sage Company MSSQL Database>

CREATE procedure [dbo].[usp_email_edi_held_orders]

-- =============================================

-- 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>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:

Posted In Sage Line 500 ERP | SQL Server 2008 | SQL Server 2012

CJSmith dot me

I dump stuff I find useful here

Category: Windows

Tidy Sage Line 500 ERP Temporary Tables via TSQL

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

Arcsoft Totalmedia Extreme causing issues upgrading to Windows 10 Version 1909

Powershell issue with output characters changing to ?

Windows 2008 – Alert on Scheduled Job Failure

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

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

Check the audit on Business Objects XIR3 SP7 for logons

In VBA strip out characters which cause TSQL to fail

Sage EDI IN Intermediary Email