Find And Replace Script Needs Filtering

Question

Find And Replace Script Needs Filtering

Chris Kitchen

Hall of Fame

Points: 3090
More actions
October 20, 2005 at 11:53 am

#88200

Hi,
I am using the SP below to scan through a database looking for a given string, and replacing it with whatever I use for the ReplaceStr parameter.
When I execute it I keep getting the following error message .....
String or binary data would be truncated.
The statement has been terminated.
The code is stopping when it gets to a trigger on one of the tables. My question is, what code do I need to change so that it will only look at the contents of the tables and nothing else. I'm using "TABLE_TYPE = 'BASE TABLE'" but that obviously isn't working.

Thanks in advance.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FindReplace]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FindReplace]
GO
CREATE PROC sp_FindReplace
(
@SearchStr nvarchar(4000),
@ReplaceStr nvarchar(4000)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0
WHILE @TableName IS NOT NULL
BEGIN
  SET @ColumnName = ''
  SET @TableName =
  (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM INFORMATION_SCHEMA.TABLES
   WHERE   TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 'IsMSShipped'
             ) = 0
&nbsp
  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
   SET @ColumnName =
   (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
     AND QUOTENAME(COLUMN_NAME) > @ColumnName
  &nbsp

   IF @ColumnName IS NOT NULL
   BEGIN
    SET @SQL= 'UPDATE ' + @TableName +
      ' SET ' + @ColumnName
      + ' = REPLACE(' + @ColumnName + ', '
      + QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
      ') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    EXEC (@SQL)
    SET @RCTR = @RCTR + @@ROWCOUNT
   END
  END
END
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' Occurence(s), oh YES!!' + ' Of The String "'
+ @SearchStr + '" With The Text "' + @ReplaceStr + '". Aren''t you clever!!' AS 'Outcome'
END
www.sqlAssociates.co.uk

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

Many issues here. You can turn off triggers, but should you?

What checks are you going to make to ensure that the inserted string does not cause the data to exceed the column width? (and thus lose data.)

You could also turn off the aborting on truncation if you want to use brute force.

You can also get the column width and filter on not exceeding, but then some will be left unchanged. Perhaps you want a two step process that simply sees if it's ok... and what of transaction management...

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 2

Hi,

Thanks for your post.

The length of the Find string will always be the same length as the Replace string, we can't turn the triggers off.

Is there a simple way to add another "AND" clause to the procedure to stop this happening?

Thanks in advance.

www.sqlAssociates.co.uk

john-132685 SSCertifiable Points: 6134 More actions · Answer 3

(OK, I'm trying my best not to think too hard... so this is knee jerk problem solving)

If you are certain that the FIND and REPLACE strings are same length, then it follows that this error should not occur.

Therefor, I would guess that it might be an issue with NVARCHAR/VARCHAR: That is, the parser maybe choking on the differences between using nvarchars and varchars while manipulating data.... (And you might need to be more explicit there) I have also seen it when, because a variable is potentially 4k, it doesn't want to allow the concatenation even thought it doesn't exceed... but I can't recall that circumstance more definitively. (perhaps your find and replace should be varchar(1000)?)

Eitherway, be sure what the issue is: If you think it's a trigger then what is that trigger doing to cause it?

Have your script simply print the SQL to be executed, then wrap it in a transaction so you can roll it back and trace it down to where and why it's happening...

No doubt there is a simple, obvious thing that someone will come along and point out after me...

noeld SSC Guru Points: 96590 More actions · Answer 4

write a print statement before the exec statement and see where it stops

* Noel