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.
- ESP - Delete Old Messages
- ESP - Database Copy Script
- ESP - Delete User
- ESP - Create Financial Year and Period
- MsSQL - Create Database Table from Code
- ESP - Add Notifications to All Active Companies
- ESP - Delete Product Design in Imported Status
- ESP - Delete Old Message
- SQL - Kill Database Connections
- MsSQL - Backups
- MsSQL - Verify Properties
- MySQL - Bag Of Tricks
- MsSQL - SQL Reporting Services
- MsSQL - Move TempDB To Another Drive
- MsSQL - Set All User Tables to Simple on DEV/TEST Server
ESP - Delete Old Messages
/*
This will delete all old XML messages
and events older then 5 days
SFL 2008-11-29 Created
Status
1 FAILED
3 Sucessfull
6 History
*/
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())
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
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
The 20240104_Create_Backups_Integrity_Index_Check.sql is the version used on ESP SQL servers with the use of a DB_Administration table.
20240104_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
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 :

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:

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