Unfortunately, converting to varbinary didn't work and neither did removing the nchar(0) start.

Also unfortunately, I do not have permission to run profiler in this particular case. It's a severely locked down server.

I have confirmed that the '?' I am seeing is truly CHAR(63) by checking the ASCII value of the characters in the field.

Now, to explain further, I have this code in a Function on another server. It works flawlessly there. I pass in a NVARCHAR(MAX) string and it runs through all the chars and uses STUFF to take them out. It spits out the cleaned string.

I can't simply put the working function on this server and handle it the same way. I am not allowed to create anything more than temp tables. As a result, I am using SSIS. Here is the run-down on what I am doing:

First thing I do is identify ALL char, varchar, nchar, and nvarchar columns in the TABLE_SCHEMA.

I have a ForEach loop to iterate over all of these identified tables/columns. In the loop I do a couple of things:

First, I create a SQL statement using VBScript. It outputs two variables; SQL1 and SQL2. They are:

--sTable and sColumn are pulled in from the ForEach loop

SQL1 = "SELECT * FROM " & sTable & " WHERE PATINDEX("

SQL2 = ", (" & sColumn & ") COLLATE Latin1_General_BIN) <> 0"

Next, I run the SQL code from my first post. Here is the unaltered code in that SQL Task:

DECLARE @Counter INTEGER

DECLARE @Pattern NVARCHAR(MAX)

DECLARE @loop int

DECLARE @sSQL NVARCHAR(MAX)

SET @Pattern = '%['

SET @Pattern = @Pattern + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR (5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31)

SET @loop = 55296

WHILE @loop < 57344

BEGIN

SET @Pattern = @Pattern + NCHAR (@loop)

SET @loop = @loop + 1

END

SET @Pattern = @Pattern + NCHAR(65534) + NCHAR(65535)

SET @Pattern = @Pattern + ']%'

SET @sSQL = ? + '''' + @Pattern + '''' + ?

EXEC (@sSQL)

After this step, I do some logging and such that isn't important to this question, so I'll stop it there.

Now, the reason why I am using the VBScript to create SQL1 and SQL2 is because I cannot include @Pattern in dynamic SQL. (Must declare the scalar variable "@Pattern".) I have to use dynamic SQL because I am changing the table and/or column each time as I iterate over my list.

I am open to any other suggestions or different approaches. I've been scratching my head over this for a couple of weeks now.