x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Sanitize input files

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

GO

SET NOCOUNT OFF

 

Print | posted on Tuesday, March 23, 2004 9:02 AM | Filed Under [ SQL Server ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET