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
SET @Pattern = @Pattern + NCHAR (@loop)
SET @loop = @loop + 1
SET @Pattern = @Pattern + NCHAR(65534) + NCHAR(65535)
SET @Pattern = @Pattern + ']%'
SET @sSQL = ? + '''' + @Pattern + '''' + ?
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.