question

jassatwal26 avatar image
jassatwal26 asked

Index rebuilt job is failing with following error. Anybody knows how to fix it?

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
indexesmaintenancerebuild
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Fatherjack avatar image
Fatherjack answered
First thought is that the variable @id which is declared as an INT is then CAST into a VARCHAR(8). This could be truncating part of the value and then leading to your error. Try changing it to `SELECT 'rebuild partition ' + CAST(@id AS VARCHAR(10))` at line 51 above
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jassatwal26 avatar image jassatwal26 commented ·
Thanks a lot will try with it.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.