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 […]
I find this useful for using validating spreadsheet imports, using ISDATE can just blank the field if crap data is provided
1 2 3 4 5 6 7 | --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 |
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 […]