SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query works static, but does not dynamically


Query works static, but does not dynamically

Author
Message
joseph.robinson83
joseph.robinson83
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 63
I cannot seem to figure this one out, so I have taken to a forum for assistance.

I have an SSIS package that puts together a series of queries to check all text fields in all tables of a database for a set of "invalid" characters. I know the root query works as we have been running it manually for quite a while. However, now that I have changed it to build dynamically, I cannot figure out why it does not work.

Here is the code. The EXEC (@sSQL) returns 3 records. It just so happens that these 3 records are the only ones in the database with a '?' in the field. The very bottom statement returns 0 records. I cannot figure out what the difference is. When I do PRINT (@sSQL), it looks fine to me, except that @Pattern is fully printed.

Any ideas?


DECLARE @Pattern NVARCHAR(MAX)
DECLARE @sSQL NVARCHAR(MAX)
DECLARE @1 nvarchar(max)
DECLARE @2 nvarchar(max)
DECLARE @loop int

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 @1 = 'SELECT * FROM [datacreator].[M_EventPOI] WHERE PATINDEX('
set @2 = ', CONVERT(NVARCHAR(MAX), LTRIM(RTRIM(Alternate_Name))) COLLATE Latin1_General_BIN) <> 0'

SET @sSQL = @1 + '''' + @Pattern + '''' + @2


--3 Rows Returns; all 3 have '?'
EXEC (@ssql)

--FOR COMPARISON. 0 rows return (which is correct)
select * FROM [datacreator].[M_EventPOI] WHERE PATINDEX(@Pattern, CONVERT(NVARCHAR(MAX), LTRIM(RTRIM(Alternate_Name))) COLLATE Latin1_General_BIN) <> 0


joseph.robinson83
joseph.robinson83
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 63
Basically, I think what is happeneing is that the dynamic SQL is evaluating '?' as one of my invalid characters, even though it's not part of my list. '?' is CHAR(63), which I am not including in my @Pattern. What I can't figure out is why/how it is getting in there for the dynamic version but not for the static.
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7802 Visits: 7142
I'm guessing what is happening is that Latin1 has only ascii 0 to 255, not extended characters. So, the double-bytes are being interpreted as single bytes, one of which is a "?".

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
joseph.robinson83
joseph.robinson83
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 63
The script works fine and properly if I do not build a dynamic SQL input, but it also doesn't actually parse out the @Pattern prior to the PATINDEX in the static query like it does in the dynamic SQL.

I did go ahead and try other collations, though, with the same result. Any idea what collation would accept the unicode extended characters?
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7802 Visits: 7142
Sorry, I have no idea; that's not my area of expertise.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8559 Visits: 7660
Two questions and a clarification:

Clarification: What are you using to build the @sql string? Is this completely encapsulated in an OLEDB or Exectute SQL task, or are you assembling a local variable in a script component first?

Question 1: Have you performed a profiler to confirm the query being sent to the SQL Server is the one you expect?

Question 2: Have you directly confirmed that your character is definately a CHR(63) via ASCII(SUBSTRING())?


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14934 Visits: 18591
The code fails because the first character in the Pattern sequence is NCHAR(0). Any usage of the string will recognise this as the end of the string, truncating the string at this point. The solution is to start at NCHAR(1) instead.
Cool
For demonstration, run first this code
DECLARE @Pattern NVARCHAR(MAX)
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)
SELECT @Pattern


Return value
%[



And then this
DECLARE @Pattern NVARCHAR(MAX)
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)
SELECT CONVERT(VARBINARY(MAX),@Pattern,3)



Return value
0x25005B000000010002000300040005000600070008000B000C000E000F0010001100120013001400150016001700180019001A001B001C001D001E001F00



This is probably way too obvious for one to notice at the first glance;-)
joseph.robinson83
joseph.robinson83
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 63
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.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16081 Visits: 19534
Have you tried using an SC collation? There's info here. Your ? might be the first of a surrogate pair.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
joseph.robinson83
joseph.robinson83
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 63
ChrisM@Work (8/29/2014)
Have you tried using an SC collation? There's info here. Your ? might be the first of a surrogate pair.


I thought supplementary char collations were not in 2k8? When I check fn_helpcollations, I don't see any with _SC. Am I missing something?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search