Wildcard in Dynamic SQL
-
We have been running an ad hoc report generator for some time. This morning is began to error.
This is a portion of the code that is erroring: " AND type LIKE 'OBJ%'"
When I select on this part of the dynamic code I get the following return: AND type LIKE 'OBJ%
For some reason the final single quote is missing... I have incorporated SET QUOTED_IDENTIFIER ON into the stored procedure to no avail. I have also tried numerous iterations of adding the wildcard via ASCII character assignment [CHAR(37)], etc.
It looks as if the wildcard character is the problem. We have tried removing it and the final single quote shows up. We have moved it to other locations, and it wipes out the final single quote.
Is anyone aware of a similar problem or better, a solution?
Thanks in advance.
I wasn't born stupid - I had to study.
-
Can you post the code you are using??
-
Also check the length of variable that holds dynamic SQL.
Regards,
gova -
You got it govinn! I am an idiot....
What I don't get is, why did this work for so long?
Sorry for wasting your time...
I wasn't born stupid - I had to study.
-
Always the simplest things that trip you up. Probably worked for so long because the parameters were few and short when called... or maybe you just didn't hear about it untill now.
-
I know and I hate it when I find out it was that simple.
Actually, this is one portion that is a "hard-coded" section of the dynamic sql, (it is pasted together with the choices from the clients). It is probably the most commonly used section, that is wild that it did not error until now... wierd.
I wasn't born stupid - I had to study.
-
Now that you've been humbled, we'll send you on your way to the next bug
. -
That length of variable bug is sometimes hard to find. I had a varchar(8000) field truncating to 4000 characters. As soon as I cast all of my nvarchar variables I was adding to it to varchar, my problem went away.
-
What gets me is that is worked for months! This is probably the most common of the hardcoded dynamic SQL used in this program.
The place I contract uses a front end call SQL Programmer and it often covers programming errors - I have been finding this since I started and used Query Analyzer to check other peoples code. I am guessing this covered this error, (I had to re-write someone else's code).
But to all of a sudden through the error really intriqued me! The DBA's may have installed a patch or something else that I am unaware about. It just was quite intriguing - and as Remi is want to say, humbling... when you find the remedy is such an easy fix... oil well....
I wasn't born stupid - I had to study.
-
SQL Server version 6.5 we face a lot of problems like this. There If the table has datatype varchar(10) and if we insert a 11 character string it doesn't complain. But takes 1st 10 characters.
When we migrated to SQL Server 2000 it gives an error(Data truncated blah blah) when we do this.
May be in SQL 2005 assigning the value to a variable also (that is truncated) gives an error.
Regards,
gova -
Hi Farrel,
Still working with the dynamic stuff then, good man.
A bit late in this thread I know but......
It's always well worth just doing a PRINT LEN(@DodgyVariable) just to double check it isn't near its limit.
I tend now to build with a variable for each part of the statement now e.g. 1 with the Select, 1 with the From and 1 with the Where. Then throw them all together at the end - EXEC sp_executesql @Select + @From + @Where - I find it much easier to debug in long winded queries....
Have fun
Steve
We need men who can dream of things that never were.
-
Isn't sp_executesql limited to 4000 characters?
-
I had the same issues. I solved it by created 2 variables. One held the unchanging SQL and the secon held the dynamic portion. I concatenated them as I calle the exec/sp_executesql.
As for the char limit, I have passed it 7500 thus far in some of the queries with success.
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
I don't find reference to length that sp_executesql will accept. I only that in one specific portion of this stored procedure - Steve and I went back and forth on how to populate @Variables within a stored proc since execute and sp_executesql will spawn its own SPID.
In this instance, I was using EXEC( @sql), so I had up to 8000 characters. In versions past, (6.5 for example) I had to concatenate @sql too so I could pass more than 2000 characters.
This was just a strange little glitch. It should have failed long ago....
I wasn't born stupid - I had to study.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply