Category: SQL Server
Sage Line 500/1000 Period End Email Check
As part of Sage Line 500/1000 Period End we found it easier and more audit friendly to have an automated email fire via SQL. My colleague Jim wrote the below (Hey Bob!), I then changed it around to match the sections in which we ran period end. This made it easier to determine if period…
Use TSQL to extract V1 DBArchive Blobs
Using TSQL it is very easy to automate extraction from Version One’s DBArchive product with its dbremoteblob program (It does require a username and password to access DBArchive and the program comes with DBArchive). With a 4 core CPU I extracted 72000 documents a day, a limitation found was the programs used did not return…
Check scheme.plitempm for any orphaned entries
Check scheme.plitempm for any orphaned entries against plpy tables, found to be caused by third party app not Sage Line 500.
Simple method to see what AD Users SQL Server can see in AD Groups
Credit: Microsoft StackOverflow user Saranya Kumaran Simple method to see what AD Users SQL Server can see in AD Groups exec xp_logininfo ‘Domain/AD Group’, ‘all’ exec xp_logininfo ‘Domain/AD Group’, ‘members’ Notes: Non ad group first command returns error, second command returns no output Msg 15404, Level 16, State 10, Procedure xp_logininfo, Line 64 [Batch Start…
Sage Line 500 password policy implementation and force password reset
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…
Setup MS SQL Server Alerts via TSQL
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]…
Tidy Sage Line 500 ERP Temporary Tables via TSQL
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…
Powershell issue with output characters changing to ?
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…
Mail on Login failure to a Microsoft SQL Server
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]…
Populate Sage table opheadm with credit note information for Cindercone Magma to send to Tungsten/OB10
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…