Database Toolset Toolset of tools and script for maintaining the databases. We also included scripts for different applications.  Copyright Notice SFL Services LLC has prepared this document for use only by their staff, agents, customers and prospective customers. Companies, names and data used as examples in this document are fictitious unless otherwise noted. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of SFL Services LLC, who reserve the right to change specifications and other information contained herein without prior notice. The reader should consult SFL Services LLC to determine whether any such changes have been made. Licensing and Warranty The terms and conditions governing the licensing of SFL Services LLC software consist solely of those set forth in the written contracts between SFL Services LLC and its customers. Except as expressly provided for in the warranty provisions of those written contracts, no representation or other affirmation of fact contained in this document, including but not limited to statements regarding capacity, suitability for use or performance of products described herein, shall be deemed to be a warranty by SFL Services LLC for any purpose, or give rise to any liability of SFL Services LLC whatsoever. Liability In no event shall SFL Services LLC be liable for any incidental, indirect, special or consequential damages whatsoever (including but not limited to lost profits) arising out of or related to this document or the information contained in it, even if SFL Services LLC had been advised, knew or should have known of the possibility of such damages, and even if they had acted negligently. MsSQL - Setup Emailing /* Turn on MsSQL emailing on Created By: Steve Ling 2024/08/31 */ -- Run First if (SELECT [value] FROM sys.configurations WHERE [name] = N'show advanced options') <> 1 Begin EXEC sp_configure 'show advanced options', 1; RECONFIGURE; End if (SELECT [value] FROM sys.configurations WHERE [name] = N'Database Mail XPs') <> 1 Begin EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE End -- Declaration Parameters Declare @SMTP nvarchar(50), @SendFromEmail nvarchar(100), @Port int, @SSL int, @User nvarchar(50), @Pass nvarchar(50) -- SMTP server Set @SMTP = 'mail.onling.com' Set @SendFromEmail = 'no_reply@sflservicesllc.com' Set @Port = 25 Set @SSL = 0 --1 = True, 0= False --If @SSL is false leave the following blank Set @User = '' Set @Pass = '' -- ########### No changes below here ########### -- Create a Database Mail profile EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'Notifications', @description = 'Profile used for sending outgoing notifications.' ; -- Grant access to the profile to the DBMailUsers role EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'Notifications', @principal_name = 'public', @is_default = 1 ; -- Create a Database Mail account EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SMTP', @description = 'Mail account for sending outgoing notifications.', @email_address = @SendFromEmail, @display_name = 'Automated Mailer', @mailserver_name = @SMTP, @port = @Port, @enable_ssl = @SSL, @username = @User, @password = @Pass ; -- Add the account to the profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Notifications', @account_name = 'SMTP', @sequence_number =1 ; GO -- Run to Delete /* EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'Notifications' EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'Notifications' EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'SMTP' EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Notifications' */ -- Run to Test /* EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'Use a valid e-mail address', @body = 'The database mail configuration was completed successfully.', @subject = 'Automated Success Message'; GO */   ESP - Database Copy Script /* Search and replace for the ESP database and its folders Created By : SFL 08/13/23 Modified By : SFL 08/21/24 Use at own risk */ USE ESP_DEV; /* select * from infParameter where value like '%/%' select * from infPlantValue where value like '%/%' select * from infGroupValue where value like '%/%' select * from infpersonalValue where value like '%/%' select * from infParameter where value like '%\%' select * from infPlantValue where value like '%\%' select * from infGroupValue where value like '%\%' select * from infpersonalValue where value like '%\%' */ --update infParameter set mainttime=getdate(),userid='CopyDB', value=replace(value,'\PRD\','\DEV\') where value like '%\PRD\%' update infParameter set mainttime=getdate(),userid='CopyDB', value=replace(value,'\\SFL-APP-001.onling.com\\KiwiplanInterfaces\Prd\','\\SFL-APP-001.onling.com\KiwiplanInterfaces\Dev\') where value like '%\\SFL-APP-001.onling.com\%' update infmapdataset set userid='CopyDB', mainttime=getdate(),hostuser='remuser', hostpassword='l5oPU0kDgwy7vPj/uUFuqA==' where hostdataset ='fgsc' update infParameter set mainttime=getdate(),userid='CopyDB', value='SFL-APP-001' where name ='Purge server name' delete from infMAPDataset where id not in (select mapdatasetID from orgplant) update infMAPDataset set mainttime=getdate(),userid='CopyDB', hostname='USW2-D-KP-MAP' where hostdataset ='fgsc' update infParameter set mainttime=getdate(), userid='CopyDB', value='TEST 9.80.5040' where name='database status' delete from agtAgentServer where id not in (select agentserverID from agtagent) update agtAgentServer set mainttime=getdate(),userid='CopyDB', name='SFL-APP-001' update agtagent set mainttime=getdate(),userid='CopyDB', agentServerID =(select id from agtAgentServer where name='SFL-APP-001') update infParameter set mainttime=getdate(),userid='CopyDB', value='SFL-APP-001' where name = 'KDG server name' /* delete from infUserMembership where memberID > (select min(id) from infUser) delete from infTaskbutton where usernameID not in (select memberid from infUserMembership ) delete from infUser where id > (select min(id) from infUser) */ --Need SID search and replace for all users tool ran --create folders that do not exist after replace To DO! update infparameter set userid='Move_to_', mainttime=getdate() , value=0 where name='Age in Days for archiving' and value != 0 update orgContact set email = '' update orgAddress set notificationemail = ''   ESP - Delete User /* Delete users from ESP which do not exist in Active Directory Created By : Steve Ling 2024/03/07 The user name is the name from the SAM account in ADD Or if you do the following select select name from infuser */ Declare @username nvarchar(30), @UID int, @UPD int Set @username = 'Steve.Ling' Set @UPD = 0 --1 to delete 0 to select --- No Changes below here Set @UID = (select ID from infuser where name = @username) if @UPD <> 1 Begin select * from infTaskbutton where usernameID = @UID select * from infUserMembership where memberID = @UID select * from infuser where name = @username End if @UPD = 1 Begin delete from infTaskbutton where usernameID = @UID delete from infUserMembership where memberID = @UID delete from infuser where name = @username End ESP - Create Financial Year and Period /* Script to auto add finacial year and its periods if they are missing Created By : Steve Ling 2024/03/02 You may have to change the stating year depending on your finacial year and periods */ GO Declare @y nvarchar(4), @yid int, @ly nvarchar(4), @ny nvarchar(4) Set @y = '2030' Set @ny = (select @y+1) print @ny Set @ly = (Select CASE WHEN @ny & 3 = 0 AND (@ny % 25 <> 0 OR @ny & 15 = 0) THEN '0229' else '0228' end) print @ly INSERT INTO [dbo].[orgfinancialyear] ([mainttime] ,[userid] ,[name] ,[startdate] ,[enddate]) VALUES (getdate() ,'Script' ,@y ,Convert(datetime,@y+'0601 00:00:00') ,Convert(datetime,@ny+'0530 23:59:59') ) set @yid = (select @@IDENTITY ) INSERT INTO [dbo].[orgfinancialperiod] ([mainttime] ,[userid] ,[endDate] ,[periodnumber] ,[name] ,[startdate] ,[financialyearID]) VALUES (GETDATE(),'Script',Convert(datetime,@y+'0628 23:59:59'),1,'June',Convert(datetime,@y+'0601 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0802 23:59:59'),2,'July',Convert(datetime,@y+'0629 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0830 23:59:59'),3,'August',Convert(datetime,@y+'0803 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0927 23:59:59'),4,'September',Convert(datetime,@y+'0831 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'1101 23:59:59'),5,'October',Convert(datetime,@y+'0928 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'1129 23:59:59'),6,'November',Convert(datetime,@y+'1102 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@ny+'0103 23:59:59'),7,'December',Convert(datetime,@y+'1130 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@ny+'0131 23:59:59'),8,'January',Convert(datetime,@ny+'0104 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@ny+@ly+' 23:59:59'),9,'February',Convert(datetime,@ny+'0201 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@ny+'0328 23:59:59'),10,'March',Convert(datetime,@ny+'0301 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@ny+'0502 23:59:59'),11,'April',Convert(datetime,@ny+'0329 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@ny+'0530 23:59:59'),12,'May',Convert(datetime,@ny+'0503 00:00:00'),@yid) select * from orgfinancialyear where id=@yid;select * from orgfinancialperiod where financialyearID =@yid GO /* Script to auto add finacial year and its periods if they are missing Created By : Steve Ling 2024/03/02 You may have to change the stating year depending on your finacial year and periods This one is January to December */ GO Declare @y nvarchar(4), @yid int, @ly nvarchar(4) Set @y = '2016' Set @ly = (Select CASE WHEN @y & 3 = 0 AND (@y % 25 <> 0 OR @y & 15 = 0) THEN '0229' else '0228' end) INSERT INTO [dbo].[orgfinancialyear] ([mainttime] ,[userid] ,[name] ,[startdate] ,[enddate]) VALUES (getdate() ,'Script' ,@y ,Convert(datetime,@y+'0101 00:00:00') ,Convert(datetime,@y+'1231 23:59:59') ) set @yid = (select @@IDENTITY ) INSERT INTO [dbo].[orgfinancialperiod] ([mainttime] ,[userid] ,[endDate] ,[periodnumber] ,[name] ,[startdate] ,[financialyearID]) VALUES (GETDATE(),'Script',Convert(datetime,@y+'0131 23:59:59'),1,'January',Convert(datetime,@y+'0101 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+@ly+' 23:59:59'),2,'February',Convert(datetime,@y+'0201 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0331 23:59:59'),3,'March',Convert(datetime,@y+'0301 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0430 23:59:59'),4,'April',Convert(datetime,@y+'0401 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0531 23:59:59'),5,'May',Convert(datetime,@y+'0501 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0630 23:59:59'),6,'June',Convert(datetime,@y+'0601 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0731 23:59:59'),7,'July',Convert(datetime,@y+'0701 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0831 23:59:59'),8,'August',Convert(datetime,@y+'0801 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'0930 23:59:59'),9,'September',Convert(datetime,@y+'0901 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'1031 23:59:59'),10,'October',Convert(datetime,@y+'1001 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'1130 23:59:59'),11,'November',Convert(datetime,@y+'1101 00:00:00'),@yid), (GETDATE(),'Script',Convert(datetime,@y+'1231 23:59:59'),12,'December',Convert(datetime,@y+'1201 00:00:00'),@yid) --select * from orgfinancialyear where id=@yid;select * from orgfinancialperiod where financialyearID =@yid GO MsSQL - Create Database Table from Code declare @TableName sysname = 'TableName' declare @Result varchar(max) = 'public class ' + @TableName + ' {' select @Result = @Result + ' public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; } ' from ( select replace(col.name, ' ', '_') ColumnName, column_id ColumnId, case typ.name when 'bigint' then 'long' when 'binary' then 'byte[]' when 'bit' then 'bool' when 'char' then 'string' when 'date' then 'DateTime' when 'datetime' then 'DateTime' when 'datetime2' then 'DateTime' when 'datetimeoffset' then 'DateTimeOffset' when 'decimal' then 'decimal' when 'float' then 'double' when 'image' then 'byte[]' when 'int' then 'int' when 'money' then 'decimal' when 'nchar' then 'string' when 'ntext' then 'string' when 'numeric' then 'decimal' when 'nvarchar' then 'string' when 'real' then 'float' when 'smalldatetime' then 'DateTime' when 'smallint' then 'short' when 'smallmoney' then 'decimal' when 'text' then 'string' when 'time' then 'TimeSpan' when 'timestamp' then 'long' when 'tinyint' then 'byte' when 'uniqueidentifier' then 'Guid' when 'varbinary' then 'byte[]' when 'varchar' then 'string' else 'UNKNOWN_' + typ.name end ColumnType, case when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') then '?' else '' end NullableSign from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id where object_id = object_id(@TableName) ) t order by ColumnId set @Result = @Result + ' }' print @Result Stored Procedure ESP - Add Notifications to All Active Companies /* This script will add Notifications to all active companies Please run in SQLAgent every 15 minutes. Modified Steven F Ling 2019-11-27 Modified Steven F Ling 2023-08-14 */ DECLARE @companyid INT DECLARE @name VARCHAR(100) DECLARE @regionName VARCHAR(100) DECLARE @not1id INT, @not2id INT, @not3id INT, @not4id INT, @not5id INT --##### User Variables --##### Please set these variables before running this script DECLARE @updateUser VARCHAR(100) = 'AddNotificationScript' DECLARE @messageSystemID1 INT = (SELECT id FROM mscmessagesystem WHERE name = 'Invoice_Export') DECLARE @messageSystemID2 INT = (SELECT id FROM mscmessagesystem WHERE name = 'espOrderExport') DECLARE @messageSystemID3 INT = (SELECT id FROM mscmessagesystem WHERE name = 'ESP Docket Export') DECLARE @messageSystemID4 INT = (SELECT id FROM mscmessagesystem WHERE name = 'CreditNote_Export') DECLARE @messageSystemID5 INT = (SELECT id FROM mscmessagesystem WHERE name = 'espOrderExport') -- Where clause added to look for only Active companies DECLARE look CURSOR FOR SELECT c.id,c.name FROM orgcompany c INNER JOIN orgaddress a ON c.billingAddressID=a.id LEFT Join orgNotification n on n.forCompanyID = c.id WHERE companystatus='Active' and n.id is null --and n.deliverbyMessageSystemID != @messageSystemID3 ORDER BY c.id OPEN look FETCH NEXT FROM look INTO @companyid, @name WHILE @@FETCH_status=0 BEGIN print '' print '=====Starting with company and looking for Notifications: '+CONVERT(VARCHAR,@name)+'=====' print '' --##### The notificationtype must be changed on each of the following lines to reflect the notifications being used SELECT @not1id=(SELECT id FROM orgnotification WHERE forcompanyid=@companyid AND notificationtype ='InvoicePosted' and name ='Invoice') SELECT @not2id=(SELECT id FROM orgnotification WHERE FORcompanyid=@companyid AND notificationtype ='OrderConfirmed') SELECT @not3id=1 --(SELECT id FROM orgnotification WHERE FORcompanyid=@companyid AND notificationtype ='DocketImported') SELECT @not4id=1 --(SELECT id FROM orgnotification WHERE forcompanyid=@companyid AND notificationtype ='InvoicePosted' and name ='Credit') SELECT @not5id=(SELECT id FROM orgnotification WHERE FORcompanyid=@companyid AND notificationtype ='OrderCancelled') --##### END notificationtype changes prINT 'Notification adding process FOR: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) --Check to see if notification 1 exists the begin` --IF @not1id IS NULL -- BEGIN -- print 'Adding Invoice Export FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) -- INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) -- VALUES(GETDATE(),@updateUser,'InvoicePosted',0,2,'Invoice_Export',@companyid,@messageSystemID1,NULL,NULL,'SAPInvoiceExport',null) -- print 'Added' -- END -- Else -- print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) /* Comment out this section if you are not using Order Confirmations */ IF @not2id IS NULL BEGIN print 'Adding the ESP_Order Confirmation Export NotIFication FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) VALUES(GETDATE(),@updateUser,'OrderConfirmed',0,2,'ESPOrder_Export',@companyid,@messageSystemID2,NULL,NULL,'OrderConfirmed', null) print 'Added' END Else print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) /* This is the Docket Export Section */ --IF @not3id IS NULL -- BEGIN -- print 'Added the ESP_Docket Export FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) -- INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) -- VALUES(GETDATE(),@updateUser,'DocketFirstShipped',0,2,'ESP Docket Export',@companyid,@messageSystemID3,NULL,NULL,'ESP_Docket Export Notification', '[masterbilloflading.masterbolnumber]&"_"&[docketnumber]&"_"&[plant.plantcode]&"_"') -- print 'Added' -- END -- Else -- print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) --Check to see if notification 4 exists the begin` --IF @not4id IS NULL -- BEGIN -- print 'Adding the Invoice Export FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) -- INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) -- VALUES(GETDATE(),@updateUser,'InvoicePosted',0,2,'SFR015_CreditNote_Export',@companyid,@messageSystemID4,NULL,NULL,'SAPCreditExport',null) -- print 'Added' -- END -- Else -- print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) --Check to see if notification 5 exists the begin` IF @not5id IS NULL BEGIN print 'Adding the ESP_Order Cancelled Export NotIfication FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) VALUES(GETDATE(),@updateUser,'OrderCancelled',0,2,'ESPOrder_Export',@companyid,@messageSystemID2,NULL,NULL,'OrderCancelled', null) print 'Added' END Else print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name) FETCH NEXT FROM look INTO @companyid,@name print '' print '=====Ended with '+'Company Name: '+CONVERT(VARCHAR,@name)+' =====' print '' print '' END CLOSE look DEALLOCATE look   ESP - Delete Product Design in Imported Status /************************************************ This script is to delete PD that do not have Orders on them and are in import status 20140207 Steven F Ling created 20230401 Steven F Ling Modified Changed for the new Database structure *************************************************/ declare @dnid int declare @rid int declare @dn varchar(30) declare @slid int declare @pdpid int declare @udid int declare look cursor for SELECT isnull(pd.ID,0), isnull(pd.designnumber,''), isnull(rt.ID,0), isnull(sl.id,0), isnull(pdp.id,0), isnull(ud.id,0) FROM ebxproductDesign pd with (NOLOCK) LEFT JOIN ebxroute rt ON pd.ID = rt.productDesignID LEFT JOIN ebxProductDesignPlant pdp ON pdp.productDesignID = pd.id LEFT JOIN esporder o ON o.productDesignID = pd.id LEFT JOIN fgsstockline sl ON sl.productdesignid = pd.id LEFT JOIN ebxunitizingData ud ON pdp.unitizingDataID = ud.id inner join orgcompany c on c.id = pd.companyID where productdesignstatus = 'imported' and o.productDesignID is null --and designnumber in ('') and c.companynumber ='1555' open look fetch next from look into @dnid, @dn ,@rid, @slid, @pdpid, @udid while @@fetch_status=0 begin IF @dnid > 0 Begin print 'preping to delete '+@dn +' with ID of '+convert(nvarchar(30),@dnid) print ' deleting ebxPriceListProductDesign' delete from ebxPriceListProductDesign where productdesignid=@dnid print ' deleting ebxproductprice' delete from ebxproductprice where productdesignid=@dnid print ' deleting cstcostestimateline' delete from cstcostestimateline where productdesignid=@dnid print ' deleting cstcostestimate' delete from cstcostestimate where productdesignid=@dnid print ' deleting ebxalternateRoute' delete from ebxalternateRoute where productdesignid=@dnid print ' deleting ebxmachinestep' delete from ebxmachinestep where routeid in (@rid) print ' deleting cstpurchaseCost PD' delete from cstpurchaseCost where productDesignID = @dnid print ' deleting cstpurchaseCost Route' delete from cstpurchaseCost where routeID = @rid print ' deleting ebxroute' delete from ebxroute where productdesignid=@dnid print ' deleting ebxProductDesignColourCoating' delete from ebxProductDesignColourCoating where productDesignID = @dnid print ' deleting ebxproductDesignInstruction' delete from ebxproductDesignInstruction where productDesignID = @dnid print ' Updating ebxunitizingData' update ebxunitizingData set ownerProductDesignPlantID=null where id=@udid print ' deleting ebxProductDesignPlant' delete from ebxProductDesignPlant where productDesignID = @dnid print ' deleting ebxunitizingData' delete from ebxunitizingData where id=@udid print ' deleting fgsstocklinePurchaser' delete from fgsstocklinePurchaser where stockLineID = @slid print ' deleting fgsstockLine' delete from fgsstockLine where productDesignID = @dnid print ' updating ebxproductDesign' Update ebxproductDesign set previousProductDesignID=null, nextProductDesignID=null where ID = @dnid print ' updating ebxproductDesign' Update ebxproductDesign set mainttime=getdate(), userid='Removed PD Link', previousProductDesignID=null where previousProductDesignID = @dnid print ' updating ebxproductDesign' Update ebxproductDesign set mainttime=getdate(), userid='Removed PD Link', nextProductDesignID=null where nextProductDesignID = @dnid print ' deleting ebxproductDesign' delete from ebxproductdesign where designnumber = @dn End Else Begin print 'does not exist...' end fetch next from look into @dnid, @dn ,@rid, @slid, @pdpid, @udid end close look deallocate look   ESP - Delete Old Message /* This will delete all old XML messages and events older then 5 days SFL 2008-11-29 Created Status 6 = history 5 = 4 = processing 3 = success 2 = pending 1 = failed 0 = created */ delete from infbigtext where foreignId <> 1 and lastchanged < dateadd(d,-5,getdate()) and foreigntype like 'Message%' delete from infapplicationevent where createddate < dateadd(d,-5,getdate()) --Delete Messages too declare @id int declare look cursor for Select id from mscmessage where processeddate < dateadd(d,-5,getdate()) and status in (3,6) open look fetch next from look into @id while @@fetch_status=0 begin update mscmessage set previousMessageID=null where previousMessageID=@id delete from mscmessage where id=@id fetch next from look into @id end close look deallocate look delete from infexception where mainttime < dateadd(dd,-5,getdate())   SQL - Kill Database Connections /* This is to kill off any connection to a database Created by : Steve Ling 2022/03/20 */ --This script will kill when ran USE MASTER GO DECLARE @Spid INT DECLARE @ExecSQL VARCHAR(255) DECLARE KillCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT DISTINCT SPID FROM MASTER..SysProcesses WHERE DBID = DB_ID('ESP_TEST') OPEN KillCursor -- Grab the first SPID FETCH NEXT FROM KillCursor INTO @Spid WHILE @@FETCH_STATUS = 0 BEGIN SET @ExecSQL = 'KILL ' + CAST(@Spid AS VARCHAR(50)) EXEC (@ExecSQL) -- Pull the next SPID FETCH NEXT FROM KillCursor INTO @Spid END CLOSE KillCursor DEALLOCATE KillCursor -- this script will give you selects and also kill USE MASTER GO DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';' --select * FROM master..sysprocesses WHERE dbid = db_id('ESP_DEV') EXEC(@kill);   MsSQL - Backups Introduction This is to create auto backups on a SQL server and place them on a drive. Credits go to: Ola Hallengren https://ola.hallengren.com The 20240104_Backup_ola_original.sql file is the untouched default version. 20240104_Backup_ola_original.sql Make sure you change the save location of the database, search for @Directory and replace the location. The 20240104_Create_Backups_Integrity_Index_Check.sql is the version used on ESP SQL servers with the use of a DB_Administration table defaults USER dB's to 96 hours and 336 hour for System tables 20240104_Create_Backups_Integrity_Index_Check.sql The 20251119_Create_Backups_Integrity_Index_Check.sql is the version used on ESP SQL servers with the use of a DB_Administration table defaults USER dB's to 169 hours and 336 hour for System tables 20251119_Create_Backups_Integrity_Index_Check.sql MsSQL - Verify Properties /* This will check the proprties on a given Database and also the server Created By : Steve Ling 2022/04/07 */ --Change the DB name here DECLARE @DATABASE NVARCHAR(50) = 'SHOP' /* No changes below this line */ ---- Simple Version Query --SELECT SERVERPROPERTY( 'productversion' ) AS "Product Version" , -- SERVERPROPERTY ( 'productlevel' ) AS "Product Level" , -- SERVERPROPERTY ( 'edition' ) AS "Edition" -- Full Properties Query SELECT SERVERPROPERTY( 'ComputerNamePhysicalNetBIOS' ) AS 'Current Failover Machine' , SERVERPROPERTY( 'MachineName' ) AS 'Main Machine Name' , SERVERPROPERTY( 'ServerName' ) AS 'Server\Instance Name' , SERVERPROPERTY( 'InstanceName' ) AS 'Instance Name' , SERVERPROPERTY( 'ProcessID' ) AS 'Instance ProcessID' , CASE SUBSTRING ( CONVERT (VARCHAR(50), SERVERPROPERTY( 'productversion' )), 1, 4) WHEN '8.00' THEN 'SQL2000' WHEN '9.00' THEN 'SQL2005' WHEN '10.0' THEN 'SQL2008' WHEN '10.5' THEN 'SQL2008 R2' WHEN '11.0' THEN 'SQL2012' WHEN '12.0' THEN 'SQL2014' ELSE 'Undetermined' END AS 'SQL Version' , SERVERPROPERTY( 'ProductVersion' ) AS 'Product Version' , SERVERPROPERTY( 'ProductLevel' ) AS 'Product Level' , SERVERPROPERTY( 'Edition' ) AS 'Edition' , CASE SERVERPROPERTY( 'EngineEdition' ) WHEN 1 THEN 'Person / Desktop' WHEN 2 THEN 'Standard' WHEN 3 THEN 'Enterprise' WHEN 4 THEN 'Express' WHEN 5 THEN 'SQL Database' ELSE 'Unknown' END AS EngineEdition, CASE SERVERPROPERTY( 'IsIntegratedSecurityOnly' ) WHEN 1 THEN 'Window Authentication' WHEN 0 THEN 'Windows and SQL Authenication' ELSE 'Unknown' END AS 'Security Mode' , SERVERPROPERTY( 'Collation' ) AS 'Collation' , SERVERPROPERTY( 'BuildClrVersion' ) AS 'CLR Version' , CASE SERVERPROPERTY( 'IsFullTextInstalled' ) WHEN 0 THEN 'Installed' WHEN 1 THEN 'Not Installed' ELSE 'Unknown' END AS 'Full Text Indexing' , CASE SERVERPROPERTY( 'IsHadrEnabled' ) WHEN 0 THEN 'Disabled' WHEN 1 THEN 'Enabled' ELSE 'Unknown' END AS 'AlwaysOn Availability Groups' , CASE SERVERPROPERTY( 'HadrManagerStatus' ) WHEN 0 THEN 'Not Started / PENDING' WHEN 1 THEN 'Start/Running' WHEN 2 THEN 'Not Started / FAILED' ELSE 'Unknown' END AS 'AlwaysOn Availability Groups Manager' , CASE SERVERPROPERTY( 'IsClustered' ) WHEN 0 THEN 'Clustered' WHEN 1 THEN 'Not Clustered' ELSE 'Unknown' END AS 'Failover Cluster' , CASE SERVERPROPERTY( 'IsSingleUser' ) WHEN 0 THEN 'Not in Single User Mode' WHEN 1 THEN 'In Single User Mode' ELSE 'Unknown' END AS 'Single User Mode' ; DECLARE @DB SYSNAME = @DATABASE SELECT 'Collation' as Property, DATABASEPROPERTYEX (@DB, 'Collation') as Value UNION SELECT 'ComparisonStyle' as Property, DATABASEPROPERTYEX (@DB, 'ComparisonStyle') as Value UNION SELECT 'Edition' as Property, DATABASEPROPERTYEX (@DB, 'Edition') as Value UNION SELECT 'IsAnsiNullDefault' as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiNullDefault') as Value UNION SELECT 'IsAnsiNullsEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiNullsEnabled') as Value UNION SELECT 'IsAnsiPaddingEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiPaddingEnabled') as Value UNION SELECT 'IsAnsiWarningsEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsAnsiWarningsEnabled') as Value UNION SELECT 'IsArithmeticAbortEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsArithmeticAbortEnabled') as Value UNION SELECT 'IsAutoClose' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoClose') as Value UNION SELECT 'IsAutoCreateStatistics' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoCreateStatistics') as Value UNION SELECT 'IsAutoCreateStatisticsIncremental' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoCreateStatisticsIncremental') as Value UNION SELECT 'IsAutoShrink' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoShrink') as Value UNION SELECT 'IsAutoUpdateStatistics' as Property, DATABASEPROPERTYEX (@DB, 'IsAutoUpdateStatistics') as Value UNION SELECT 'IsClone' as Property, DATABASEPROPERTYEX (@DB, 'IsClone') as Value UNION SELECT 'IsCloseCursorsOnCommitEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsCloseCursorsOnCommitEnabled') as Value UNION SELECT 'IsFulltextEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsFulltextEnabled') as Value UNION SELECT 'IsInStandBy' as Property, DATABASEPROPERTYEX (@DB, 'IsInStandBy') as Value UNION SELECT 'IsLocalCursorsDefault' as Property, DATABASEPROPERTYEX (@DB, 'IsLocalCursorsDefault') as Value UNION SELECT 'IsMemoryOptimizedElevateToSnapshotEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsMemoryOptimizedElevateToSnapshotEnabled') as Value UNION SELECT 'IsMergePublished' as Property, DATABASEPROPERTYEX (@DB, 'IsMergePublished') as Value UNION SELECT 'IsNullConcat' as Property, DATABASEPROPERTYEX (@DB, 'IsNullConcat') as Value UNION SELECT 'IsNumericRoundAbortEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsNumericRoundAbortEnabled') as Value UNION SELECT 'IsParameterizationForced' as Property, DATABASEPROPERTYEX (@DB, 'IsParameterizationForced') as Value UNION SELECT 'IsQuotedIdentifiersEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsQuotedIdentifiersEnabled') as Value UNION SELECT 'IsPublished' as Property, DATABASEPROPERTYEX (@DB, 'IsPublished') as Value UNION SELECT 'IsRecursiveTriggersEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsRecursiveTriggersEnabled') as Value UNION SELECT 'IsSubscribed' as Property, DATABASEPROPERTYEX (@DB, 'IsSubscribed') as Value UNION SELECT 'IsSyncWithBackup' as Property, DATABASEPROPERTYEX (@DB, 'IsSyncWithBackup') as Value UNION SELECT 'IsTornPageDetectionEnabled' as Property, DATABASEPROPERTYEX (@DB, 'IsTornPageDetectionEnabled') as Value UNION SELECT 'IsVerifiedClone' as Property, DATABASEPROPERTYEX (@DB, 'IsVerifiedClone') as Value UNION SELECT 'IsXTPSupported' as Property, DATABASEPROPERTYEX (@DB, 'IsXTPSupported') as Value UNION SELECT 'LastGoodCheckDbTime' as Property, DATABASEPROPERTYEX (@DB, 'LastGoodCheckDbTime') as Value UNION SELECT 'LCID' as Property, DATABASEPROPERTYEX (@DB, 'LCID') as Value UNION SELECT 'MaxSizeInBytes' as Property, DATABASEPROPERTYEX (@DB, 'MaxSizeInBytes') as Value UNION SELECT 'Recovery' as Property, DATABASEPROPERTYEX (@DB, 'Recovery') as Value UNION SELECT 'ServiceObjective' as Property, DATABASEPROPERTYEX (@DB, 'ServiceObjective') as Value UNION SELECT 'ServiceObjectiveId' as Property, DATABASEPROPERTYEX (@DB, 'ServiceObjectiveId') as Value UNION SELECT 'SQLSortOrder' as Property, DATABASEPROPERTYEX (@DB, 'SQLSortOrder') as Value UNION SELECT 'Status' as Property, DATABASEPROPERTYEX (@DB, 'Status') as Value UNION SELECT 'Updateability' as Property, DATABASEPROPERTYEX (@DB, 'Updateability') as Value UNION SELECT 'UserAccess' as Property, DATABASEPROPERTYEX (@DB, 'UserAccess') as Value UNION SELECT 'Version' as Property, DATABASEPROPERTYEX (@DB, 'Version') as Value DECLARE @props TABLE (propertyname sysname PRIMARY KEY) INSERT INTO @props(propertyname) SELECT 'BuildClrVersion' UNION SELECT 'Collation' UNION SELECT 'CollationID' UNION SELECT 'ComparisonStyle' UNION SELECT 'ComputerNamePhysicalNetBIOS' UNION SELECT 'Edition' UNION SELECT 'EditionID' UNION SELECT 'EngineEdition' UNION SELECT 'InstanceName' UNION SELECT 'IsClustered' UNION SELECT 'IsFullTextInstalled' UNION SELECT 'IsIntegratedSecurityOnly' UNION SELECT 'IsSingleUser' UNION SELECT 'LCID' UNION SELECT 'LicenseType' UNION SELECT 'MachineName' UNION SELECT 'NumLicenses' UNION SELECT 'ProcessID' UNION SELECT 'ProductVersion' UNION SELECT 'ProductLevel' UNION SELECT 'ResourceLastUpdateDateTime' UNION SELECT 'ResourceVersion' UNION SELECT 'ServerName' UNION SELECT 'SqlCharSet' UNION SELECT 'SqlCharSetName' UNION SELECT 'SqlSortOrder' UNION SELECT 'SqlSortOrderName' UNION SELECT 'FilestreamShareName' UNION SELECT 'FilestreamConfiguredLevel' UNION SELECT 'FilestreamEffectiveLevel' SELECT propertyname, SERVERPROPERTY(propertyname) FROM @props   MySQL - Bag Of Tricks Introduction This document has many useful command. MySql Kill users You can use the following syntax to lookup users within a database SHOW PROCESSLIST; SELECT group_concat(concat('KILL ',id,';') SEPARATOR ' \n') FROM information_schema.processlist WHERE `db` LIKE 'ashe%' AND `user` ='kiwilive'; Then cut and paste into a query window Get all database names from a full dump file grep -E "^-- Current Database:" all.sql You can do the extract this way with one line if you have a prefix of the database names awk '/^-- Current Database: `valley_/ { out = substr($4, 2, length($4)-2) ".sql"; print "Extracting to " out } out { print > out } /^-- Current Database: `[^v]/ { out = "" }' all.sql   MsSQL - SQL Reporting Services Enable Errors Connect to the database engine in SSMS and navigate to the ReportServer database. Query the ConfigurationInfo table to get familiar with it. Issue the following query: USE ReportServer GO UPDATE ConfigurationInfo SET Value = 'True' WHERE Name = 'EnableRemoteErrors' Restart Reporting Services on the server: Click Start > Administrative Tools > Services to open the Services management console. Right-click the SQL Server Reporting Services ([InstanceName]) service, and then click Restart. MsSQL - Move TempDB To Another Drive Sometimes as a database administrator, you need to move the TempDB database and log files to a new hard drive. This happens for example if you have installed SQL Server on C:\ and you no longer have space to process your queries. So it is necessary to move TempDB to a disk with more free space. This article explains all the steps to move TempDB files. Steps to move TempDB and log files to new location Here are the steps to move SQL Server temporary database : Identify the location of TempDB data and log files Change the location of TempDB data and log files using ALTER DATABASE Stop and restart the SQL Server service Check path change Delete old TempDB as well as .mdf and .ldf files Identify the location of TempDB data and log files In the query window of SQL Server Management Studio, run the script below to identify the location of the TempDB data and log file: Use master GO SELECT name AS [LogicalName] ,physical_name AS [Location] ,state_desc AS [Status] FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); GO This query shows us the presence of the TempDB in the default folder of the SQL Server installation : TempDB in the default folder Once you have identified the location of the TempDB files, the next step will be to create folders on the new hard drive where you want to store the TempDB data and log file. However, you must ensure that the new location where the TempDB files are stored is accessible by SQL Server. That is, you must ensure that the account under which the SQL Server service is running has read and write permissions to the folder where the files are stored. Change Location of TempDB Data Files and Log Files Using ALTER DATABASE Run the ALTER DATABASE command below to change the TempDB data and log file location in SQL Server: USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf'); GO Tip if you have several tempDB files It happens that sometimes we have several tempDB databases to move, as shown in our example above. Instead of doing several copy / paste, a small script to generate all TempDB move requests: SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],' + ' FILENAME = ''Z:\MSSQL\DATA\' + f.name + CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END + ''');' FROM sys.master_files f WHERE f.database_id = DB_ID(N'tempdb'); The result is: generate all TempDB move requests A simple copy / paste of all the queries to execute them at once! Stop and restart the SQL Server service Stop and restart the instance of SQL Server for the changes to take effect. Verify Changed Location of TempDB Data Files and Log Files All you have to do is re-execute the very first query. Results: Delete old tempdb.mdf and templog.ldf files The final step will be to delete the tempdb.mdf and templog.ldf files from the original location. So just go there to the location and delete them with the DELETE key on your keyboard or with the right mouse button. Note : SQL Server does not support moving the TempDB database using backup/restore and using database detach methods. The only way to do this is by Transact SQL code as shown above. MsSQL - Set All User Tables to Simple on DEV/TEST Server /* This script is to set all of the user database to simple on a DEV/TEST server Use the @Update and set to 1 for updating or 0 default to ready only Created By: Steve Ling 2024/09/05 */ USE MASTER declare @isql varchar(2000), @dbname varchar(64), @logfile varchar(128), @Update bit Set @Update = 0 --0=Ready Only, 1=Update Databases declare c1 cursor for SELECT d.name, mf.name as logfile--, physical_name AS current_file_location, size FROM sys.master_files mf inner join sys.databases d on mf.database_id = d.database_id where recovery_model_desc <> 'SIMPLE' and d.name not in ('master','model','msdb','tempdb','DB_Administration','DWConfiguration','DWDiagnostics','DWQueue') and mf.type_desc = 'LOG' open c1 fetch next from c1 into @dbname, @logfile While @@fetch_status <> -1 begin select @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE' print @isql If @Update = 1 exec(@isql) select @isql='USE ' + @dbname + ' checkpoint' print ' '+@isql If @Update = 1 exec(@isql) select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)' print ' '+@isql If @Update = 1 exec(@isql) fetch next from c1 into @dbname, @logfile end close c1 deallocate c1 MsSQL - Export Stored Procedures and Views to Files Use the Generate Scripts tool in SSMS: Right Click Database in Object Explorer. Tasks -> Generate Scripts. If given the "tutorial" click Next. Select "Select specific database objects" and tick "Stored Procedures". Click Next. Choose export method. Likely here you want "Save as script file" with "one script file per object" selected. Ensure you choose the export location. Click Next and Finish buttons as required. ESP - Delete Agent Requests For Users This removes failed jobs requests after 1 month that users are not cleaning up themselves. /* This script will clean up the agent errors that we are not looking for every day. Please run in SQLAgent every day. Created Steven F Ling 2021-06-22 */ --select * from agtRequest where status=3; delete from agtRequest where status=3 and errordescription like '%email%' or errordescription like '%exited%' or errordescription like 'The docket number%' or errordescription like '%KDG: Error%' or errordescription like 'A connect%' or errordescription like '%No available document rules%' and cast(actiondate as date) <= cast(dateadd(m,-1, getdate()) as date);   MsSQL - Restore Master/Model/MSDB Steps to Restore the  master Database: Stop the SQL Server Instance: Open SQL Server Configuration Manager or Windows Services. Locate the SQL Server service (e.g.,  SQL Server (MSSQLSERVER)  for a default instance). Stop the service. Start SQL Server in Single-User Mode: Open the SQL Server service properties (e.g., by double-clicking the service in Services). Go to the "Startup Parameters" tab. Add  -m  (for single-user mode) to the existing startup parameters.  If there are existing parameters, separate them with a semicolon. Start the SQL Server service from there Connect using  sqlcmd : Open a new Command Prompt window as Administrator. Navigate to the SQL Server Binn directory (e.g.,  C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Binn ). Connect to the SQL Server instance using  sqlcmd  in single-user mode.  For a default instance, use: sqlcmd -S . -E -d master sqlcmd -S . -U sa - P PASSWORD -d master   For a named instance, replace .   with  .\ Restore the  master  Database: In the  sqlcmd  prompt, execute the following  RESTORE DATABASE  command, replacing   with the actual path to your  master  database backup file: RESTORE DATABASE master FROM DISK = '\\192.168.1.61\Backups\UNIBCESRV02\master\FULL\UNIBCESRV02_master_FULL_20250910_010011.bak' WITH REPLACE, RECOVERY; RESTORE DATABASE model FROM DISK = '\\192.168.1.61\Backups\UNIBCESRV02\model\FULL\UNIBCESRV02_model_FULL_20250910_010013.bak' WITH REPLACE, RECOVERY; RESTORE DATABASE msdb FROM DISK = '\\192.168.1.61\Backups\UNIBCESRV02\msdb\FULL\UNIBCESRV02_msdb_FULL_20250910_010014.bak' WITH REPLACE, RECOVERY; Check integrity DBCC CHECKDB ('master') WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS; The  WITH REPLACE  option is crucial as it overwrites the existing  master  database. The  RECOVERY  option brings the database online after the restore. Restart SQL Server in Normal Mode: After the restore completes and  sqlcmd  indicates the instance is shutting down, stop the SQL Server service again. Remove the  -m  startup parameter from the SQL Server service properties. Start the SQL Server service normally. MsSQL - Restore Agent Jobs from Another MSDB database This is to copy by specific Job --- ErangaMSDB is the copy of the old msdb --- DECLARE @JobID UNIQUEIDENTIFIER DECLARE @SchedulerID Int SELECT @JobID = job_id FROM ErangaMSDB.dbo.sysjobs WHERE NAME='Restore IPay' --- Insert the jobs INSERT msdb.dbo.sysjobs SELECT * FROM ErangaMSDB.dbo.sysjobs WHERE job_id=@JobID --Insert the steps INSERT msdb.dbo.sysjobsteps SELECT * FROM ErangaMSDB.dbo.sysjobsteps WHERE job_id=@JobID --Insert the job history SET IDENTITY_INSERT msdb.dbo.sysjobhistory ON INSERT msdb.dbo.sysjobhistory (instance_id,job_id,step_id,step_name,sql_message_id,sql_severity, [message],run_status,run_date,run_time,run_duration,operator_id_emailed, operator_id_netsent,operator_id_paged,retries_attempted,[server]) SELECT instance_id,job_id,step_id,step_name,sql_message_id,sql_severity, [message],run_status,run_date,run_time,run_duration,operator_id_emailed, operator_id_netsent,operator_id_paged,retries_attempted,[server] FROM ErangaMSDB.dbo.sysjobhistory WHERE job_id=@JobID SET IDENTITY_INSERT msdb.dbo.sysjobhistory OFF --Insert the schedules -- For the schedule - If more than 1 schedule, should add : WHERE schedule_id in (number1,number2) SET IDENTITY_INSERT msdb.dbo.sysschedules ON INSERT INTO msdb.dbo.sysschedules ( [schedule_id],[schedule_uid],[originating_server_id],[name],[owner_sid] ,[enabled],[freq_type],[freq_interval],[freq_subday_type],[freq_subday_interval] ,[freq_relative_interval],[freq_recurrence_factor],[active_start_date],[active_end_date] ,[active_start_time],[active_end_time],[date_created],[date_modified],[version_number] ) SELECT [schedule_id],[schedule_uid],[originating_server_id],[name],[owner_sid] ,[enabled],[freq_type],[freq_interval],[freq_subday_type],[freq_subday_interval] ,[freq_relative_interval],[freq_recurrence_factor],[active_start_date],[active_end_date] ,[active_start_time],[active_end_time],[date_created],[date_modified],[version_number] FROM ErangaMSDB.dbo.sysschedules WHERE schedule_id =@SchedulerID INSERT msdb.dbo.sysjobschedules SELECT * FROM ErangaMSDB.dbo.sysjobschedules WHERE job_id=@JobID Came from Here This is to copy all jobs /**************************************Script to generate all the Jobs on a server **************************************/--CreatedBy - Amit Mathur (v-amat) --CreatedDate - 08/26/2009 /**************************************Script to generate all the Jobs on a server **************************************/SET NOCOUNT ON BEGIN TRY PRINT 'USE [msdb]' PRINT 'GO' PRINT '' DECLARE @JobID nvarchar(100),         @JobName varchar (128),         @JobCategory varchar (128),         @JobCategoryClass varchar(128),         @Now datetime,         @Nowtext varchar(30)     SELECT @Now = GETDATE()                  SELECT @Nowtext = CAST(@Now as varchar(30)) CREATE TABLE #Jobs (id int identity (1,1), jobid varchar(50)) INSERT INTO #Jobs (jobid) SELECT jobid = convert(varchar(50),job_id) FROM msdb.dbo.SysJobs WITH (NOLOCK) DECLARE @MaxJobs int,         @JobControl int SELECT @JobControl = 1 SELECT @MaxJobs = MAX(id) FROM #jobs --Create Jobs by looping through all the existing jobs on the server WHILE (@JobControl <= @MaxJobs) BEGIN --BEGIN Jobs     SELECT @JobID = JobID FROM #jobs WHERE id = @JobControl     SELECT @JobName = name FROM msdb.dbo.sysjobs_view WHERE Job_ID = @JobID     SELECT @JobCategory =    sc.name, @JobCategoryClass = category_class FROM msdb.dbo.sysjobs sj                             INNER JOIN msdb.dbo.syscategories sc                             ON sc.category_id = sj.category_id                             WHERE Job_ID = @JobID     PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'     PRINT 'IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''' + @JobName + ''')'     PRINT 'EXEC msdb.dbo.sp_delete_job @job_name= N''' + @JobName + ''''+ ', @delete_unused_schedule=1'     PRINT 'GO'     PRINT ''     PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'     PRINT 'BEGIN TRANSACTION'     PRINT 'DECLARE @ReturnCode INT'     PRINT 'SELECT @ReturnCode = 0'     PRINT '/****** Object: JobCategory ' + QUOTENAME(@JobCategory) + ' Script Date:' + @Nowtext + ' ******/'     PRINT 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = N''' + @JobCategory + ''' AND category_class = ' + @JobCategoryClass+ ')'     PRINT 'BEGIN'     PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name = N''' + @JobCategory + ''''     PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'     PRINT ''     PRINT 'END'     PRINT ''     PRINT 'DECLARE @jobId BINARY(16)'     PRINT ''     DECLARE @enabled int,             @notify_level_eventlog int,             @notify_level_email int,             @notify_level_netsend int,             @notify_level_page int,             @delete_level int,             @description nvarchar(128),             @category_name nvarchar(128),             @owner_login_name nvarchar(128),             @notify_email_operator_name nvarchar(128)     SELECT     @enabled = sj.enabled,             @notify_level_eventlog = sj.notify_level_eventlog,             @notify_level_email = sj.notify_level_email,             @notify_level_netsend = sj.notify_level_netsend,             @notify_level_page = sj.notify_level_page,             @delete_level = sj.delete_level,             @description = sj.[description],             @category_name = sc.name,             @owner_login_name = SUSER_NAME(sj.owner_sid),             @notify_email_operator_name = so.name     FROM msdb.dbo.sysjobs sj     INNER JOIN msdb.dbo.syscategories sc         ON sc.category_id = sj.category_id     LEFT OUTER JOIN msdb.dbo.sysoperators so         ON sj.notify_email_operator_id = so.id         WHERE Job_ID = @JobID     PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N''' + @JobName + ''','     PRINT '        @enabled=' + CAST(@enabled as varchar(30))+ ','     PRINT '        @notify_level_eventlog=' + CAST(@notify_level_eventlog as varchar(30))+ ','     PRINT '        @notify_level_email=' + CAST(@notify_level_email as varchar(30))+ ','     PRINT '        @notify_level_netsend=' + CAST(@notify_level_netsend as varchar(30))+ ','     PRINT '        @notify_level_page=' + CAST(@notify_level_page as varchar(30))+ ','     PRINT '        @delete_level=' + CAST(@delete_level as varchar(30))+ ','     PRINT '        @description=N''' + REPLACE(@description, '''','''''') + ''','     PRINT '        @category_name=N''' + @category_name + ''','     PRINT '        @owner_login_name=N''' + ISNULL(@owner_login_name,'sa') + ''',' IF @notify_email_operator_name IS NOT NULL BEGIN     PRINT '        @notify_email_operator_name=N''' + @notify_email_operator_name + ''', @job_id = @JobID OUTPUT' END ELSE     BEGIN     PRINT '        @job_id = @JobID OUTPUT'     END     PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'     PRINT ''     --CREATE STEPS     DECLARE @MaxSteps int,             @LoopControl int     SELECT @LoopControl = 1     SELECT @MaxSteps = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID     WHILE (@LoopControl <= @MaxSteps)         BEGIN             DECLARE @step_name nvarchar (128),                     @step_id int,                     @cmdexec_success_code int,                     @on_success_action int,                     @on_success_step_id int,                     @on_fail_action int,                     @on_fail_step_id int,                     @retry_attempts int,                     @retry_interval int,                     @os_run_priority int,                     @subsystem nvarchar (128),                     @command nvarchar (max),                     @database_name nvarchar(128),                     @flags int             SELECT     @step_name = step_name,                     @step_id = step_id,                     @cmdexec_success_code = cmdexec_success_code,                     @on_success_action = on_success_action,                     @on_success_step_id = on_success_step_id,                     @on_fail_action = on_fail_action,                     @on_fail_step_id = on_fail_step_id,                     @retry_attempts = retry_attempts,                     @retry_interval = retry_interval,                     @os_run_priority = os_run_priority,                     @subsystem = subsystem,                     @command = command,                     @database_name = database_name,                     @flags = flags             FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID                     AND step_id = @LoopControl             PRINT ''                 PRINT '/****** Object: Step ' + @step_name + ' Script Date: ' + @Nowtext + '******/'             PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''' + @step_name + ''','             PRINT '        @step_id=' + CAST(@step_id as varchar(30))+ ','             PRINT '        @cmdexec_success_code=' + CAST(@cmdexec_success_code as varchar(30))+ ','             PRINT '        @on_success_action=' + CAST(@on_success_action as varchar(30))+ ','             PRINT '        @on_success_step_id=' + CAST(@on_success_step_id as varchar(30))+ ','             PRINT '        @on_fail_action=' + CAST(@on_fail_action as varchar(30))+ ','             PRINT '        @on_fail_step_id=' + CAST(@on_fail_step_id as varchar(30))+ ','             PRINT '        @retry_attempts=' + CAST(@retry_attempts as varchar(30))+ ','             PRINT '        @retry_interval=' + CAST(@retry_interval as varchar(30))+ ','             PRINT '        @os_run_priority=' + CAST(@os_run_priority as varchar(30))+ ', @subsystem=N''' + @subsystem + ''','             PRINT '        @command=N''' + REPLACE(@command, '''','''''') + ''','             PRINT '        @database_name=N''' + @database_name + ''','             PRINT '        @flags=' + CAST(@flags as varchar(30))             PRINT ''             SELECT @LoopControl = @LoopControl + 1         END -- End Steps While     PRINT ''     PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'     PRINT 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1'     PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'          PRINT ''          --CREATE SCHEDULES     DECLARE @MaxSchedules int,             @SchedulesLoopControl int     SELECT @SchedulesLoopControl = 1     CREATE TABLE #Schedules (id int identity (1,1), schedule_id int)     INSERT INTO #Schedules (schedule_id) SELECT schedule_id = sjs.schedule_id     FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)     --INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id     WHERE sjs.Job_ID = @JobID     SELECT @MaxSchedules = MAX(id) FROM #Schedules          IF EXISTS (SELECT COUNT(*) FROM #Schedules)     BEGIN              WHILE (@SchedulesLoopControl <= @MaxSchedules)             BEGIN                 DECLARE @name nvarchar(2000),                         @sch_enabled int,                         @freq_type int,                         @freq_interval int,                         @freq_subday_type int,                         @freq_subday_interval int,                         @freq_relative_interval int,                         @freq_recurrence_factor int,                         @active_start_date int,                         @active_end_date int,                         @active_start_time int,                         @active_end_time int,                         @schedule_uid nvarchar (50)                 SELECT     @name = name,                         @sch_enabled = enabled,                         @freq_type = freq_type,                         @freq_interval = freq_interval,                         @freq_subday_type = freq_subday_type,                         @freq_subday_interval = freq_subday_interval,                         @freq_relative_interval = freq_relative_interval,                         @freq_recurrence_factor = freq_recurrence_factor,                         @active_start_date = active_start_date,                         @active_end_date = active_end_date,                         @active_start_time = active_start_time,                         @active_end_time = active_end_time,                         @schedule_uid = schedule_uid                         FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)                         INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id                         INNER JOIN #Schedules s ON ss.schedule_id = s.schedule_id                         WHERE sjs.Job_ID = @JobID                         AND s.id = @SchedulesLoopControl                 PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''' + REPLACE(@name, '''','''''') + ''','                 PRINT '        @enabled=' + CAST(@sch_enabled as varchar(30))+ ','                 PRINT '        @freq_type=' + CAST(@freq_type as varchar(30))+ ','                 PRINT '        @freq_interval=' + CAST(@freq_interval as varchar(30))+ ','                 PRINT '        @freq_subday_type=' + CAST(@freq_subday_type as varchar(30))+ ','                 PRINT '        @freq_subday_interval=' + CAST(@freq_subday_interval as varchar(30))+ ','                 PRINT '        @freq_relative_interval=' + CAST(@freq_relative_interval as varchar(30))+ ','                 PRINT '        @freq_recurrence_factor=' + CAST(@freq_recurrence_factor as varchar(30))+ ','                 PRINT '        @active_start_date=' + CAST(@active_start_date as varchar(30))+ ','                 PRINT '        @active_end_date=' + CAST(@active_end_date as varchar(30))+ ','                 PRINT '        @active_start_time=' + CAST(@active_start_time as varchar (30)) + ','                 PRINT '        @active_end_time=' + CAST(@active_end_time as varchar (30)) + ','                 PRINT '        @schedule_uid=N''' + @schedule_uid + ''''                 PRINT ''                 PRINT ''                 PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'                 PRINT ''                                  SELECT @SchedulesLoopControl = @SchedulesLoopControl + 1             END -- End Schedules While loop     END -- END IF (SELECT COUNT(*) FROM #Schedules) > 0              DECLARE @server_name varchar(30)     SELECT @server_name = CASE server_id WHEN 0 THEN 'local' ELSE 'Multi-Server' END     FROM msdb.dbo.sysjobservers WHERE Job_ID = @JobID     PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =N''(' + @server_name + ')'''     PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'     PRINT 'COMMIT TRANSACTION'     PRINT 'GOTO EndSave'     PRINT 'QuitWithRollback:'     PRINT ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'     PRINT 'EndSave:'     PRINT ''     PRINT 'GO'     PRINT ''     PRINT ''              SELECT @JobControl = @JobControl + 1     DROP TABLE #Schedules END --End Jobs DROP TABLE #Jobs END TRY BEGIN CATCH     DROP TABLE #Jobs     DROP TABLE #Schedules END CATCH; Came from here SQL - Try Catch Rollback BEGIN TRY BEGIN TRANSACTION; -- SQL statements that are part of the transaction -- e.g., INSERT, UPDATE, DELETE statements COMMIT TRANSACTION; END TRY BEGIN CATCH -- Error handling logic IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; -- Optional: Log error details, raise error, or perform other actions SELECT ERROR_MESSAGE() AS ErrorMessage, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_LINE() AS ErrorLine, ERROR_PROCEDURE() AS ErrorProcedure; -- Optional: Re-throw the error -- THROW; END CATCH;   SQL - Blocking Process These scripts are using to help how to determine what is blocking process are occurring. SELECT text,* FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE DB_NAME(database_id) = '[dBName]' AND blocking_session_id <> 0 SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id() ORDER BY L.request_session_id; GO SELECT * FROM sys.dm_exec_input_buffer (66,0); GO WITH cteBL (session_id, blocking_these) AS (SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s CROSS APPLY (SELECT isnull(convert(varchar(6), er.session_id),'') + ', ' FROM sys.dm_exec_requests as er WHERE er.blocking_session_id = isnull(s.session_id ,0) AND er.blocking_session_id <> 0 FOR XML PATH('') ) AS x (blocking_these) ) SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these , batch_text = t.text, input_buffer = ib.event_info, * FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id INNER JOIN cteBL as bl on s.session_id = bl.session_id OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib WHERE blocking_these is not null or r.blocking_session_id > 0 ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id; GO WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time ,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage ,session_cpu_time,session_reads,session_writes,session_logical_reads ,percent_complete,est_completion_time,request_start_time,request_status,command ,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle ,session_status,group_id,query_hash,query_plan_hash) AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type' , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type' , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads' , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads' , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time' , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash FROM sys.dm_exec_sessions AS sess LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id ) , cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms, wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level]) AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level] FROM cteHead AS head WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0) AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0) UNION ALL SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type, blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset, h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1 FROM cteHead AS blocked INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null ) SELECT bh.*, txt.text AS blocker_query_or_most_recent_query FROM cteBlockingHierarchy AS bh OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt; GO SELECT [s_tst].[session_id], [database_name] = DB_NAME (s_tdt.database_id), [s_tdt].[database_transaction_begin_time], [sql_text] = [s_est].[text] FROM sys.dm_tran_database_transactions [s_tdt] INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id] INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id] CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]; GO Links Microsoft MySQL - Cannot use select on user table ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Looks like database is corrupted. Try the following workaround: 1. Insert missing privileges: Code: INSERT INTO `tables_priv` (`Host`, `Db`, `User`, `Table_name`, `Grantor`, `Timestamp`, `Table_priv`, `Column_priv`) VALUES ('localhost','mysql','mariadb.sys','global_priv','root@localhost','0000-00-00 00:00:00','Select,Delete',''); 2. Restart MariaDB to apply security changes: systemctl restart mariadb 3. Verify that select works now: mysql select * from mysql.user; Postgres - Install on Redhat 9 **To install the `pgvector` extension** (the popular open-source vector similarity search extension for PostgreSQL, which provides the `vector` data type and operators) on **Red Hat Enterprise Linux (RHEL) 9** with **PostgreSQL 16**, follow these steps. **`vector.control`** is the control file that comes bundled with the `pgvector` extension — it's not a separate extension. The extension name is simply **`vector`**. ### Recommended Method: Install via PGDG Repository (Easiest & Recommended) The official PostgreSQL Yum Repository (PGDG) provides pre-built RPM packages for `pgvector` on RHEL 9. 1. **Add the official PostgreSQL repository** (if not already added):    ```bash    sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm    ``` 2. **Disable the built-in RHEL PostgreSQL module** (to avoid conflicts):    ```bash    sudo dnf -qy module disable postgresql    ``` 3. **Install PostgreSQL 16** (if not already installed):    ```bash    sudo dnf install -y postgresql16-server postgresql16-devel    ```    - Initialize the database (only if this is a fresh install):      ```bash      sudo /usr/pgsql-16/bin/postgresql-16-setup initdb      sudo systemctl enable --now postgresql-16      ``` 4. **Install pgvector for PostgreSQL 16**:    ```bash    sudo dnf install -y pgvector_16    ```    This installs the extension files (including `vector.control`, `vector--*.sql`, and `vector.so`) into the correct PostgreSQL 16 directories. ### Alternative: Compile from Source (if you prefer or need a specific version) If the RPM is not available or you want the latest development version: 1. Install dependencies:    ```bash    sudo dnf install -y git make gcc postgresql16-devel    ``` 2. Clone and build:    ```bash    cd /tmp    git clone --branch v0.8.1 https://github.com/pgvector/pgvector.git    cd pgvector    make    sudo make install    ```    (Use the latest tag from https://github.com/pgvector/pgvector/releases instead of `v0.8.1` if desired.) ### Enable the Extension in Your Database 1. Connect to your PostgreSQL 16 database (as superuser):    ```bash    sudo -u postgres psql    ```    Or connect to a specific database:    ```bash    psql -U postgres -d your_database_name    ``` 2. Create the extension (run this in every database where you want to use vectors):    ```sql    CREATE EXTENSION IF NOT EXISTS vector;    ``` 3. Verify it's installed:    ```sql    \dx    ```    You should see `vector` listed.    Or check the version:    ```sql    SELECT vector_version();    ``` ### Troubleshooting Tips - If you get `could not load library "/usr/pgsql-16/lib/vector.so"`, verify the file exists and has correct permissions. - If using a custom PostgreSQL install, ensure `pg_config` points to the right version. - For multiple PostgreSQL versions, always use the `_16` suffix in package names (e.g., `pgvector_16`). This should get you up and running with vector support on PostgreSQL 16 on RHEL 9. Let me know if you run into any errors! MySQL - my.cnf Tweaking performance_schema = ON tmpdir = /run/mariadb thread_cache_size = 4 table_open_cache = 16384 table_definition_cache = 8384 sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION query_cache_type = 0 query_cache_size = 0 query_cache_limit = 128M query_cache_strip_comments = 1 tmp_table_size = 512M max_heap_table_size = 512M max_connections = 750 max_allowed_packet = 24M sort_buffer_size = 24M join_buffer_size = 48M innodb_buffer_pool_size = 65G # 65-75% of RAM for InnoDB cache --> Keeping some for VUE innodb_buffer_pool_instances = 10 # One instance per CPU core --> Keeping some for VUE innodb_flush_method = O_DIRECT # Reduce I/O overhead innodb_flush_log_at_trx_commit = 2 # Balance performance and durability innodb_log_buffer_size = 16M # Buffer for transaction logs innodb_thread_concurrency = 0 # Let MySQL manage threads (0 = unlimited) innodb_io_capacity = 2000 # Adjust based on storage IOPS innodb_io_capacity_max = 4000 # Max IOPS for bursts innodb_use_native_aio = 1 innodb_flush_log_at_trx_commit = 2 # Balance performance and durability --> Changed from 0 innodb_file_per_table innodb_log_file_size = 2G # Larger logs for better write performance --> Changed from 512 MongoDB - Installation on RedHat 9 Step 1: Configure the MongoDB Repository Create a new repository file named /etc/yum.repos.d/mongodb-org-7.0.repo  using your preferred text editor (like  vi  or  nano ), or by using the  cat  command as shown below. This example uses version 7.0; you can check the  MongoDB repositories for the latest version. cat <