x002548's Blog - March 2004 Entries

Posts: 83
Comments: 600
Trackbacks: 40

March 2004 Entries

From a SQLTeam freind, Thanks Duane

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=9857

QA Needs to be set to text mode and font courier

SET NOCOUNT ON

DECLARE @InpString VARCHAR(4000)
DECLARE @CharCT INT
DECLARE @PrevY INT
DECLARE @CurrX INT
DECLARE @CurrY INT
DECLARE @InpRow VARCHAR(4000)
DECLARE @RowCT INT

SET @RowCT = 1
SET @InpString = '04040404080409041004110415041604170418042204230424042504'
SET @InpString = @InpString + '030503050405070512051405190521052605'
SET @InpString = @InpString + '030603060406070612061406190621062606'
SET @InpString = @InpString + '020702070407070712071407190721072607'
SET @InpString = @InpString + '020802080408070812081408190821082608'
SET @InpString = @InpString + '010901090409070912091409190921092609'
SET @InpString = @InpString + '011001100410071012101410191021102610'
SET @InpString = @InpString + '0011001101110211031104110511071112111411191121112611'
SET @InpString = @InpString + '04120412071212121412191221122612'
SET @InpString = @InpString + '04130413081309131013111315131613171318132213231324132513'

CREATE TABLE #Stuff(X INT, Y INT)
CREATE TABLE #BK(BID INT, B VARCHAR(4000))

SET @CharCT = 1

WHILE @CharCT < LEN(@InpString)
BEGIN
INSERT INTO #Stuff VALUES(CAST(SUBSTRING(@InpString, @CharCT, 2) as INT) + 1, SUBSTRING(@InpString, @CharCT + 2, 2))
SET @CharCT = @CharCT + 4
END

DECLARE BrettCur
CURSOR FOR
SELECT * FROM #Stuff
ORDER BY 2

SET @InpRow = ''

OPEN BrettCur
FETCH NEXT FROM BrettCur INTO @CurrX, @CurrY
SET @CharCT = 0
SET @PrevY = @CurrY
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CurrY = @PrevY
BEGIN
WHILE @CharCT < @CurrX
BEGIN
SET @InpRow = @InpRow + ' '
SET @CharCT = @CharCT + 1
END
SET @InpRow = @InpRow + '@'
SET @CharCT = @CharCT + 1
END
ELSE
BEGIN
INSERT INTO #BK VALUES(@PrevY, @InpRow)
SET @CharCT = 0
SET @PrevY = @CurrY
SET @InpRow = ''
END
FETCH NEXT FROM BrettCur INTO @CurrX, @CurrY
END

CLOSE BrettCur
DEALLOCATE BrettCur

INSERT INTO #BK VALUES(99, @InpRow)

SELECT B
FROM #BK
ORDER BY BID

DROP TABLE #Stuff
DROP TABLE #BK

posted @ Wednesday, March 24, 2004 3:47 PM | Feedback (0)

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

SET NOCOUNT OFF

posted @ Tuesday, March 23, 2004 9:02 AM | Feedback (0)

Article Categories

Archives

Post Categories

Image Galleries

Misc

SQL Server