February 12, 2009 at 3:05 am
Hi there
I hit my head on a strange behaviour of SQL Server: I have a large query fired from a .NET application. First, I realized, that .NET gets nothing back, while my test in Management Studio returned rows. Then I looked at the query in detail, reshaped them and found the difference:
The declare statement from .NET has sizes set for the varchar parameters, while my test in SSMS has not. That is the only difference. :w00t:
If i declare the varchar parameters in my testquery with size (.NET sets the size to the length of the value-string), the query also returns empty set. Independently of the size (even if I set them to the size the columns have in the tables or to max or the default size of varchar when not declared).
Also interesting is that it depends on the paramter: I can remove the size setting of one or two of two varchar parameters and the behaviour changes. Size setting of the third varchar parameter is not influencing the result. Difference: This third parameter concerns another table from the joins of the query. If I remove parts of the join (i.e. the table with the third varchar parameter, I always get a result.
It does also not depend if I execute the query by sp_executesql or declaring the variables and then running the T-SQL directly.
And another dependency: If I change the values of the parameters, the query gives results in both variants. I'm very confused!
Can anybody explain this change in behaviour? Why doesn't SSMS warn me about that if it si prevented from returning values by the size setting? Why does this change the behaviour?
Are there any problems with querylength? But then again, why doesn't SSMS warn about that? If I really have not to give sizes: How do I tell .NET not to set them? Because in the code I only give type varchar.
At the moment I cannot post the query itself because of table-, column- and parameternames representing our logic, but If needed I can rename these, still keeping length of the names in case of this beeing the problem?
Thanks in advance for any suggestions or other participation,
Chris
February 12, 2009 at 10:30 am
I can think of a few possibilities, but this is hard to comment on at such an abstract level. Could you post the query for us? That would make it much easier. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 13, 2009 at 1:44 am
Query is something like:
declare @p1 varchar(4), @p2 varchar(6)
set @p1 = '%ZH%'
set @p2 = '%8434%'
select * from table1 o left join table2 t on o.x=t.x
where o.canton like @p1 and o.postcode like @p2 and ...
If I drop (4) or (6) from above query, it outputs result rows. With both set, it does not.
After a long day I found the solution.
And I'm ashamed to tell, but the SQL Server must behave like this, since the query is not the same: What I was not aware: If you do not set the size of a varchar parameter, SQL Server defaults to varchar(1). Since in the query the two parameters affected by the size problem were like '%8434%' or like '%ZH%', this default has set the parameter value to '%', which is true for any string and therefore the query had results. In the other way, if I set the varcharsize or if it was set by .NET, the value of the parameter has been '%8434%' and then the output of no result rows simply was correct. I assumed it to be wrong since my query did return values... :unsure: Letting away a join returned results because some restricting where clauses got removed by removing the join.
So sorry for troubling, but I really learned something out of that and I think some of you will too 😉 I'm glad having found a forum post somewhere deep in the net of a user pointing to the fact of default size 1 and wondering, where this strange default of 1 comes from...
Cheers, Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply