question

Index rebuilt job is failing with following error. Anybody knows how to fix it? > ALTER INDEX' statement failed because the expression identifying partition number for the index 'tblMain_PK' is not of integer type.;16;3 Msg 14607, Level 16, State 1, Server abc, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid > (1 rows affected) 'ALTER INDEX' statement failed because the expression identifying partition number for the index 'tblMain_PK' is not of integer type. Msg 14607, Level 16, State 1, Server abc, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid Hi The script for index rebuilt used is as follow. Please suggest if i need to change something as the job failing still with above error. Use lpmovernight GO DECLARE @error INT DECLARE @ErrorMessage NVARCHAR(4000) DECLARE @ErrorSeverity INT DECLARE @ErrorState INT SELECT @error = 0 /*rebuild index tblMain.idx_mprgs*/ BEGIN TRY ALTER INDEX idx_mprgs ON tblMain REBUILD WITH ( MAXDOP = 2 ) END TRY BEGIN CATCH SELECT @error = @error + 1 SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); SELECT @ErrorMessage + ';' + CAST(@ErrorSeverity AS VARCHAR(8)) + ';' + CAST(@ErrorState AS VARCHAR(8)) EXECUTE msdb.dbo.sp_notify_operator 'DatabaseMail',@Name='SQL DBA',@subject='rebuild_index_lpmovernight_table_2:Failure',@body='INDEX REBUILD ON idx_mprgs FAILED' RAISERROR (@ErrorMessage,10,@ErrorState) WITH LOG END CATCH /*rebuild index tblMain.tblMain_PK*/ BEGIN TRY DECLARE @sql NVARCHAR(4000) DECLARE @parameters NVARCHAR(4000) DECLARE @id INT SELECT @sql = 'ALTER INDEX tblMain_PK ON tblMain REBUILD PARTITION = @id' SELECT @parameters = '@id INT' SELECT @id = b.boundary_id FROM sys.partition_functions a JOIN sys.partition_range_values b ON b.function_id = a.function_id AND b.value = ( SELECT MIN(x.value) FROM sys.partition_range_values x WHERE x.function_id = a.function_id AND x.value >= dbo.ConvertDateToONDate(GETDATE()) ) WHERE a.name = 'ufn_partition_tblMain' SELECT 'rebuild partition ' + CAST(@id AS VARCHAR(8)) EXECUTE sp_executesql @sql,@parameters,@ id=@id END TRY BEGIN CATCH SELECT @error = @error + 1 SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); SELECT @ErrorMessage + ';' + CAST(@ErrorSeverity AS VARCHAR(8)) + ';' + CAST(@ErrorState AS VARCHAR(8)) EXECUTE msdb.dbo.sp_notify_operator 'DatabaseMail',@Name='SQL DBA',@subject='rebuild_index_lpmovernight_table_2:Failure',@body='INDEX REBUILD ON tblMain_PK FAILED' RAISERROR (@ErrorMessage,10,@ErrorState) WITH LOG END CATCH IF @error = 0 BEGIN EXECUTE msdb.dbo.sp_notify_operator 'DatabaseMail',@Name='RatingsApp',@subject='rebuild_index_lpmovernight_table_2:Success',@body='Completed Successfully' END ELSE BEGIN EXECUTE msdb.dbo.sp_notify_operator 'DatabaseMail',@Name='RatingsApp',@subject='rebuild_index_lpmovernight_table_2:Success',@body='Completed with Failures' END