Forum Discussion

adityakumar1176's avatar
adityakumar1176
Copper Contributor
Jul 12, 2024

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

  • Arshad440's avatar
    Arshad440
    Copper Contributor
    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

Resources