SET NOCOUNT ON

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_DataHold]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_DataHold]
GO

CREATE TABLE wrk_DataHold(Col1 varchar(8000))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_OldNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_OldNew]
GO

CREATE TABLE wrk_OldNew(Old varchar(255),New varchar(255))
GO


INSERT INTO wrk_OldNew(Old,New)
SELECT 'SEVERAL EE~S', ''
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ModifyRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ModifyRows]
GO
CREATE PROC usp_ModifyRows
	  @Path sysname
	, @FName sysname	 
AS

SET NOCOUNT ON

  BEGIN TRAN
	DECLARE @cmd varchar(8000), @Servername sysname, @rc int, @error int, @rowcount int
	, @Old varchar(255), @New varchar(255), @x int

	CREATE TABLE ##bcpLog(Col1 varchar(8000))

	SET @rc = 0

	DELETE FROM wrk_DataHold

	SELECT @error = @@error, @rowcount = @@ROWCOUNT
	IF @error <> 0
	  BEGIN
		SET @rc = -1
		GOTO usp_ModifyRows_Error
	  END
  COMMIT TRAN

  BEGIN TRAN
	SET @cmd = 'bcp wrk_DataHold in ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
	INSERT INTO ##bcpLog(Col1) EXEC master..xp_cmdShell @cmd

	DECLARE OldNew CURSOR FOR SELECT Old, New FROM wrk_OldNew

	OPEN OldNew

	FETCH NEXT FROM OldNew INTO @Old, @New

	WHILE @@FETCH_STATUS = 0
	  BEGIN 
		UPDATE wrk_DataHold
		   SET Col1 = REPLACE(Col1,@Old,@New)
		 WHERE Col1 LIKE '%'+@Old+'%'

		SELECT @error = @@error, @rowcount = @@ROWCOUNT
		IF @error <> 0
		  BEGIN
			SET @rc = -1
			GOTO usp_ModifyRows_Error
		  END

		INSERT INTO ##bcpLog(Col1) 
		SELECT 'REPLACE "'+ RTRIM(@Old) + '" With "' + RTRIM(@New)+ '"' UNION ALL
		SELECT '('+CONVERT(varchar(25),@rowcount)+' row(s) affected)'
		
		FETCH NEXT FROM OldNew INTO @Old, @New
	  END

	CLOSE OldNew
	DEALLOCATE OldNew
  COMMIT TRAN

   BEGIN TRAN 

	INSERT INTO ##bcpLog(Col1) 
	SELECT 'Preparing to Archive Old file To '
		+ @Path + '_'+ @FName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate()),'-','_'),':','_'),' ','_')

	SET @cmd = 'MD ' + @Path+
		+REPLACE(@FName,'.','_')
		+'_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate(),120),'-','_'),':','_'),' ','_')
	INSERT INTO ##bcpLog(Col1)  SELECT @cmd
	INSERT INTO ##bcpLog(Col1)  EXEC master..xp_cmdShell @cmd

	SET @cmd = 'MOVE '+ @Path + @FName + ' '
		+ @Path + REPLACE(@FName,'.','_')
		+'_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate(),120),'-','_'),':','_'),' ','_')+ '\'+ @FName
	INSERT INTO ##bcpLog(Col1)  SELECT @cmd
	INSERT INTO ##bcpLog(Col1)  EXEC master..xp_cmdShell @cmd

	INSERT INTO ##bcpLog(Col1) 
	SELECT 'Preparing to Write out new file '+ @Path + @FName


 COMMIT TRAN

	SET @cmd = 'bcp wrk_DataHold out ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
	INSERT INTO ##bcpLog(Col1)  EXEC master..xp_cmdShell @cmd


  BEGIN TRAN 
	SET @cmd = 'bcp ##bcpLog out ' + @Path + 'bcpLog.txt -S ' + @@SERVERNAME + ' -U -P -c'
	SET @cmd = 'EXEC master..xp_cmdShell "'+@cmd+'", no_output' 
	EXEC(@cmd)
 COMMIT TRAN

usp_ModifyRows_Exit:
	
--	SELECT * FROM ##bcpLog
	DROP TABLE ##bcpLog
	SET NOCOUNT OFF
	RETURN @rc

usp_ModifyRows_Error:

	CLOSE OldNew
	DEALLOCATE OldNew
	ROLLBACK TRAN
	GOTO usp_ModifyRows_Exit

GO

SET NOCOUNT OFF