Setup MS SQL Server Alerts via TSQL

Published May 08, 2020 / by Chris Smith / Leave a Comment

Amend TSQL to your environment, run the TSQL, restart the agent service and test (I find it easy to enter an incorrect username and password).

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,                              @databasemail_profile=N'<TSQL_MAIL_PROFILE>',                              @use_databasemail=1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Login Mismatch',                               @message_id=18456,                               @severity=0,                               @enabled=1,                               @delay_between_responses=0,                               @include_event_description_in=1,                              @job_id=N'00000000-0000-0000-0000-000000000000'GO

USE [msdb]
GO 
/****** Object:  Alert [Login failed : State 5]    Script Date: 24/03/2020 12:50:49 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Login failed : State 5',                               @message_id=0,                               @severity=0,                               @enabled=1,                               @delay_between_responses=0,                               @include_event_description_in=5,                              @category_name=N'[Uncategorized]',                              @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',                              @wmi_query=N'SELECT * FROM AUDIT_LOGIN_FAILED WHERE State = 1 OR State=  5 OR State =  8 OR State= 14 OR State=  38',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object:  Alert [Error 823]    Script Date: 24/03/2020 12:51:12 ******/EXEC msdb.dbo.sp_add_alert @name=N'Error 823',                               @message_id=823,                               @severity=0,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Error 824]    Script Date: 24/03/2020 12:51:33 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error 824',                               @message_id=824,                               @severity=0,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Error 825]    Script Date: 24/03/2020 12:51:51 ******/EXEC msdb.dbo.sp_add_alert @name=N'Error 825',                               @message_id=825,                               @severity=0,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 016]    Script Date: 24/03/2020 12:52:05 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',                               @message_id=0,                               @severity=16,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Severity 017]    Script Date: 24/03/2020 12:52:14 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',                               @message_id=0,                               @severity=17,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 018]    Script Date: 24/03/2020 12:52:22 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',                               @message_id=0,                               @severity=18,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 019]    Script Date: 24/03/2020 12:52:34 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',                               @message_id=0,                               @severity=19,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Severity 020]    Script Date: 24/03/2020 12:52:44 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',                               @message_id=0,                               @severity=20,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 021]    Script Date: 24/03/2020 12:52:52 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',                               @message_id=0,                               @severity=21,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO   USE [msdb]GO /****** Object:  Alert [Severity 022]    Script Date: 24/03/2020 12:53:02 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',                               @message_id=0,                               @severity=22,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Severity 023]    Script Date: 24/03/2020 12:53:23 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',                               @message_id=0,                               @severity=23,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 024]    Script Date: 24/03/2020 12:53:32 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',                               @message_id=0,                               @severity=24,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 025]    Script Date: 24/03/2020 12:53:52 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',                               @message_id=0,                               @severity=25,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

USE [msdb]

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1, @databasemail_profile=N'<TSQL_MAIL_PROFILE>', @use_databasemail=1

USE [msdb]

EXEC msdb.dbo.sp_add_alert @name=N'Login Mismatch', @message_id=18456, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'GO

USE [msdb]

/****** Object: Alert [Login failed : State 5] Script Date: 24/03/2020 12:50:49 ******/

EXEC msdb.dbo.sp_add_alert @name=N'Login failed : State 5', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=5, @category_name=N'[Uncategorized]', @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', @wmi_query=N'SELECT * FROM AUDIT_LOGIN_FAILED WHERE State = 1 OR State= 5 OR State = 8 OR State= 14 OR State= 38', @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object: Alert [Error 823] Script Date: 24/03/2020 12:51:12 ******/EXEC msdb.dbo.sp_add_alert @name=N'Error 823', @message_id=823, @severity=0, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]

/****** Object: Alert [Error 824] Script Date: 24/03/2020 12:51:33 ******/

EXEC msdb.dbo.sp_add_alert @name=N'Error 824', @message_id=824, @severity=0, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object: Alert [Error 825] Script Date: 24/03/2020 12:51:51 ******/EXEC msdb.dbo.sp_add_alert @name=N'Error 825', @message_id=825, @severity=0, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object: Alert [Severity 016] Script Date: 24/03/2020 12:52:05 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 016', @message_id=0, @severity=16, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]

/****** Object: Alert [Severity 017] Script Date: 24/03/2020 12:52:14 ******/

EXEC msdb.dbo.sp_add_alert @name=N'Severity 017', @message_id=0, @severity=17, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object: Alert [Severity 018] Script Date: 24/03/2020 12:52:22 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 018', @message_id=0, @severity=18, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object: Alert [Severity 019] Script Date: 24/03/2020 12:52:34 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 019', @message_id=0, @severity=19, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]

/****** Object: Alert [Severity 020] Script Date: 24/03/2020 12:52:44 ******/

EXEC msdb.dbo.sp_add_alert @name=N'Severity 020', @message_id=0, @severity=20, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object: Alert [Severity 021] Script Date: 24/03/2020 12:52:52 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 021', @message_id=0, @severity=21, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object: Alert [Severity 022] Script Date: 24/03/2020 12:53:02 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 022', @message_id=0, @severity=22, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]

/****** Object: Alert [Severity 023] Script Date: 24/03/2020 12:53:23 ******/

EXEC msdb.dbo.sp_add_alert @name=N'Severity 023', @message_id=0, @severity=23, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object: Alert [Severity 024] Script Date: 24/03/2020 12:53:32 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 024', @message_id=0, @severity=24, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object: Alert [Severity 025] Script Date: 24/03/2020 12:53:52 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 025', @message_id=0, @severity=25, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'

Sage Line 500 User Activity Data

Published May 08, 2020 / by Chris Smith / Leave a Comment

Sage Line 500 ERP maintains user activity data in: DATABASE_SERVER.csmaster.scheme.uar

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

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

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

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

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

Mail on Login failure to a Microsoft SQL Server

Published August 11, 2019 / by Chris Smith / Leave a Comment

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.

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

USE [msdb]

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

USE [msdb]

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

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

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

CJSmith dot me

I dump stuff I find useful here

Setup MS SQL Server Alerts via TSQL

Sage Line 500 User Activity Data

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

Mail on Login failure to a Microsoft SQL Server

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