Forum Discussion
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 *...
Hi,
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
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