Job Failed while Checking for Indexes

• Shazme

  Ten Centuries

  Points: 1292

  More actions

  July 7, 2011 at 2:39 am

  #253228

  Hi All,

  I have this job which runs every day but its failed today with error message below while checking for indexes.

  scripts and stored procedures. [SQLSTATE 01000] (Message 15477) Caution: Changing any part of an object name could break scripts and stored procedures. [SQLSTATE 01000] (Message 15477) Caution: Changing any part of an object name could break scripts and stored procedures. [SQLSTATE 01000] (Message 15477) Alter table failed because unique column IDs have been exhausted for table 'XXX'. [SQLSTATE 42000] (Error 1714). The step failed.

  This is the SPR below which executes at this point.

  USE [XXXXX]

  GO

  /****** Object: StoredProcedure [dbo].[CreateIndex] Script Date: 07/07/2011 09:31:00 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER proc [dbo].[CreateIndex]

  @table_name nvarchar(255),

  @column_name nvarchar(50),

  @clustered bit=0

  as

  BEGIN

  declare @sp_name nvarchar(255)

  set @sp_name = 'CreateIndexes'

  --

  -- STARTING...

  --

  -- exec dbo.usp_CreateLog @sp_name , 'START'

  declare @sqlstr nvarchar(1000)

  declare @indextype nvarchar(20)

  if @clustered = 1

  set @indextype = 'UNIQUE CLUSTERED'

  else

  set @indextype = 'NONCLUSTERED'

  set @sqlstr = 'IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N''[dbo].['+@table_name+']'') AND name = N''IX_'+@column_name+'_'+@table_name+''')

  CREATE '+ @indextype +' INDEX [IX_'+@column_name+'_'+@table_name+'] ON [dbo].['+@table_name+'](['+@column_name+']) ON [INDEXES] '

  exec (@sqlstr)

  exec dbo.CreateLog @sp_name, @sqlstr

  --

  -- FINISHED

  --

  -- exec dbo.usp_CreateLog @sp_name ,'END'

  END

  Thanks for your help.

  ImI

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  July 7, 2011 at 2:57 am

  #1349489

  See if this helps:

  http://chopeen.blogspot.com/2007/05/alter-table-failed-because-unique.html

  -- Gianluca Sartori

• Shazme

  Ten Centuries

  Points: 1292

  More actions

  July 7, 2011 at 3:47 am

  #1349520

  Thanks for your Advice Gianluca.

  Yes ;that what I have followed .Its Column Id's looks like maxed out. So I am doing an new table and check the max id which is sitting 13 rather 32000 so this all making sense now.

  Cheers

  ImI

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  July 7, 2011 at 3:58 am

  #1349526

  Glad I could help.

  Post back here if you experience other issues.

  Gianluca

  -- Gianluca Sartori