Kaseya Community

Upgrading Kaseya 6.0 to 6.2 Failed - Database Schema Configuration

This question has suggested answer(s)

The error appears during the Database Schema Configuration in the browser which starts automatically after the installer completes and reboots.

Pass 1 of 3
Loading keys and indexes 3 of 4


Fatal MessageSys Error: install/loadKeyIndex.sql

 
System Error [0] Message: Error #1505: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.serverInfo' and the index name 'serverInfo_pk'. The duplicate key value is (1).
Could not create constraint. See previous errors.
The statement has been terminated. on line 322.
Execution Duration: 7:25:32 PM - 7:25:40 PM
SQL Command: Text:
/* load all the table keys and indexes */
/* NOTE: You can only ALTER TABLE once for a table in a batch command. If two columns */
/* in the same table need to be altered then the second command needs to go in a different file. */

/* this table has an an index on it so need to drop the index if it still needs resizing */
IF EXISTS (SELECT c.length from sysColumns c LEFT OUTER JOIN sysobjects o ON
o.id=c.id
WHERE c.name=N'emailAddr' AND o.name=N'scriptAssignment' AND c.length=800)
BEGIN
if exists (SELECT name FROM sysindexes WHERE name=N'scriptAssignment.scriptAssignment_emailAddr_ncidx')
DROP INDEX scriptAssignment.scriptAssignment_emailAddr_ncidx;
ALTER TABLE scriptAssignment ALTER COLUMN emailAddr varchar (100) NULL;
END

/*Removes unique constraints from kad canonical columns*/
IF EXISTS (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME=N'kadComputers' AND TABLE_CATALOG=N'ksubscribers' AND CONSTRAINT_TYPE=N'UNIQUE')
BEGIN
DECLARE @compConst nvarchar(200)
SET @compConst = QUOTENAME((
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME=N'kadComputers' AND TABLE_CATALOG=N'ksubscribers' AND CONSTRAINT_TYPE=N'UNIQUE'
))

EXEC(N'ALTER TABLE kadComputers DROP CONSTRAINT ' + @compConst)
END

IF EXISTS (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME=N'kadUsers' AND TABLE_CATALOG=N'ksubscribers' AND CONSTRAINT_TYPE=N'UNIQUE')
BEGIN
DECLARE @userConst nvarchar(200)
SET @userConst = QUOTENAME((
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME=N'kadUsers' AND TABLE_CATALOG=N'ksubscribers' AND CONSTRAINT_TYPE=N'UNIQUE'
))

EXEC(N'ALTER TABLE kadUsers DROP CONSTRAINT ' + @userConst)
END

if exists (select * from sysColumns WHERE name=N'emailAddr' and id=object_id(N'ntEventLog'))
ALTER TABLE ntEventLog DROP COLUMN emailAddr

/* Change sizes of columns that have changed size since inception here */
ALTER TABLE alertSysCheck ALTER COLUMN pollSeconds BIGINT
ALTER TABLE alertSysCheck ALTER COLUMN durSeconds BIGINT
ALTER TABLE managedFiles ALTER COLUMN serverFilePath varchar(1000) NULL
ALTER TABLE kadUsers ALTER COLUMN HomePhone varchar(100) NULL
ALTER TABLE kadUsers ALTER COLUMN Phone varchar(100) NULL
ALTER TABLE kadUsers ALTER COLUMN Pager varchar(100) NULL
ALTER TABLE kadUsers ALTER COLUMN Mobile varchar(100) NULL
ALTER TABLE kadUsers ALTER COLUMN FacsimileTelephoneNumber varchar(100) NULL
ALTER TABLE kadUsers ALTER COLUMN IpPhone varchar(100) NULL
ALTER TABLE rcLog ALTER COLUMN description varchar(2000) NULL
ALTER TABLE machNameTab ALTER COLUMN machGuid int NULL
ALTER TABLE scriptAssignmentReboot ALTER COLUMN emailAddr varchar (100) NULL
ALTER TABLE auditReports ALTER COLUMN displaySettingsStr varchar (200) NULL
ALTER TABLE auditRsltHwDrives ALTER COLUMN manufacturer varchar (100) NULL
ALTER TABLE auditRsltHwDrives ALTER COLUMN productName varchar (100) NULL
ALTER TABLE themefields ALTER COLUMN defaultValue varchar (2000) NULL
ALTER TABLE themefieldvalues ALTER COLUMN currentValue varchar (2000) NULL
ALTER TABLE siteParams ALTER COLUMN serverName varchar (100) NULL
IF ((SELECT ISNUMERIC(ISNULL(defaultAccount,-1)) FROM siteParams)=0)
UPDATE siteParams SET defaultAccount=(SELECT TOP 1 agentGuid FROM machNameTab
WHERE emailAddr=(SELECT CONVERT(varchar,ISNULL(defaultAccount,0)) FROM siteParams))
ALTER TABLE siteParams ALTER COLUMN defaultAccount numeric(26,0) NULL
ALTER TABLE serverInfo ALTER COLUMN serverName varchar (100) NULL
ALTER TABLE adminHistory ALTER COLUMN functionName varchar (500) NULL
ALTER TABLE logsReports ALTER COLUMN eventId varchar (100)
ALTER TABLE users ALTER COLUMN clientType varchar (400)
ALTER TABLE users ALTER COLUMN contactUrl varchar (200) NULL
ALTER TABLE users ALTER COLUMN userWebServer varchar (200) NULL
ALTER TABLE packageParams ALTER COLUMN installSwitchs varchar (400) NULL
-- only convert if defaultAccount is not numeric.
-- If this runs everytime them no errors get returned past this point (for some reason)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_catalog=N'ksubscribers' AND
table_name=N'packageParams' AND column_name=N'defaultAccount' AND data_type=N'varchar')
BEGIN
UPDATE packageParams SET defaultAccount=0 WHERE DATALENGTH(ISNULL(CONVERT(varchar,defaultAccount),N'')) = 0
UPDATE packageParams SET defaultAccount=ISNULL(m.agentGuid,0) FROM packageParams p
LEFT OUTER JOIN machNameTab m ON CONVERT(varchar,p.defaultAccount)=m.machName+N'.'+m.groupName
WHERE ISNUMERIC(p.defaultAccount)=0
ALTER TABLE packageParams ALTER COLUMN defaultAccount numeric(26,0) NULL
UPDATE groupIdPolicy SET defaultAccount=0 WHERE DATALENGTH(ISNULL(CONVERT(varchar,defaultAccount),N'')) = 0
UPDATE groupIdPolicy SET defaultAccount=ISNULL(m.agentGuid,0) FROM groupIdPolicy p
LEFT OUTER JOIN machNameTab m ON CONVERT(varchar,p.defaultAccount)=m.machName+N'.'+m.groupName
WHERE ISNUMERIC(p.defaultAccount)=0
ALTER TABLE groupIdPolicy ALTER COLUMN defaultAccount numeric(26,0) NULL
END
ALTER TABLE alertLog ALTER COLUMN alertEmail varchar (1000) NULL
ALTER TABLE alertLog ALTER COLUMN subject varchar (500) NULL
ALTER TABLE alertLog ALTER COLUMN body varchar (4000) NULL
ALTER TABLE locMissingAlert ALTER COLUMN bulletinId VARCHAR(40) NULL
ALTER TABLE locSpecialTab ALTER COLUMN bulletinId VARCHAR(40) NULL
ALTER TABLE patchCollection ALTER COLUMN bulletinId VARCHAR(40) NULL
ALTER TABLE patchStatus ALTER COLUMN bulletinId VARCHAR(40) NULL
ALTER TABLE servicePacks ALTER COLUMN spBulletinId VARCHAR(40) NULL
ALTER TABLE tempData ALTER COLUMN tempValue VARCHAR(4000) NULL
/* scriptName column no longer used so allow NULL in those columns */
ALTER TABLE scriptAssignment ALTER COLUMN scriptName varchar (260) NULL
ALTER TABLE scriptIf ALTER COLUMN scriptName varchar (260) NULL
ALTER TABLE scriptStatus ALTER COLUMN scriptName varchar (260) NULL
ALTER TABLE scriptThenElse ALTER COLUMN scriptName varchar (260) NULL
IF NOT EXISTS (SELECT
t.name FROM sysColumns s LEFT OUTER JOIN sysTypes t ON t.xusertype=s.xusertype
WHERE (
s.id IN (SELECT id FROM sysobjects WHERE name=N'auditRsltApps')) AND (s.name='size') AND (t.name='int'))
ALTER TABLE auditRsltApps ALTER COLUMN [size] int NULL
ALTER TABLE auditRsltCpu ALTER COLUMN cpuSpeedMHz int NULL
ALTER TABLE auditRsltCpu ALTER COLUMN cpuCount int NULL
ALTER TABLE auditRsltCpu ALTER COLUMN ramMBytes int NULL
ALTER TABLE auditRsltDisks ALTER COLUMN freeMBytes int NULL
ALTER TABLE auditRsltDisks ALTER COLUMN totalMBytes int NULL
ALTER TABLE auditRsltDisks ALTER COLUMN driveLetter varchar (100) NULL
ALTER TABLE auditRsltDisks ALTER COLUMN volumeName varchar (100) NULL
ALTER TABLE ticSummary ALTER COLUMN userEmail varchar (200) NULL
ALTER TABLE adminPasswords ALTER COLUMN adminId numeric(26,0) NOT NULL


/* drop obsolete clustered indexes if they do exist here before possible creating a conflicting clustered primary key */
if exists (SELECT name FROM sysindexes WHERE name=N'machNameTab_groupName_machName_cidx')
DROP INDEX machNameTab.machNameTab_groupName_machName_cidx

/* add in primary keys if they do not exist */
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'appSession'))
alter table appSession
with check add constraint appSession_pk PRIMARY KEY CLUSTERED (appSessionId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'adminAuditDefaults'))
alter table adminAuditDefaults
with check add constraint adminAuditDefaults_pk PRIMARY KEY CLUSTERED (adminId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'adminGroup'))
alter table adminGroup
with check add constraint adminGroup_pk PRIMARY KEY CLUSTERED (adminGroupId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'adminHomeDefaults'))
alter table adminHomeDefaults
with check add constraint adminHomeDefaults_pk PRIMARY KEY CLUSTERED (adminId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'adminIdTab'))
alter table adminIdTab
with check add constraint adminIdTab_pk PRIMARY KEY CLUSTERED (adminId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'adminMachFil'))
alter table adminMachFil
with check add constraint adminMachFil_pk PRIMARY KEY CLUSTERED (adminId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'adminRoleHrs'))
alter table adminRoleHrs
with check add constraint adminRoleHrs_pk PRIMARY KEY CLUSTERED (adminGroupId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'adminSingleMach'))
alter table adminSingleMach
with check add constraint adminSingleMach_pk PRIMARY KEY CLUSTERED (adminId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'alertAction'))
alter table alertAction
with check add constraint alertAction_pk PRIMARY KEY CLUSTERED (id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'alertRegistration'))
alter table alertRegistration
with check add constraint alertRegistration_pk PRIMARY KEY CLUSTERED (alertRegistrationId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'alertTupleWork'))
alter table alertTupleWork
with check add constraint alertTupleWork_pk PRIMARY KEY CLUSTERED (alertTupleWorkId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'auditRsltSmbios'))
alter table auditRsltSmbios
with check add constraint auditRsltSmbios_pk PRIMARY KEY CLUSTERED (Id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'bulletinIdTab'))
alter table bulletinIdTab
with check add constraint bulletinIdTab_pk PRIMARY KEY CLUSTERED (idStr)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'collectionDef'))
alter table collectionDef
with check add constraint collectionDef_pk PRIMARY KEY CLUSTERED (collectionId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'collectionMembers'))
alter table collectionMembers
with check add constraint collectionMembers_pk PRIMARY KEY CLUSTERED (collectionId,agentGuid)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'collectionDefaultsUpdClass'))
alter table collectionDefaultsUpdClass
with check add constraint collectionDefaultsUpdClass_pk PRIMARY KEY CLUSTERED (collectionId,updateClassification)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'collectionDefaultsWuaProd'))
alter table collectionDefaultsWuaProd
with check add constraint collectionDefaultsWuaProd_pk PRIMARY KEY CLUSTERED (collectionId,wuaProductId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'copySettingsDest'))
alter table copySettingsDest
with check add constraint copySettingsDest_pk PRIMARY KEY CLUSTERED (agentGuid)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'eventSetDef'))
alter table eventSetDef
with check add constraint eventSetDef_pk PRIMARY KEY CLUSTERED (eventSetDefId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'eventSetName'))
alter table eventSetName
with check add constraint eventSetName_pk PRIMARY KEY CLUSTERED (eventSetId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'exceptionServicePack'))
alter table exceptionServicePack
with check add constraint exceptionServicePack_pk PRIMARY KEY CLUSTERED (productId,exceptionSvcPackId,osProductId,osServicePackId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'funcList'))
alter table funcList
with check add constraint funcList_pk PRIMARY KEY CLUSTERED (id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'installSwitches'))
alter table installSwitches
with check add constraint installSwitches_pk PRIMARY KEY CLUSTERED (locId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'langCode'))
alter table langCode
with check add constraint langCode_pk PRIMARY KEY CLUSTERED (langCodeId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'langFileMap'))
alter table langFileMap
with check add constraint langFileMap_pk PRIMARY KEY CLUSTERED (langId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'langIdDef'))
alter table langIdDef
with check add constraint langIdDef_pk PRIMARY KEY CLUSTERED (langId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'locIdTab'))
alter table locIdTab
with check add constraint locIdTab_pk PRIMARY KEY CLUSTERED (id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'locWuaTab'))
alter table locWuaTab
with check add constraint locWuaTab_pk PRIMARY KEY CLUSTERED (id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'machNameTab'))
alter table machNameTab
with check add constraint machNameTab_pk PRIMARY KEY CLUSTERED (agentGuid)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'macVendor'))
alter table macVendor
with check add constraint macVendor_pk PRIMARY KEY CLUSTERED (companyId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'monitorAdmin'))
alter table monitorAdmin
with check add constraint monitorAdmin_pk PRIMARY KEY CLUSTERED (adminId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchApprovalPolicy'))
alter table patchApprovalPolicy
with check add constraint patchApprovalPolicy_pk PRIMARY KEY CLUSTERED (collectionId,patchDataId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchApprovalStats'))
alter table patchApprovalStats
with check add constraint patchApprovalStats_pk PRIMARY KEY CLUSTERED (adminId,collectionId,updateClassification)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchApprovalStats2'))
alter table patchApprovalStats2
with check add constraint patchApprovalStats2_pk PRIMARY KEY CLUSTERED (adminId,collectionId,wuaProductId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchCredentialAlert'))
alter table patchCredentialAlert
with check add constraint patchCredentialAlert_pk PRIMARY KEY CLUSTERED (patchCredentialAlertId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchData'))
alter table patchData
with check add constraint patchData_pk PRIMARY KEY CLUSTERED (patchDataId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchDataPartition'))
alter table patchDataPartition
with check add constraint patchDataPartition_pk PRIMARY KEY CLUSTERED (partitionId, patchDataId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchFilters'))
alter table patchFilters
with check add constraint patchFilters_pk PRIMARY KEY CLUSTERED (filterId,colId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchInstPatchStatus'))
alter table patchInstPatchStatus
with check add constraint patchInstPatchStatus_pk PRIMARY KEY CLUSTERED (patchInstPatchStatusId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchInstScriptStatus'))
alter table patchInstScriptStatus
with check add constraint patchInstScriptStatus_pk PRIMARY KEY CLUSTERED (patchInstScriptStatusId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchKbOverride'))
begin
alter table patchKbOverride
with check add constraint patchKbOverride2_pk PRIMARY KEY CLUSTERED (kbArticleId,partitionId)
end
else
begin
if exists (SELECT name FROM sysObjects WHERE xtype=N'PK'
AND parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchKbOverride')
AND name <> 'patchKbOverride2_pk')
begin
DECLARE @VALUE VARCHAR(256);
DECLARE @sqlcmd varchar(1000)
SET @VALUE=(SELECT name FROM sysObjects WHERE xtype=N'PK'
AND parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchKbOverride') )
SET @sqlcmd = 'ALTER TABLE patchKbOverride DROP CONSTRAINT ' + @VALUE

EXEC(@sqlcmd)
end

if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK'
AND parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchKbOverride')
AND name = 'patchKbOverride2_pk')
begin
alter table patchKbOverride with check add constraint patchKbOverride2_pk PRIMARY KEY CLUSTERED (kbArticleId,partitionId)
end
end
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchLocationNotification'))
alter table patchLocationNotification
with check add constraint patchLocationNotification_pk PRIMARY KEY CLUSTERED (patchLocationNotificationId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchLocationOverride'))
alter table patchLocationOverride
with check add constraint patchLocationOverride_pk PRIMARY KEY CLUSTERED (patchLocationOverrideId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchOfcConfig'))
alter table patchOfcConfig
with check add constraint patchOfcConfig_pk PRIMARY KEY CLUSTERED (patchOfcConfigId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchOverride'))
alter table patchOverride
with check add constraint patchOverride_pk PRIMARY KEY CLUSTERED (patchOverrideId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchParams'))
alter table patchParams
with check add constraint patchParams_pk PRIMARY KEY CLUSTERED (agentGuid)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchPolicyAlert'))
alter table patchPolicyAlert
with check add constraint patchPolicyAlert_pk PRIMARY KEY CLUSTERED (patchPolicyAlertId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchScanPrereq'))
alter table patchScanPrereq
with check add constraint patchScanPrereq_pk PRIMARY KEY CLUSTERED (prereqId,cpuArch)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchScanResults'))
alter table patchScanResults
with check add constraint patchScanResults_pk PRIMARY KEY CLUSTERED (patchScanResultsId)
IF NOT EXISTS (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchStatus'))
BEGIN
ALTER TABLE patchStatus
WITH CHECK ADD CONSTRAINT patchStatus_pk PRIMARY KEY CLUSTERED (agentGuid,patchDataId)
END
ELSE
BEGIN
-- Check to see if PKey is non-clustered (indid > 1); If it is, drop it and recreate as clustered
IF EXISTS (SELECT 1 FROM sysindexes WHERE name = 'patchStatus_pk' AND indid > 1)
BEGIN
ALTER TABLE patchStatus DROP CONSTRAINT patchStatus_pk
ALTER TABLE patchStatus WITH CHECK ADD CONSTRAINT patchStatus_pk PRIMARY KEY CLUSTERED (agentGuid,patchDataId)
END
END
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchStatusReports'))
alter table patchStatusReports
with check add constraint patchStatusReports_pk PRIMARY KEY NONCLUSTERED (sessionAdminId,reportKey,agentGuid)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'patchStatusTotals'))
alter table patchStatusTotals
with check add constraint patchStatusTotals_pk PRIMARY KEY CLUSTERED (agentGuid)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'productIdTab'))
alter table productIdTab
with check add constraint productIdTab_pk PRIMARY KEY CLUSTERED (id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'productWuaTab'))
alter table productWuaTab
with check add constraint productWuaTab_pk PRIMARY KEY CLUSTERED (id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'reportsDef'))
alter table reportsDef
with check add constraint reportsDef_pk PRIMARY KEY CLUSTERED (reportID)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'serverInfo'))
alter table serverInfo
with check add constraint serverInfo_pk PRIMARY KEY CLUSTERED (uniqueRowId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'scriptFldr'))
alter table scriptFldr
with check add constraint scriptFldr_pk PRIMARY KEY CLUSTERED (fldrId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'scriptIdTab'))
alter table scriptIdTab
with check add constraint scriptIdTab_pk PRIMARY KEY CLUSTERED (scriptId)
IF NOT EXISTS (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'supersededPatch'))
ALTER TABLE supersededPatch
WITH CHECK ADD CONSTRAINT supersededPatch_pk PRIMARY KEY NONCLUSTERED (supersededById,supersededId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'tempData'))
alter table tempData
with check add constraint tempData_pk PRIMARY KEY CLUSTERED (id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'ticArchSummary'))
alter table ticArchSummary
with check add constraint ticArchSummary_pk PRIMARY KEY CLUSTERED (ID)

--** Creation of index moved to DBInstallCoreindexdbo.ticDuePolicyPK.sql
--if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'ticDuePolicy'))
-- alter table ticDuePolicy
-- with check add constraint ticDuePolicy_pk PRIMARY KEY CLUSTERED (dueId)

if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'ticAssigneePolicy'))
alter table ticAssigneePolicy
with check add constraint ticAssigneePolicy_pk PRIMARY KEY CLUSTERED (policyId)

--** Creation of index moved to DBInstallCoreindexdbo.ticFieldDefPK.sql
--if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'ticFieldDef'))
-- alter table ticFieldDef
-- with check add constraint ticFieldDef_pk PRIMARY KEY CLUSTERED (fieldId)

if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'ticSummary'))
alter table ticSummary
with check add constraint ticSummary_pk PRIMARY KEY CLUSTERED (ID)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'users'))
alter table users
with check add constraint users_pk PRIMARY KEY CLUSTERED (agentGuid)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'viewDef'))
alter table viewDef
with check add constraint viewDef_pk PRIMARY KEY CLUSTERED (viewId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'vproCredentials'))
alter table vproCredentials
with check add constraint vproCredentials_pk PRIMARY KEY CLUSTERED (vproCredentialsId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'vproDevices'))
alter table vproDevices
with check add constraint vproDevices_pk PRIMARY KEY CLUSTERED (vproDeviceId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'vproDisks'))
alter table vproDisks
with check add constraint vproDisks_pk PRIMARY KEY CLUSTERED (vproDiskId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'vproInventory'))
alter table vproInventory
with check add constraint vproInventory_pk PRIMARY KEY CLUSTERED (vproInventoryId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'vproPowerMgmt'))
alter table vproPowerMgmt
with check add constraint vproPowerMgmt_pk PRIMARY KEY CLUSTERED (vproPowerMgmtId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'vproProcessors'))
alter table vproProcessors
with check add constraint vproProcessors_pk PRIMARY KEY CLUSTERED (vproProcessorId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'vproRemoteBoot'))
alter table vproRemoteBoot
with check add constraint vproRemoteBoot_pk PRIMARY KEY CLUSTERED (vproRemoteBootId)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'kadComputers'))
alter table kadComputers
with check add constraint kadComputers_pk PRIMARY KEY CLUSTERED (Id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'kadUsers'))
alter table kadUsers
with check add constraint kadUsers_pk PRIMARY KEY CLUSTERED (Id)
if not exists (SELECT name FROM sysObjects WHERE xtype=N'PK' and parent_obj=(SELECT id FROM sysObjects WHERE name=N'uptimeLog'))
alter table uptimeLog
with check add constraint uptimeLog_pk PRIMARY KEY CLUSTERED (uptimeId)

/* language indexes */
if not exists (SELECT name FROM sysindexes WHERE name=N'langPacShort_baseStr_langId_idx')
CREATE CLUSTERED INDEX langPacShort_baseStr_langId_idx ON langPacShort (baseStr, langId)

if not exists (SELECT name FROM sysindexes WHERE name=N'scriptIdTab_scriptName_idx')
CREATE INDEX scriptIdTab_scriptName_idx ON scriptIdTab (scriptName)
if not exists (SELECT name FROM sysindexes WHERE name=N'scriptIdTab_dynamicCols_idx')
CREATE INDEX scriptIdTab_dynamicCols_idx ON scriptIdTab (dynamicForAgentGuid, dynamicScriptType)


/* scriptAssignment indexes */
if not exists (SELECT name FROM sysindexes WHERE name=N'scriptAssignment_scriptId_idx')
CREATE INDEX scriptAssignment_scriptId_idx ON scriptAssignment (scriptId)
--if not exists (SELECT name FROM sysindexes WHERE name=N'scriptAssignment_id_ncidx')
-- CREATE NONCLUSTERED INDEX scriptAssignment_id_ncidx ON scriptAssignment (id)

if not exists (SELECT name FROM sysindexes WHERE name=N'dlMaint_lowIp_clust_idx')
CREATE CLUSTERED INDEX dlMaint_lowIp_clust_idx ON dlMaint (lowIp)

/* create clustered index to speed up order by machine names when using LEFT OUTER JOIN */
if exists (SELECT name FROM sysindexes WHERE name=N'machNameTab_groupName_ncidx')
DROP INDEX machNameTab.machNameTab_groupName_ncidx
if exists (SELECT name FROM sysindexes WHERE name=N'machNameTab_machName_ncidx')
DROP INDEX machNameTab.machNameTab_machName_ncidx
if not exists (SELECT name FROM sysindexes WHERE name=N'machNameTab_groupName_machName_ncidx')
CREATE NONCLUSTERED INDEX machNameTab_groupName_machName_ncidx ON machNameTab (groupName, machName)

if not exists (SELECT name FROM sysindexes WHERE name=N'adminLog_eventTime_cidx')
CREATE CLUSTERED INDEX adminLog_eventTime_cidx ON adminLog (eventTime)
if exists (SELECT name FROM sysindexes WHERE name=N'scriptIf_scriptId_idx')
DROP INDEX scriptIf.scriptIf_scriptId_idx
if exists (SELECT name FROM sysindexes WHERE name=N'scriptIf_scriptId_ncidx')
DROP INDEX scriptIf.scriptIf_scriptId_ncidx
if not exists (SELECT name FROM sysindexes WHERE name=N'scriptIf_scriptId_cidx')
CREATE CLUSTERED INDEX scriptIf_scriptId_cidx ON scriptIf (scriptId)
if exists (SELECT name FROM sysindexes WHERE name=N'scriptThenElse_scriptId_idx')
DROP INDEX scriptThenElse.scriptThenElse_scriptId_idx
if exists (SELECT name FROM sysindexes WHERE name=N'scriptThenElse_scriptId_ncidx')
DROP INDEX scriptThenElse.scriptThenElse_scriptId_ncidx
--if not exists (SELECT name FROM sysindexes WHERE name=N'scriptThenElse_scriptId_cidx')
-- CREATE CLUSTERED INDEX scriptThenElse_scriptId_cidx ON scriptThenElse (scriptId)
if exists (SELECT name FROM sysindexes WHERE name=N'eventSetDef_eventSetId_idx')
DROP INDEX eventSetDef.eventSetDef_eventSetId_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'eventSetDef_eventSetId_ncidx')
CREATE NONCLUSTERED INDEX eventSetDef_eventSetId_ncidx ON eventSetDef (eventSetId)
if exists (SELECT name FROM sysindexes WHERE name=N'adminMessages_adminId_idx')
DROP INDEX adminMessages.adminMessages_adminId_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'adminMessages_adminId_ncidx')
CREATE NONCLUSTERED INDEX adminMessages_adminId_ncidx ON adminMessages (adminId)
if exists (SELECT name FROM sysindexes WHERE name=N'adminTasks_adminId_idx')
DROP INDEX adminTasks.adminTasks_adminId_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'adminTasks_adminId_ncidx')
CREATE NONCLUSTERED INDEX adminTasks_adminId_ncidx ON adminTasks (adminId)
if not exists (SELECT name FROM sysindexes WHERE name=N'langStr_strId_idx')
CREATE CLUSTERED INDEX langStr_strId_idx ON langStr (strId)
if not exists (SELECT name FROM sysindexes WHERE name=N'sStat_scriptId_idx')
CREATE NONCLUSTERED INDEX sStat_scriptId_idx ON scriptStatus (scriptId)
if not exists (SELECT name FROM sysindexes WHERE name=N'sStat_id_lastExec_idx')
CREATE CLUSTERED INDEX sStat_id_lastExec_idx ON scriptStatus (scriptId, lastExecTime)
if exists (SELECT name FROM sysindexes WHERE name=N'tempData_tempName_idx')
DROP INDEX tempData.tempData_tempName_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'tempData_tempName_ncidx')
CREATE NONCLUSTERED INDEX tempData_tempName_ncidx ON tempData (tempName)
if not exists (SELECT name FROM sysindexes WHERE name=N'scriptDoneAction_scriptId_ncidx')
CREATE NONCLUSTERED INDEX scriptDoneAction_scriptId_ncidx ON scriptDoneAction (scriptId)

if not exists (SELECT name FROM sysindexes WHERE name=N'id_alertTuple_idx')
CREATE NONCLUSTERED INDEX id_alertTuple_idx ON alertTuple (id)
if not exists (SELECT name FROM sysindexes WHERE name=N'tupleName_alertTuple_idx')
CREATE NONCLUSTERED INDEX tupleName_alertTuple_idx ON alertTuple (tupleName)

/* Patch Indexes */
if not exists (SELECT name FROM sysindexes WHERE name=N'location_locIdTab_idx')
CREATE NONCLUSTERED INDEX location_locIdTab_idx ON locIdTab (id,location,patchName)
if not exists (SELECT name FROM sysindexes WHERE name=N'locLangId_rptId_idx')
CREATE CLUSTERED INDEX locLangId_rptId_idx ON locLangId (rptId)
if exists (SELECT name FROM sysindexes WHERE name=N'collectionMembers_collectionId_idx')
DROP INDEX collectionMembers.collectionMembers_collectionId_idx
if exists (SELECT name FROM sysindexes WHERE name=N'patchCollection_collectionId_idx')
DROP INDEX patchCollection.patchCollection_collectionId_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'patchCollection_collectionId_ncidx')
CREATE NONCLUSTERED INDEX patchCollection_collectionId_ncidx ON patchCollection (collectionId)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchCollection_bulletinId_idx')
CREATE NONCLUSTERED INDEX patchCollection_bulletinId_idx ON patchCollection (bulletinId)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchStatus_patchDataId_idx')
CREATE NONCLUSTERED INDEX patchStatus_patchDataId_idx ON patchStatus (patchDataId)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchStatus_bulletinId_idx')
CREATE NONCLUSTERED INDEX patchStatus_bulletinId_idx ON patchStatus (bulletinId)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchStatus_updateClass_idx')
CREATE NONCLUSTERED INDEX patchStatus_updateClass_idx ON patchStatus (updateClass)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchPolicyAlert_idx')
CREATE NONCLUSTERED INDEX patchPolicyAlert_idx ON patchPolicyAlert (alertSent, bulletinId)
if not exists (SELECT name FROM sysindexes WHERE name=N'servicePacks_spbulletinId_idx')
CREATE NONCLUSTERED INDEX servicePacks_spbulletinId_idx ON servicePacks (spbulletinId)
if exists (SELECT name FROM sysindexes WHERE name=N'patchData_update_uidx')
DROP INDEX patchData.patchData_update_uidx
if not exists (SELECT name FROM sysindexes WHERE name=N'patchData_altkey_uidx')
CREATE UNIQUE NONCLUSTERED INDEX patchData_altkey_uidx ON patchData (updateId, updateRev, bUpdateId, bUpdateRev, langCodeId)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchData_updateClass_idx')
CREATE NONCLUSTERED INDEX patchData_updateClass_idx ON patchData (patchDataId,updateClassification)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchData_product_idx')
CREATE NONCLUSTERED INDEX patchData_product_idx ON patchData (wuaProductId)
IF ((SELECT COUNT(*) FROM sys.index_columns
WHERE object_id = object_id('patchLocationOverride')
AND index_id = (select index_id FROM sys.indexes WHERE name='patchLocationOverride_uidx')) < 6)
BEGIN
IF EXISTS (SELECT name FROM sysindexes WHERE name=N'patchLocationOverride_uidx')
BEGIN
DROP INDEX patchLocationOverride.patchLocationOverride_uidx;
END
CREATE UNIQUE NONCLUSTERED INDEX patchLocationOverride_uidx ON patchLocationOverride (kbArticleId, updateId, updateRev, bUpdateId, bUpdateRev, langCodeId)
END
IF ((SELECT COUNT(*) FROM sys.index_columns
WHERE object_id = object_id('patchLocationNotification')
AND index_id = (select index_id FROM sys.indexes WHERE name='patchLocationNotification_uidx')) < 6)
BEGIN
IF EXISTS (SELECT name FROM sysindexes WHERE name=N'patchLocationNotification_uidx')
BEGIN
DROP INDEX patchLocationNotification.patchLocationNotification_uidx;
END
CREATE UNIQUE NONCLUSTERED INDEX patchLocationNotification_uidx ON patchLocationNotification (kbArticleId, updateId, updateRev, bUpdateId, bUpdateRev, langName)
END
if exists (SELECT name FROM sysindexes WHERE name=N'patchOverride_idx')
DROP INDEX patchOverride.patchOverride_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'patchOverride_uidx')
CREATE UNIQUE NONCLUSTERED INDEX patchOverride_uidx ON patchOverride (kbArticleId, updateId, updateRev, bUpdateId, bUpdateRev)
IF ((SELECT COUNT(*) FROM sys.index_columns
WHERE object_id = object_id('patchOverride')
AND index_id = (select index_id FROM sys.indexes WHERE name='patchOverride_uidx')) = 3)
BEGIN
DROP INDEX patchOverride.patchOverride_uidx;
CREATE UNIQUE NONCLUSTERED INDEX patchOverride_uidx ON patchOverride (kbArticleId, updateId, updateRev, bUpdateId, bUpdateRev)
END
if not exists (SELECT name FROM sysindexes WHERE name=N'patchData_bulletinId46_idx')
CREATE NONCLUSTERED INDEX patchData_bulletinId46_idx ON patchData (bulletinId46)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchData_bulletinId45_idx')
CREATE NONCLUSTERED INDEX patchData_bulletinId45_idx ON patchData (bulletinId45)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchData_locationId_idx')
CREATE NONCLUSTERED INDEX patchData_locationId_idx ON patchData (locationId)
if not exists (SELECT name FROM sysindexes WHERE name=N'productWuaTab_uidx')
CREATE UNIQUE NONCLUSTERED INDEX productWuaTab_uidx ON productWuaTab (wuaProductId)
IF ((SELECT COUNT(*) FROM sys.index_columns
WHERE object_id = object_id('locWuaTab')
AND index_id = (select index_id FROM sys.indexes WHERE name='locWuaTab_uidx')) < 5)
BEGIN
IF EXISTS (SELECT name FROM sysindexes WHERE name=N'locWuaTab_uidx')
BEGIN
DROP INDEX locWuaTab.locWuaTab_uidx;
END
CREATE UNIQUE NONCLUSTERED INDEX locWuaTab_uidx ON locWuaTab (wuaLocId,wuaLocRev,wuaPatchId,wuaPatchRev,langCodeId)
END
if not exists (SELECT name FROM sysindexes WHERE name=N'patchInstScriptStatus_uidx')
CREATE UNIQUE NONCLUSTERED INDEX patchInstScriptStatus_uidx ON patchInstScriptStatus (agentGuid,scriptId)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchInstPatchStatus_uidx')
CREATE UNIQUE NONCLUSTERED INDEX patchInstPatchStatus_uidx ON patchInstPatchStatus (patchInstScriptStatusId,patchDataId)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchInstPatchStatus_idx')
CREATE NONCLUSTERED INDEX patchInstPatchStatus_idx ON patchInstPatchStatus (patchInstScriptStatusId)
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name=N'supersededPatch_supersededId_idx')
CREATE NONCLUSTERED INDEX supersededPatch_supersededId_idx ON supersededPatch (supersededId)
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name=N'patchApprovalPolicy_patchDataId_idx')
CREATE NONCLUSTERED INDEX patchApprovalPolicy_patchDataId_idx ON patchApprovalPolicy (patchDataId)
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name=N'patchApprovalPolicy_partitionId_idx')
CREATE NONCLUSTERED INDEX patchApprovalPolicy_partitionId_idx ON patchApprovalPolicy (partitionId)
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name=N'patchDataPartition_isActive_idx')
CREATE NONCLUSTERED INDEX patchDataPartition_isActive_idx ON patchDataPartition (partitionId,isActive) INCLUDE (patchDataId)


/* ticketing indexes
ticDescription ? ticketId, noteTime
ticFieldVals ? ticId, fieldId
ticListLabels ? fieldId, listOrder
ticRead ? ticId, adminId
*/
if not exists (SELECT name FROM sysindexes WHERE name=N'ticSearch_adminId_idx')
CREATE NONCLUSTERED INDEX ticSearch_adminId_idx ON ticSearch (adminId)
if not exists (SELECT name FROM sysindexes WHERE name=N'ticDescription_ticketId_idx')
CREATE NONCLUSTERED INDEX ticDescription_ticketId_idx ON ticDescription (ticketId)
if not exists (SELECT name FROM sysindexes WHERE name=N'ticDescription_noteTime_idx')
CREATE NONCLUSTERED INDEX ticDescription_noteTime_idx ON ticDescription (noteTime)
if exists (SELECT name FROM sysindexes WHERE name=N'ticFieldVals_ticId_idx')
DROP INDEX ticFieldVals.ticFieldVals_ticId_idx
if exists (SELECT name FROM sysindexes WHERE name=N'ticFieldVals_ticId_clust_idx')
DROP INDEX ticFieldVals.ticFieldVals_ticId_clust_idx
if exists (SELECT name FROM sysindexes WHERE name=N'ticFieldVals_fieldId_idx')
DROP INDEX ticFieldVals.ticFieldVals_fieldId_idx
if exists (SELECT name FROM sysindexes WHERE name=N'ticListLabels_fieldId_idx')
DROP INDEX ticListLabels.ticListLabels_fieldId_idx
if exists (SELECT name FROM sysindexes WHERE name=N'ticListLabels_fieldId_clust_idx')
DROP INDEX ticListLabels.ticListLabels_fieldId_clust_idx
if exists (SELECT name FROM sysindexes WHERE name=N'ticListLabels_listOrder_idx')
DROP INDEX ticListLabels.ticListLabels_listOrder_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'ticRead_ticId_idx')
CREATE NONCLUSTERED INDEX ticRead_ticId_idx ON ticRead (ticId)
if not exists (SELECT name FROM sysindexes WHERE name=N'ticRead_adminId_idx')
CREATE NONCLUSTERED INDEX ticRead_adminId_idx ON ticRead (adminId)
if not exists (SELECT name FROM sysindexes WHERE name=N'ticSummary_assignee_idx')
CREATE NONCLUSTERED INDEX ticSummary_assignee_idx ON ticSummary (assignee)
if not exists (SELECT name FROM sysindexes WHERE name=N'ticSummary_emailAddr_idx')
CREATE NONCLUSTERED INDEX ticSummary_emailAddr_idx ON ticSummary (emailAddr)

/* auditRsltApps table indexes - first, drop old indexes with non-standard name prefix */
if exists (SELECT name FROM sysindexes WHERE name=N'auditApps_productName_idx')
DROP INDEX auditRsltApps.auditApps_productName_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditApps_version_idx')
DROP INDEX auditRsltApps.auditApps_version_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditApps_applicationName_idx')
DROP INDEX auditRsltApps.auditApps_applicationName_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditApps_manufacturer_idx')
DROP INDEX auditRsltApps.auditApps_manufacturer_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditApps_description_idx')
DROP INDEX auditRsltApps.auditApps_description_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditApps_lastModifiedDate_idx')
DROP INDEX auditRsltApps.auditApps_lastModifiedDate_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditApps_dirPath_idx')
DROP INDEX auditRsltApps.auditApps_dirPath_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditApps_ea_baseAudit_lastAudit_idx')
DROP INDEX auditRsltApps.auditApps_ea_baseAudit_lastAudit_idx;
if exists (SELECT name FROM sysindexes WHERE name=N'auditRsltApps_productName_idx')
DROP INDEX auditRsltApps.auditRsltApps_productName_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditRsltApps_version_idx')
DROP INDEX auditRsltApps.auditRsltApps_version_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditRsltApps_manufacturer_idx')
DROP INDEX auditRsltApps.auditRsltApps_manufacturer_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditRsltApps_description_idx')
DROP INDEX auditRsltApps.auditRsltApps_description_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditRsltApps_dirPath_idx')
DROP INDEX auditRsltApps.auditRsltApps_dirPath_idx

/* auditRsltApps table indexes - second, recreate indexes */
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltApps_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltApps_agentGuid_idx ON auditRsltApps (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltApps_applicationName_idx')
CREATE NONCLUSTERED INDEX auditRsltApps_applicationName_idx ON auditRsltApps (applicationName)

/* auditRsltHwDrives table indexes */
if exists (SELECT name FROM sysindexes WHERE name=N'auditHwDrives_manufacturer_idx')
DROP INDEX auditRsltHwDrives.auditHwDrives_manufacturer_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditHwDrives_productName_idx')
DROP INDEX auditRsltHwDrives.auditHwDrives_productName_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditHwDrives_revision_idx')
DROP INDEX auditRsltHwDrives.auditHwDrives_revision_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditHwDrives_specificInfo_idx')
DROP INDEX auditRsltHwDrives.auditHwDrives_specificInfo_idx

/* auditRsltLicense table indexes */
if exists (SELECT name FROM sysindexes WHERE name=N'auditLicense_publisher_idx')
DROP INDEX auditRsltLicense.auditLicense_publisher_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditLicense_productName_idx')
DROP INDEX auditRsltLicense.auditLicense_productName_idx
if exists (SELECT name FROM sysindexes WHERE name=N'auditLicense_licenseCode_idx')
DROP INDEX auditRsltLicense.auditLicense_licenseCode_idx


/* pciProductList table indexes */
if not exists (SELECT name FROM sysindexes WHERE name=N'pciProductList_vendorId_idx')
CREATE CLUSTERED INDEX pciProductList_vendorId_idx ON pciProductList (vendorId)
if not exists (SELECT name FROM sysindexes WHERE name=N'pciVendorList_productId_idx')
CREATE NONCLUSTERED INDEX pciVendorList_productId_idx ON pciProductList (productId)
if not exists (SELECT name FROM sysindexes WHERE name=N'pciVendorList_productName_idx')
CREATE NONCLUSTERED INDEX pciVendorList_productName_idx ON pciProductList (productName)

/* pciVendorList table indexes */
if not exists (SELECT name FROM sysindexes WHERE name=N'pciVendorList_vendorId_idx')
CREATE CLUSTERED INDEX pciVendorList_vendorId_idx ON pciVendorList (vendorId)
if not exists (SELECT name FROM sysindexes WHERE name=N'pciVendorList_vendorName_idx')
CREATE NONCLUSTERED INDEX pciVendorList_vendorName_idx ON pciVendorList (vendorName)

if not exists (SELECT name FROM sysindexes WHERE name=N'scriptFldrDisplay_adminId_idx')
CREATE CLUSTERED INDEX scriptFldrDisplay_adminId_idx ON scriptFldrDisplay (adminId)
if not exists (SELECT name FROM sysindexes WHERE name=N'scriptOrder_adminId_idx')
CREATE CLUSTERED INDEX scriptOrder_adminId_idx ON scriptOrder (adminId)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchStatus_schedTogether_idx')
CREATE NONCLUSTERED INDEX patchStatus_schedTogether_idx ON patchStatus (schedTogether)

/* log table indexes */
if exists (SELECT name from sysindexes WHERE name=N'errorLog_agentGuid_idx')
DROP INDEX errorlog.errorLog_agentGuid_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'errorLog_agentGuidEventTime_idx')
CREATE NONCLUSTERED INDEX errorLog_agentGuidEventTime_idx ON errorLog (agentGuid, eventTime)
if exists (SELECT name from sysindexes WHERE name=N'configLog_agentGuid_idx')
DROP INDEX configlog.configLog_agentGuid_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'configLog_agentGuidEventTime_idx')
CREATE NONCLUSTERED INDEX configLog_agentGuidEventTime_idx ON configLog (agentGuid, eventTime)
if exists (SELECT name from sysindexes WHERE name=N'firewallLog_agentGuid_idx')
DROP INDEX firewallLog.firewallLog_agentGuid_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'firewallLog_agentGuidEventTime_idx')
CREATE NONCLUSTERED INDEX firewallLog_agentGuidEventTime_idx ON firewallLog (agentGuid, eventTime)
if exists (SELECT name from sysindexes WHERE name=N'netstatsLog_agentGuid_idx')
DROP INDEX netstatsLog.netstatsLog_agentGuid_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'netstatsLog_agentGuidEventTime_idx')
CREATE NONCLUSTERED INDEX netstatsLog_agentGuidEventTime_idx ON netstatsLog (agentGuid, eventTime)
if exists (SELECT name from sysindexes WHERE name=N'scriptLog_agentGuid_idx')
DROP INDEX scriptLog.scriptLog_agentGuid_idx
if exists (SELECT name from sysindexes WHERE name=N'scriptLog_eventTime_idx')
DROP INDEX scriptLog.scriptLog_eventTime_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'scriptLog_agentGuidEventTime_idx')
CREATE NONCLUSTERED INDEX scriptLog_agentGuidEventTime_idx ON scriptLog (agentGuid, eventTime)
if exists (SELECT name from sysindexes WHERE name=N'adminNotesLog_agentGuid_idx')
DROP INDEX adminNotesLog.adminNotesLog_agentGuid_idx
if exists (SELECT name FROM sysindexes WHERE name=N'eventTime_idx')
DROP INDEX adminNotesLog.eventTime_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'adminNotesLog_agentGuidEventTime_idx')
CREATE NONCLUSTERED INDEX adminNotesLog_agentGuidEventTime_idx ON adminNotesLog (agentGuid, eventTime)
if exists (SELECT name from sysindexes WHERE name=N'rcLog_agentGuid_idx')
DROP INDEX rcLog.rcLog_agentGuid_idx
if not exists (SELECT name FROM sysindexes WHERE name=N'rcLog_agentGuidEventTime_idx')
CREATE NONCLUSTERED INDEX rcLog_agentGuidEventTime_idx ON rcLog (agentGuid, eventTime)
if not exists (SELECT name FROM sysindexes WHERE name=N'uptimeLog_agentGuidEventTime_idx')
CREATE NONCLUSTERED INDEX uptimeLog_agentGuidEventTime_idx ON uptimeLog (agentGuid, eventTime)

/* other agentGuid indexes */
if not exists (SELECT name FROM sysindexes WHERE name=N'acctDelete_agentGuid_idx')
CREATE NONCLUSTERED INDEX acctDelete_agentGuid_idx ON acctDelete (agentGuid)
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'agentState') AND name = N'PK_agentState')
BEGIN
if not exists (SELECT name FROM sysindexes WHERE name=N'agentState_agentGuid_idx')
CREATE NONCLUSTERED INDEX agentState_agentGuid_idx ON agentState (agentGuid)
END
if not exists (SELECT name FROM sysindexes WHERE name=N'alertAgentOffline_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertAgentOffline_agentGuid_idx ON alertAgentOffline (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertAppExclude_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertAppExclude_agentGuid_idx ON alertAppExclude (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertGetFile_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertGetFile_agentGuid_idx ON alertGetFile (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertHwChange_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertHwChange_agentGuid_idx ON alertHwChange (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertLanWatch_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertLanWatch_agentGuid_idx ON alertLanWatch (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertlog_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertlog_agentGuid_idx ON alertlog (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertLowDisk_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertLowDisk_agentGuid_idx ON alertLowDisk (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertNewApp_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertNewApp_agentGuid_idx ON alertNewApp (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertNtEvent_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertNtEvent_agentGuid_idx ON alertNtEvent (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertPatch_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertPatch_agentGuid_idx ON alertPatch (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertProtectViolation_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertProtectViolation_agentGuid_idx ON alertProtectViolation (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertScriptFailed_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertScriptFailed_agentGuid_idx ON alertScriptFailed (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'alertSysCheck_agentGuid_idx')
CREATE NONCLUSTERED INDEX alertSysCheck_agentGuid_idx ON alertSysCheck (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltApps_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltApps_agentGuid_idx ON auditRsltApps (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltCpu_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltCpu_agentGuid_idx ON auditRsltCpu (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltDate_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltDate_agentGuid_idx ON auditRsltDate (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltDisks_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltDisks_agentGuid_idx ON auditRsltDisks (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltHwDrives_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltHwDrives_agentGuid_idx ON auditRsltHwDrives (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltHwPci_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltHwPci_agentGuid_idx ON auditRsltHwPci (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltLicense_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltLicense_agentGuid_idx ON auditRsltLicense (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltManual_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltManual_agentGuid_idx ON auditRsltManual (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltOnBrd_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltOnBrd_agentGuid_idx ON auditRsltOnBrd (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltPort_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltPort_agentGuid_idx ON auditRsltPort (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltPrinters_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltPrinters_agentGuid_idx ON auditRsltPrinters (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltSimm_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltSimm_agentGuid_idx ON auditRsltSimm (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltSlot_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltSlot_agentGuid_idx ON auditRsltSlot (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'auditRsltSmbios_agentGuid_idx')
CREATE NONCLUSTERED INDEX auditRsltSmbios_agentGuid_idx ON auditRsltSmbios (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'blockedip_agentGuid_idx')
CREATE NONCLUSTERED INDEX blockedip_agentGuid_idx ON blockedip (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'chatStat_agentGuid_idx')
CREATE NONCLUSTERED INDEX chatStat_agentGuid_idx ON chatStat (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'collectionMembers_agentGuid_idx')
CREATE NONCLUSTERED INDEX collectionMembers_agentGuid_idx ON collectionMembers (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'credential_agentGuid_idx')
CREATE NONCLUSTERED INDEX credential_agentGuid_idx ON credential (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'firewallType_agentGuid_idx')
CREATE NONCLUSTERED INDEX firewallType_agentGuid_idx ON firewallType (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'lanWatch_agentGuid_idx')
CREATE NONCLUSTERED INDEX lanWatch_agentGuid_idx ON lanWatch (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'lastReboot_agentGuid_idx')
CREATE NONCLUSTERED INDEX lastReboot_agentGuid_idx ON lastReboot (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'machChecks_agentGuid_idx')
CREATE NONCLUSTERED INDEX machChecks_agentGuid_idx ON machChecks (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'managedFiles_agentGuid_idx')
CREATE NONCLUSTERED INDEX managedFiles_agentGuid_idx ON managedFiles (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'networkAccess_agentGuid_idx')
CREATE NONCLUSTERED INDEX networkAccess_agentGuid_idx ON networkAccess (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'openinputports_agentGuid_idx')
CREATE NONCLUSTERED INDEX openinputports_agentGuid_idx ON openinputports (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchApps_agentGuid_idx')
CREATE NONCLUSTERED INDEX patchApps_agentGuid_idx ON patchApps (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchOfcConfig_agentGuid_idx')
CREATE NONCLUSTERED INDEX patchOfcConfig_agentGuid_idx ON patchOfcConfig (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchCredentialAlert_agentGuid_idx')
CREATE NONCLUSTERED INDEX patchCredentialAlert_agentGuid_idx ON patchCredentialAlert (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'patchStatus_agentGuid_idx')
CREATE NONCLUSTERED INDEX patchStatus_agentGuid_idx ON patchStatus (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'pciDiskNotes_agentGuid_idx')
CREATE NONCLUSTERED INDEX pciDiskNotes_agentGuid_idx ON pciDiskNotes (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'protectedfiles_agentGuid_idx')
CREATE NONCLUSTERED INDEX protectedfiles_agentGuid_idx ON protectedfiles (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'proxySettings_agentGuid_idx')
CREATE NONCLUSTERED INDEX proxySettings_agentGuid_idx ON proxySettings (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'rcNotifyPolicy_agentGuid_idx')
CREATE NONCLUSTERED INDEX rcNotifyPolicy_agentGuid_idx ON rcNotifyPolicy (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'relaySession_agentGuid_idx')
CREATE NONCLUSTERED INDEX relaySession_agentGuid_idx ON relaySession (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'scriptAssignment_agentGuid_idx')
CREATE NONCLUSTERED INDEX scriptAssignment_agentGuid_idx ON scriptAssignment (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'scriptAssignmentReboot_agentGuid_idx')
CREATE NONCLUSTERED INDEX scriptAssignmentReboot_agentGuid_idx ON scriptAssignmentReboot (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'scriptStatus_agentGuid_idx')
CREATE NONCLUSTERED INDEX scriptStatus_agentGuid_idx ON scriptStatus (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'tempAuditRsltApps_agentGuid_idx')
CREATE NONCLUSTERED INDEX tempAuditRsltApps_agentGuid_idx ON tempAuditRsltApps (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'trustedip_agentGuid_idx')
CREATE NONCLUSTERED INDEX trustedip_agentGuid_idx ON trustedip (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'usageLog_agentGuid_idx')
CREATE NONCLUSTERED INDEX usageLog_agentGuid_idx ON usageLog (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'userDynamicMenuItems_agentGuid_idx')
CREATE NONCLUSTERED INDEX userDynamicMenuItems_agentGuid_idx ON userDynamicMenuItems (agentGuid)
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'userIpInfo') AND name = N'PK_userIpInfo')
BEGIN
if not exists (SELECT name FROM sysindexes WHERE name=N'userIpInfo_agentGuid_idx')
CREATE NONCLUSTERED INDEX userIpInfo_agentGuid_idx ON userIpInfo (agentGuid)
END
if not exists (SELECT name FROM sysindexes WHERE name=N'userLogon_agentGuid_idx')
CREATE NONCLUSTERED INDEX userLogon_agentGuid_idx ON userLogon (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'windowsAddRemProg_agentGuid_idx')
CREATE NONCLUSTERED INDEX windowsAddRemProg_agentGuid_idx ON windowsAddRemProg (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'windowsServices_agentGuid_idx')
CREATE NONCLUSTERED INDEX windowsServices_agentGuid_idx ON windowsServices (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorAlarm_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorAlarm_agentGuid_idx ON monitorAlarm (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorCounterActivate_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorCounterActivate_agentGuid_idx ON monitorCounterActivate (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorEventAction_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorEventAction_agentGuid_idx ON monitorEventAction (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorMachineParam_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorMachineParam_agentGuid_idx ON monitorMachineParam (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorQuickStatus_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorQuickStatus_agentGuid_idx ON monitorQuickStatus (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorSetChanged_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorSetChanged_agentGuid_idx ON monitorSetChanged (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorSetLog_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorSetLog_agentGuid_idx ON monitorSetLog (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorSnmpMachineParam_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorSnmpMachineParam_agentGuid_idx ON monitorSnmpMachineParam (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorSnmpObjectSetLog_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorSnmpObjectSetLog_agentGuid_idx ON monitorSnmpObjectSetLog (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorSuspend_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorSuspend_agentGuid_idx ON monitorSuspend (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'snmpAgent_agentGuid_idx')
CREATE NONCLUSTERED INDEX snmpAgent_agentGuid_idx ON snmpAgent (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'adminMonitorSMDefaults_agentGuid_idx')
CREATE NONCLUSTERED INDEX adminMonitorSMDefaults_agentGuid_idx ON adminMonitorSMDefaults (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorSingleMachine_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorSingleMachine_agentGuid_idx ON monitorSingleMachine (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorAlarmProcess_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorAlarmProcess_agentGuid_idx ON monitorAlarmProcess (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'monitorAlarmFullCheckin_agentGuid_idx')
CREATE NONCLUSTERED INDEX monitorAlarmFullCheckin_agentGuid_idx ON monitorAlarmFullCheckin (agentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'MonitorDeleteLog_agentGuid_idx')
CREATE NONCLUSTERED INDEX MonitorDeleteLog_agentGuid_idx ON MonitorDeleteLog (agentGuid)

/* v5.1 indexes for archive */
if not exists (SELECT name FROM sysindexes WHERE name=N'rcLog_rcLogId_idx')
CREATE NONCLUSTERED INDEX rcLog_rcLogId_idx ON rcLog (rcLogId)
--if not exists (SELECT name FROM sysindexes WHERE name=N'scriptLog_scriptLogId_idx')
-- CREATE NONCLUSTERED INDEX scriptLog_scriptLogId_idx ON scriptLog (scriptLogId)
if not exists (SELECT name FROM sysindexes WHERE name='scriptLog_agentGuid_scriptId')
CREATE NONCLUSTERED INDEX scriptLog_agentGuid_scriptId ON scriptLog (agentGuid, scriptId)
if not exists (SELECT name FROM sysindexes WHERE name=N'netStatsLog_netStatsLogId_idx')
CREATE NONCLUSTERED INDEX netStatsLog_netStatsLogId_idx ON netStatsLog (netStatsLogId)
if not exists (SELECT name FROM sysindexes WHERE name=N'errorLog_errorLogId_idx')
CREATE NONCLUSTERED INDEX errorLog_errorLogId_idx ON errorLog (errorLogId)
if not exists (SELECT name FROM sysindexes WHERE name=N'configlog_Id_idx')
CREATE NONCLUSTERED INDEX configlog_Id_idx ON configlog (Id)

/* kad indexes */
if not exists (SELECT name FROM sysindexes WHERE name=N'kadComputers_name_idx')
CREATE NONCLUSTERED INDEX kadComputers_name_idx ON kadComputers ([Name])
if not exists (SELECT name FROM sysindexes WHERE name=N'kadComputers_collectingAgentGuid_idx')
CREATE NONCLUSTERED INDEX kadComputers_collectingAgentGuid_idx ON kadComputers (collectingAgentGuid)
if not exists (SELECT name FROM sysindexes WHERE name=N'kadUsers_logonName_idx')
CREATE NONCLUSTERED INDEX kadUsers_logonName_idx ON kadUsers (logonName)
if not exists (SELECT name FROM sysindexes WHERE name=N'kadUsers_collectingAgentGuid_idx')
CREATE NONCLUSTERED INDEX kadUsers_collectingAgentGuid_idx ON kadUsers (collectingAgentGuid)

/* create all foreign keys here */
if not exists (SELECT * from sysobjects where id = object_id(N'FK_viewCollection'))
BEGIN
DELETE FROM viewCollection WHERE viewId NOT IN (SELECT viewId FROM viewDef)
ALTER TABLE viewCollection ADD CONSTRAINT FK_viewCollection FOREIGN KEY (viewId) REFERENCES viewDef(viewId) ON DELETE CASCADE
END

if not exists (SELECT * from sysobjects where id = object_id(N'FK_patchInstScriptStatus_patchInstPatchStatus'))
ALTER TABLE patchInstPatchStatus WITH NOCHECK
ADD CONSTRAINT FK_patchInstScriptStatus_patchInstPatchStatus
FOREIGN KEY (patchInstScriptStatusId) REFERENCES patchInstScriptStatus (patchInstScriptStatusId)
ON DELETE CASCADE


/* convert MachGuid to Numeric(26, 0) v4.9*/
DECLARE @tbName varchar(500)
DECLARE updCursor CURSOR FOR select name from sysobjects where id in (select id from syscolumns where name = 'machGuid' and xtype != 108) AND xtype = 'U'
Open updCursor
FETCH updCursor INTO @tbName
WHILE(@@FETCH_STATUS = 0)
BEGIN

EXEC(N'alter table ' + @tbName + N' alter column machguid numeric(26, 0);')
FETCH updCursor INTO @tbName
END
CLOSE updCursor
DEALLOCATE updCursor

Errors collection contains:
Class: 16
Error #1505: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.serverInfo' and the index name 'serverInfo_pk'. The duplicate key value is (1). on line 322.
Error reported by .Net SqlClient Data Provider while connected to (local)
Class: 16
Error #1750: Could not create constraint. See previous errors. on line 322.
Error reported by .Net SqlClient Data Provider while connected to (local)
Class: 0
Error #3621: The statement has been terminated. on line 1.
Error reported by .Net SqlClient Data Provider while connected to (local)

AT :: at Hermes.DataManager.ExecuteNonQuery(SqlConnection Conn, SqlCommand Cmd) in c:jimCore runksourceMessageSysHermesHermesRuntimeDataManagerDataManager.cs:line 682

 

All Replies
  • Further The following error appears inplace of the username and password fields at the Kaseya Login page in the browser after the unsuccessful upgrade:

    Error Type:

    Microsoft OLE DB Provider for ODBC Drivers (0x-7ffbf1ec)

    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_GetSystemPreferenceValueById", or the name is ambiguous.

    /access/logon.asp, line 105

    Suggestions appreciated.

  • Yikes. Open a ticket with Kaseya immediately.

  • Kaseya support fixed this issue quickly by helping make some changes in the database.

    This was caused due to migrating to another server which created an entry in the database which had to be deleted. So next time you have hardware issues and migrate to another server like us, you may come accross the same issue. Do contact support. They are good.