CJSmith dot me

I dump stuff I find useful here

Category: SQL Server

Sage Line 500 password policy implementation and force password reset

Published / by Chris Smith / Leave a Comment

I wrote a simple script to aid users moving to a new password policy, users were put on a policy which would expire a password after a day. Once they had changed their password the policy is amended to the new policy. I now use this for forcing password resets. Replace and with your Sage Line 500/1000 password policies

-- =============================================
-- Author:           Christopher Smith
-- Create date: 18/07/2018
-- Description:      Implement the new password policy in stages.
-- 1. Change the password policy first to enforce a password change in a short time period
-- 2. Backup and truncate the table [csmaster].[scheme].[pwdhistm]
-- 3. Run this routine nightly, it will pickup users who have changed their password and set the new policy
-- =============================================
-- Change Log:
-- 1.0 - 18/07/2018 - First Version
-- =============================================
-- exec dbo.usp_SageImplementPWPolicy
-- exec [dbo].[usp_ForceSagePWChange] 'csmithte'
-- =============================================
CREATE PROCEDURE [dbo].[usp_SageImplementPWPolicy]
AS
BEGIN
       SET NOCOUNT ON;
-- =============================================
print 'Declare Variables and setup'
-- =============================================
  declare @spdate1 date
  declare @spdate2 int
  declare @secgroup char(3)
  declare @oldsecgroup char(4)
-- Set variable to 2 days ago
  set @spdate1 = CAST(GETDATE() as date)
  print @spdate1
-- Convert the date to one which matches the Sage table
  set @spdate2 = DATEDIFF(d,'01 jan 1900',@spdate1)
  print 'Sage Date Code for Today'
  print @spdate2
  print 'New user security group'
  set @secgroup = ''
  set @oldsecgroup = ''
  print @secgroup
  print @oldsecgroup
-- =============================================
print 'Populate working table'
-- =============================================
       select t1.name, dateadd(d,t2."setup",'01 jan 1900') as pw_date, t1.sec_group, 'NO ' as pw_changed into #pwworking
              from scheme.[usermastm](nolock) t1
                     INNER JOIN scheme.passexpm(nolock) t2
                           on t1.name = t2.user_id
              where t1.sec_group = @oldsecgroup
       select * from #pwworking
-- =============================================
print 'Check if password recently changed'
-- =============================================
       update #pwworking
              set pw_changed = 'YES'
                     where name IN
                           (select "username" from [csmaster].[scheme].[pwdhistm] group by "username")
       select * from #pwworking
-- =============================================
print 'Change security group for those who have a new password on the new policy'
-- =============================================
       update scheme.[usermastm]
              set sec_group = @secgroup
                     where name IN (select name from #pwworking where pw_changed = 'YES')
drop table #pwworking
END
GO

Below is the code used to force users to enter a new password upon login

USE [csmaster]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_ForceSagePWChange] @user char(8)
--EXEC usp_ForceSagePWChange ''
AS
--IF @user = 'ALL' GOTO ALLUSERS
--IF @user != 'ALL' GOTO SINGLEUSER
BEGIN
/*ALLUSERS:
update scheme.passexpm
set setup = '10001'
GOTO FINISH
SINGLEUSER:*/

update csmaster.scheme.passexpm
set setup = '10001'
where user_id = @user
update csmaster.[scheme].[usermastm]
set [sec_group] = ''
where name = @user
GOTO FINISH
FINISH:
END

Setup MS SQL Server Alerts via TSQL

Published / 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 

Tidy Sage Line 500 ERP Temporary Tables via TSQL

Published / 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

Powershell issue with output characters changing to ?

Published / 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"'

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

Mail on Login failure to a Microsoft SQL Server

Published / 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

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

Published / 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

Force TSQL dateformat

Published / by Chris Smith / Leave a Comment

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 an out-of-range value. [SQLSTATE 22007] (Error 242) The statement has been terminated.

As I am in the UK we tend to use dmy

print 'Force dateformat to dmy'
set dateformat 'dmy'

Microsoft Docs

Use a case to make sure provided data is a date

Published / by Chris Smith / Leave a Comment

I find this useful for using validating spreadsheet imports, using ISDATE can just blank the field if crap data is provided

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

Sage EDI IN Intermediary Email

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

Use T-Sql to find triggers on specific database

Published / by Chris Smith / Leave a Comment

Credit: Joe Stefanelli on Stack Overflow

I wanted a simple way to find triggers on a specific database, Joe Stefanelli posted SQL which did exactly what I wanted.

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'