I have the following query:

exec sp_executesql N'SELECT * FROM table  WHERE ( ( ( 
myfield = Id1 AND myOtherField = @Id2)))'
,N'@Id1 nvarchar(50),@Id2 bigint',@Id1=N'default 
value',@Id2=1

This is obviously very trimmed down. Notice that @Id1 equals the value "default value", and the space corresponds with a line break in the text. This is the way it shows up in the SQL Profiler.

This query does not return a value (in a query window or in the app), but if I remove the line break, it does return one row ("default value" is actually a valid primary key. I am amazed, as I would think that the query text would get passed to the provider as a stream, or at least that it would not introduce line breaks.

Anyone else ever run into this? Is this a known problem?

Thanks for any insight,

Phil

Most probably a line break is passed to the @Id1 value. Please debug it and check the value the application passes.

Would you please post the code that formulates this query?

Walaa wrote:

Most probably a line break is passed to the @Id1 value. Please debug it and check the value the application passes.

Would you please post the code that formulates this query?

It's part of a complex prefetch path, but the line of code is:

IPrefetchPathElement2 myNode = myParentNode.SubPath.Add(ParentEntity.PrefetchPathChildEntity);

I don't think there could be a line break in the parent node's FK field, because the relationship is enforced in the database.

Whick LLBLGen Pro runtime library version are you using?

Walaa wrote:

Whick LLBLGen Pro runtime library version are you using?

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 2.0.07.0424

SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll 2.0.07.0129

Adapter vs. SQL Server 2005

Would you please try the latest available version of LLBLGen Pro?

Have you had the DQE Compatibility mode set to SQLServer 2005? (ref: LLBLGen Pro manual "Using the generated code -> Application configuration through .config files")

Walaa wrote:

Would you please try the latest available version of LLBLGen Pro?

Have you had the DQE Compatibility mode set to SQLServer 2005? (ref: LLBLGen Pro manual "Using the generated code -> Application configuration through .config files")

Same result after updating the runtime libs (both the ORMSupportClasses and DQE) and changing the compatibility mode to 2005.

Was this a known problem that a recent build fixed?

It's pretty easy to reproduce, although you have to fiddle with it.

1. Use an entity with a PK that is a varchar/nvarchar (it's nvarchar in my case), put a space or a number of spaces in the middle of the column value ("my entity")
2. Add it to a parent entity using prefetch paths
3. Grab the query from SQL profiler
4. Now manipulate the generated query so that the "word wrap" of the query causes the text to break in the middle of the entity's PK field value. By "manipulate the query", I mean add filters and/or sort clauses do that the right amount of text gets added to the query to cause the word wrap problem.

I call it "word wrap" because that's the way it appears to me. I have no idea how the text actually gets transmitted to the database.

Thanks,

Phil

We don't alter any value whatsoever so I have no clue why the CRLF is in the value. Your repro steps are ok, but actually, I want real code to reproduce it. Also, if you could enable DQE tracing and check what the parameter value is, (which are listed in the query text), it would be great.

Also, double check if the value contains a line break where it is set to the field. (but I assume you already have that), I assume 'default value' is a value you set ?

Btw the query you see is the one send by SqlClient, not the one produced by LLBLGen Pro.

Ugh, I put together a repro case, was able to get the SQL to emit as I wanted it to, and verified that cutting and pasting the SQL resulted in no row being return.

Then I ran the code through the application, and the entities all fetched without problem.

So I think the space thing was just a coincidence/red herring when this problem first appeared.

It just so happened that another SQL oddity reared its head at the same time. This was something I was not familiar with until now. Basically, the problem has to do with the case of the PK/FK fields. If the cases are not the same, certain queries won't properly return rows.

Sorry for the confusion.

Phil

Edit: Looks like the case sensitive issue was answered here:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4755&HighLight=1

Again, it was a huge coincidence that this came up and the space in the parameter happened to break across a line in SQL Profiler.

Ok so you're good now ? (just to be absolutely sure ) Yes the case thingy is something to look out for...

Otis wrote:

Ok so you're good now ? (just to be absolutely sure ) Yes the case thingy is something to look out for...

Yep, turns out I just need to make sure to enforce capitalization between string-based FK/PK relationships (which has nothing to do with LLBL per se).

Thanks!

Phil