CREATE PROCEDURE [dbo].[CopyAttachments] @FromCustomerNumber int, @ToCustomerNumber int AS
SET NOCOUNT ON

/*
	Created by: Ole Fromberg February 2009
	Used to copy Attachments from one customer to another. Used by CopyCustomerProfile.
  
  	Changes:
      VD 20090921: Bug: Better cleanup when changing profile
      VD 20090921: Bug: Housekeeping before copying simplified, because trying to delete from tabel Attachment not allowed
      OF 20090211: EXT: Some minor changes made.
	  OF 20090209: Bug: Blob field copy changed to copy by chunks. SQL Server 2000 do not support blob field copy directly.
*/

declare @Date smalldatetime
declare @FileGroup smallint
declare @DateStamp smalldatetime
declare @Readonly bit
declare @Batch bit
declare @AddressTypeNumber int
declare @Name varchar(255)
declare @TemplateNumber smallint        
declare @AttachmentNumber int
declare @AttachmentNumberNew int

declare @ptrval varbinary(16) 
declare @StartIndex int 
declare @RequestDataBodyLength int 
declare @RequestDataBody varchar(8000) 

-- Delete all attachments from the destination customer before copying new ones
declare DeleteCur Cursor Forward_Only FOR
  SELECT AttachmentNumber FROM CustomerAttachmentXTab WITH (nolock) WHERE CustomerNumber = @ToCustomerNumber
  
OPEN DeleteCur  
FETCH NEXT FROM DeleteCur INTO @AttachmentNumber
WHILE (@@Fetch_status = 0)
BEGIN
  DELETE FROM CustomerAttachmentXTab WHERE AttachmentNumber = @AttachmentNumber
  DELETE FROM ReservationAttachmentXTab WHERE AttachmentNumber = @AttachmentNumber
  DELETE FROM Attachment WHERE AttachmentNumber = @AttachmentNumber 
  FETCH NEXT FROM DeleteCur INTO @AttachmentNumber
END
CLOSE DeleteCur
DEALLOCATE DeleteCur

declare CopyCur Cursor Forward_Only FOR
  SELECT
    Attachment.FileGroup, 
	Attachment.DateStamp,
	Attachment.ReadOnly,
	Attachment.Batch,
	Attachment.AddressTypeNumber,
	Attachment.Name,
	Attachment.AttachmentNumber,
	Attachment.TemplateNumber
  from Attachment WITH (nolock)
  join CustomerAttachmentXTab WITH (nolock) ON Attachment.AttachmentNumber = CustomerAttachmentXTab.AttachmentNumber
  where CustomerAttachmentXTab.CustomerNumber = @FromCustomerNumber

Open CopyCur
Fetch Next From CopyCur into @FileGroup, @DateStamp, @Readonly, @Batch, @AddressTypeNumber, @Name, @AttachmentNumber, @TemplateNumber

While(@@Fetch_status = 0)
Begin
    INSERT INTO Attachment (FileGroup, DateStamp, ReadOnly, Batch, AddressTypeNumber, [Name], TemplateNumber) 
		VALUES (@FileGroup, @DateStamp, @Readonly, @Batch, @AddressTypeNumber, @Name, @TemplateNumber)
    
    SELECT @AttachmentNumberNew = @@identity
    
		-- **********************************************************************************************************
		-- Copy blob field from source to destination in chunks. SQL Server 2000 does not allow copying blob directly

		--clear any existing data in destination blob field
		UPDATE Attachment 
		SET Blob = NULL 
		WHERE AttachmentNumber = @AttachmentNumberNew 

		SET @StartIndex = 0 

		--get length of data to copy 
		SELECT @RequestDataBodyLength = DATALENGTH(Blob)
		FROM Attachment WITH (nolock)
		WHERE AttachmentNumber = @AttachmentNumber 

		--get text pointer to destination blob field
		SELECT @ptrval = TEXTPTR(Blob) 
		FROM Attachment WITH (nolock)
		WHERE AttachmentNumber = @AttachmentNumberNew  

		--copy data in chunks of 8000 from source to destination
		WHILE @StartIndex < @RequestDataBodyLength
		BEGIN 
			SELECT @RequestDataBody = SUBSTRING(Blob, @StartIndex +1 , 8000) 
			FROM Attachment WITH (nolock)
			WHERE AttachmentNumber = @AttachmentNumber
			UPDATETEXT Attachment.Blob @ptrval @StartIndex 0 @RequestDataBody
			SET @StartIndex = @StartIndex + DATALENGTH(@RequestDataBody) 
		END

		 --This will work in SQL Server 2005 and on. Just copy the blob field directly.
			--update Attachment set Attachment.Blob = (SELECT Blob from Attachment (nolock) where AttachmentNumber = @AttachmentNumber)
			--where Attachment.AttachmentNumber = @AttachmentNumberNew
		
		-- ****************************************************************************************************************
       
    INSERT INTO CustomerAttachmentXTab (CustomerNumber, AttachmentNumber) 
		VALUES (@ToCustomerNumber, @AttachmentNumberNew)
    
    Fetch Next From CopyCur into @FileGroup, @DateStamp, @Readonly, @Batch, @AddressTypeNumber, @Name, @AttachmentNumber, @TemplateNumber
end;

Close CopyCur
DeAllocate CopyCur