Forum Discussion
adityakumar1176
Jul 12, 2024Copper Contributor
SQL job keeps failing!
I have a SQL where jobs keep failing due to index being already present.
[SQLSTATE 01000] (Message 50000) Creating CRM_CreateChangeCompany [SQLSTATE 01000] (Message 50000) if not exists (select * from [Replication].[SysIndexes] where name = 'CRM_CreateChangeCompany') begin create index CRM_CreateChangeCompany on [PROD_Replica].[dbo].[COMPANY] (CREATE_DATE, CHANGE_DATE, COMPANY_NO) exec LogMessage @procedure = 'EnsureIndexExists', @message = 'Created Index CRM_CreateChangeCompany' end
Any help and assistance would be great!
2 Replies
Sort By
- Arshad440Copper ContributorHi,
You Should Specify the database name when the query puts on Jobs, could use this query
use <DbName>
if exists (select * from [Replication].[SysIndexes] where name = 'CRM_CreateChangeCompany')
begin
drop index [PROD_Replica].[dbo].[COMPANY].CRM_CreateChangeCompany
create index CRM_CreateChangeCompany on [PROD_Replica].[dbo].[COMPANY] (CREATE_DATE, CHANGE_DATE, COMPANY_NO)
exec LogMessage @procedure = 'EnsureIndexExists', @message = 'Created Index CRM_CreateChangeCompany'
END
Regards
Arshad - olafhelperBronze Contributor
[SQLSTATE 01000] (Message 50000)adityakumar1176 , error 50000 and above are user code raised error, so review your code.
See RAISERROR (Transact-SQL) - SQL Server | Microsoft Learn => msg_id = 50000