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 […]
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.
1 2 3 4 | '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, ",", ""), "'", "") & "'" |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | 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: |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 |
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 […]
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
1 2 | @echo off reg add HKCU\Software\Microsoft\Office\16.0\PowerPoint\Options /v UseMonMgr /t REG_DWORD /d 0 /f |
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 […]
As the email field can sometimes not be used for the correct purpose I use a bit of code to look for an @ symbol and then a dot. If more than one email address is in I replace the comma with -t which is required by dbmailsend. If using Sage Line 500/1000 the CustomerTable […]
I recently had an issue where someone had deleted my admin user profile from a remote server by deleting its folder. The logon then failed as Windows still expected the folder to be there, as obviously the registry entries were not removed. Rather than manually checking HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList for the SID and then removing it […]
For anyone who has had the annoyance of trying to backup a Cisco Unity Communications based server to a Windows based server, you will know how annoying it can be. I recommend Syncplify.me Server! and it has a how to for its older version of Server!. They have obviously changes it to a web based […]