Sanitize input files

Tue Mar 23, 2004 by Brett Kaiser (x002548) in sql-server

I had a devil of a time with the spawned thread blocking from xp_cmdshell and doing bcp's....

Sometime it would work, other times not...

The sproc is treated as 1 logical unit of work...for example there is no problem in just rerunning it if it fails...

Even though I've got 4 transactions...

Any comment appreciated...

Oh, and btw, it takes a file (8k or less wide) loads it, and modifies what you want to cheange, archives the old file, and spits out a sanitized one...

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

SET NOCOUNT OFF