Articles
Change AccountEmail on Account Zones (Requires Edit)
This query requires editing of information before being executed. UPDATE AccountZone
SET EmailAccount = 1
WHERE EmailAccount like 3 ...
Change AccountEmail on DealerZones (Requires Edit)
This query requires editing of information before being executed. UPDATE DealerZone
SET EmailAccount = 1
WHERE EmailAccount like 3
Change DealerEmail on AccountZones (Requires Edit)
This query requires editing of information before being executed. UPDATE AccountZone
SET EmailDealer = 1
WHERE EmailDealer like 3
Change DealerEmail on DealerZones (Requires Edit)
This query requires editing of information before being executed. UPDATE DealerZone
SET EmailDealer = 1
WHERE EmailDealer like 3
Change DST (Requires Edit)
This query requires editing of information before being executed. UPDATE AccountMain
SET DaylightSavingsTime = '3'
Change Enable Option for Days (Requires Edit)
This query requires editing of information before being executed. UPDATE AccountContacts
SET EnableMonday = 0
WHERE AccountID in (SELECT ID FROM AccountMain WHERE GroupID = 569) and BeginDate = '2017-09-05 00:00:00'
UPDATE AccountContacts
S...
Change SerialNo for new customer (Requires Edit)
This query requires editing of information before being executed. UPDATE SystemInfo
SET SerialNo = '9999'
Change TimeZone (Requires Edit)
This query requires editing of information before being executed. UPDATE AccountMain
SET TimeZone = '3'
Change TimeZoneMinutes (Requires Edit)
This query requires editing of information before being executed. UPDATE AccountMain
SET TimeZoneMinutes = 'B'
Clearing Old NOT_LTC_LTO Events
This query requires editing of information before being executed. This works for clearing any invalid event in the dbo.EventSchedule table. Event Type A – NOT Event Type B – LTC/LTO Clearing an old/duplicate NOT signal out of the SQL server- (fo...
Clearing SIMSData (1000 lines per)
DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
BEGIN
-- Delete some small number of rows at a time
DELETE TOP (1000) SIMSData
where ComputerTime < DATEADD(day,-30,GETDATE())
...
Fix Blank Delays
update DelayFIFO
set DelayFIFO.AccountNumber = t.AccountNumber,
DelayFIFO.Class = s.Class,
DelayFIFO.Name = t.PremiseName,
DelayFIFO.ZipCode = a.Zip,
DelayFIFO.GroupID = d.GroupID,
DelayFIFO.BriefDesc = et.BriefDescription
from DelayFIFO u
I...
Function for history (internal use only-not at customer sites)
Create FUNCTION [dbo].[fnNTextToIntTable] (@Data NTEXT) RETURNS @IntTable TABLE([Value] INT NULL) AS BEGIN DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10) SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1 WHILE(@Ptr < @Length) BEGIN ...
How to search and remove auto test timer from zones
These steps will show how to search which zones have an auto-test timer (Schedule Type ‘D’) and how to remove them. NOTE! : You only want to do this when you are certain customer doesn’t use “NOT” timer on zones. TO LOCATE WHICH ZONES HAVE AUTO T...
Query for deleting Account from SIMS (for all tables) (Requires Edit)
DELETE
FROM AccountAddress Where AccountID in (SELECT ID from AccountMain where AccountNumber LIKE '01-011-%')
DELETE
FROM AccountBilling Where AccountID in (SELECT ID from AccountMain where AccountNumber LIKE '01-011-%')
DELETE
FROM Accoun...
Query to run if SQL backup not correct coming from SIMS Transfer
IF EXISTS(SELECT * FROM sys.databases WHERE name='SIMSBAK')
DROP DATABASE [SIMSBAK];
CREATE DATABASE [SIMSBAK];
--*********************
--*******Tables********
--*********************
SELECT * INTO SIMSBAK.dbo.AccessLevels FROM SIMSData.dbo....
Query to show Account Number with Zones
SELECT
(Select AccountNumber from AccountMain where ID = AccountID) AS AccountNumber,
AccountZone.*
FROM [SIMSData1].[dbo].[AccountZone] where TestEvery != 0
Report for Brief comments with no begin date
SELECT AccountNumber
FROM AccountMain
WHERE ID in
(
SELECT AccountID
FROM AccountComment
WHERE CommentType = 'B' and CommentBeginDate = '1980-01-01 00:00:00' and Comment != '')
and AccountNumber NOT LIKE '~%-%-%' ...
Report for Temp comments with no begin date
SELECT AccountNumber
FROM AccountMain
WHERE ID in
(
SELECT AccountID
FROM AccountComment
WHERE CommentType = 'T' and CommentBeginDate = '1980-01-01 00:00:00' and Comment != '')
and AccountNumber NOT LIKE '~%-%-%' ...
Search SQL History for a Specific Account
This query requires editing of Account Number before being executed SELECT*FROM SIMSData.dbo.EventSummary
WHERE
SIMSData.dbo.EventSummary.AccountID IN (SELECT ID FROM SIMSData.dbo.AccountMain WHERE AccountNumber = 'RR-LL-AAAA’) ...
Update accounts that have a locked item in Equipment
Run the below script in SIMS SQL database to clear the account locks in AccountEquipment
UPDATE AccountEquipment
SET Locked = '0'
WHERE Locked = '1'
Which accounts have entries locked in Equipment Page
Customer receives an error that there is a lock on a line item on the Equipment page. This query will show which account(s) has this issue.
Run the below query in SQL
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP ...