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 […]
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.
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 | 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 |
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 […]
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 […]
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 |