------set xact_abort on
------begin transaction


declare @debug bit
  set @debug = 0

--------------------
if @debug = 1 begin
  select ident_current('BufferItem') as 'at START'
end
--------------------


declare @ReceedThreshold int
set @ReceedThreshold = 1600000000   -- max INT is 2,147,483,647

declare @MaxBufferItemID_old int
if object_id('BufferItem_old') is null
	set @MaxBufferItemID_old = ident_current('BufferItem')
else
	set @MaxBufferItemID_old = ident_current('BufferItem_old')

--------------------
if @debug = 1 begin
	select @MaxBufferItemID_old as [@MaxBufferItemID_old]
end
--------------------

if @MaxBufferItemID_old >=  @ReceedThreshold   -- TODO: change to >= !!!!! - DONE; toggle for testing
begin 

	--declare @tran int 
	--    set @tran = @@trancount

	declare @Script nvarchar(1000) 
	    set @Script= '7176_920-SP1_BufferItem_ReceedIdentity.sql'

	declare @Step   nvarchar(1000) 
	    set @Step   = 'Start'

	--------------------
	if @debug = 1 begin
		print @Step
	end
	--------------------

    declare @Last_BufferItemID_old int
	    -- set @Last_BufferItemID_old = 0 -- set later

	begin try
  
	--if @tran = 0    
	--BEGIN TRANSACTION   

	set @Step = 'exec DBM_Conv_rename_Table BufferItem to BufferItem_old'
	--------------------
	if @debug = 1 begin
		print @Step
	end
	--------------------
	if object_id('BufferItem_old') is null
		exec DBM_Conv_rename_Table
			@tablename           = 'BufferItem'
		 --  ,@newname             = 'BufferItem_old'
		   ,@suffix              = '_old'
		   ,@RenamePK            = 1
		   ,@DropDF              = 1
		   ,@DropFK              = 1
		   ,@DropPK              = 0
		   ,@DropNCIndex         = 0	                            

 
	set @Step = '
	CREATE TABLE [dbo].[BufferItem]
	(
		 [BufferID]			[int]				NOT NULL
		,[Item]				[image]				NOT NULL
		,[BufferItemID]		[int] IDENTITY(1,1) NOT NULL
		,BufferItemID_old	int						null
		 CONSTRAINT		[BufferItem_PK] PRIMARY KEY CLUSTERED 
		(
			[BufferID] ASC,
			[BufferItemID] ASC
		)
		,CONSTRAINT [FK_BufferItem_Buffer] FOREIGN KEY(BufferID) REFERENCES [dbo].[Buffer] ([BufferID])
	) 
	'
	if object_id('BufferItem') is null -- this is done for re-runnability in case there was an exception in the middle of the script
	begin
		--------------------
		if @debug = 1 begin
			print @Step
		end
		--------------------
		exec(@Step)
    end

	----------------------
	set @Step = '
	create nonclustered index BufferItem_old_BufferItemID_IX
		on dbo.BufferItem_old(BufferItemID) 
     '
    if 0 = dbo.udf_Conv_DoesIndexExist('BufferItem_old','BufferItem_old_BufferItemID_IX') 
	begin
		--------------------
		if @debug = 1 begin
			print @Step
		end
		--------------------

		exec(@Step)
    end
	----------------------

    
	----------------------
	set @Step = '
	create nonclustered index BufferItem_BufferItemID_IX
		on dbo.BufferItem(BufferItemID) 
     '
    if 0 = dbo.udf_Conv_DoesIndexExist('BufferItem','BufferItem_BufferItemID_IX')
	begin
		--------------------
		if @debug = 1 begin
			print @Step
		end
		--------------------

		exec(@Step)
	end


	-- now that we have the BufferItem_old_BufferItemID_IX index
	--------------------
	if @debug = 1 begin
		print 'set @Last_BufferItemID_old  = (select top (1)...'
	end
	--------------------
	exec sp_executesql N'
		set @Last_BufferItemID_old  = (select top (1) 
											  BufferItemID_old 
										 from BufferItem
										order 
										   by BufferItemID desc
									  )'
		,@params                = N'@Last_BufferItemID_old int output'
		,@Last_BufferItemID_old = @Last_BufferItemID_old       output

    if @Last_BufferItemID_old is null
		set @Last_BufferItemID_old = 0

    select @Last_BufferItemID_old as [@Last_BufferItemID_old]
	      ,@MaxBufferItemID_old   as [@MaxBufferItemID_old]
	----------------------


	declare @sqlTransfer nvarchar(2000)
	set @sqlTransfer = '
	  insert dbo.BufferItem
		  (
			 [BufferID]		
			,[Item]			
			,BufferItemID_old -- BufferItem.BufferItemID_old
		  )
	  select top(5000)  
			 [BufferID]		
			,[Item]			
			,[BufferItemID]	   -- BufferItem_old.BufferItemID
		from 
		      dbo.BufferItem_old  
       where
	         BufferItemID > @Last_BufferItemID_old
		order 
		   by BufferItemID asc

        set @rows = @@RowCount

		set @Last_BufferItemID_old  = (select top (1) 
											  BufferItemID_old 
										 from BufferItem
										order 
										   by BufferItemID desc
									  )

--raisError(''Test--Error'',16,10)
	'

	set @Step = 'exec sp_executesql @sqlTransfer'
	declare @rows int 

 	while (1 = 1) begin

		--------------------
		if @debug = 1 begin
			print @Step
		end
		--------------------

	  exec sp_executesql @sqlTransfer
	                    ,@params = N'@Last_BufferItemID_old   int output						
						            ,@rows                    int output'
                        ,@Last_BufferItemID_old = @Last_BufferItemID_old out
                        ,@rows                  = @rows                  out
	
		if    @Last_BufferItemID_old >= @MaxBufferItemID_old 
		    or @rows = 0
		begin
			
			select   @Last_BufferItemID_old as [@Last_BufferItemID_old] 

			-- drop column BufferItemID_old
			--------------------
			if @debug = 1 begin
				print 'drop column BufferItemID_old'
			end
			--------------------
			exec dbo.DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN 'BufferItem','BufferItemID_old'


			-- drop temporary index
			--------------------
			if @debug = 1 begin
				print 'drop index BufferItem_BufferItemID_IX on dbo.BufferItem'
			end
			--------------------
			drop index BufferItem_BufferItemID_IX on dbo.BufferItem

			select top(10)
			       * 
				  ,count(*) over() as Rows
			  from BufferItem 
			 order 
			   by BufferItemID desc

		    --------------------
			if @debug = 1 begin
				print 'BREAK -- exit; all transferred'
			end
			--------------------
			BREAK -- exit; all transferred
			
        end
	end


	--if @tran = 0  
	--	   COMMIT   
	CHECKPOINT

	drop TABLE [dbo].[BufferItem_old]

		---------------------------------
end try
---------------------
begin catch

	print '------------------  @Step:'
	print @Step
	
    declare @ErrorMessage       nvarchar(max)
	print @ErrorMessage

    set @ErrorMessage = '[Error in: Script=' +  isnull(@Script,'null') + ' @Step=' + isnull(@Step,'null') + ']' +  + [dbo].[udf_ErrorInfo]()
	print @ErrorMessage

 --   if (XACT_STATE() != 0) 
	--and @tran = 0  
	--	rollback transaction

    RaisError (@ErrorMessage,16,255)

end catch
end -- if object_id('TableName') is null 


--------------------
if @debug = 1 begin
	select ident_current('BufferItem') as 'at END'
end
--------------------

go

 ------rollback