 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server CE / March 2008Search fails, but only for a specific sequence
This is weird! My medical database works just fine and has done since ages, until someone treid to look for a medicin begining with 'ney...' Major Error 0x8007007A, Minor Error 0 > SELECT * FROM t_artikel WHERE NAME LIKE 'ney%' Der an einen Systemaufruf übergebene Datenbereich ist zu klein. OK, so it's a German installation, but the issue is not hardware relevant: same on any Mobile device and on my PC. Varying the search string usually works, like 'ne%' is OK and 'n_y%' is ok, but 'new%' fails as does 'ney_%'. Can anyone tell me what is going on here? Thanks in advance. What are the medicines you would expect to find in that range of values? I'm guessing that accented characters specific to German might be causing the result you're seeing. Assuming that you have an index on Name, do you get the same result using TableDirect access? The reason I ask is that using TableDirect no longer uses the query processor but it does still use the index order.  SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Solid Waste Management > This is weird! My medical database works just fine and has done since > ages, [quoted text clipped - 12 lines] > > Thanks in advance. Hallo Ginny, Using TableDirect would imply moving the entire table into the PDA's memory and paring it there with C#... then why bother with the database? I do not think there are any strange characters causing the system to go crazy - the looser search for 'ne%' works and includes everything that should be returned by 'ney%'! I have created a workaround by adding the DESC parameter to the index definiton (yes, the name colunm has it's own index), though I rather fear this has simply moved the bug to a new location (I wonder which customer will discover it first?). > What are the medicines you would expect to find in that range of values? I'm > guessing that accented characters specific to German might be causing the [quoted text clipped - 20 lines] > > > > Thanks in advance. Puddles, You wouldn't load the entire table into memory with TableDirect since you'd specify an index name and range. But in any case since adding DESC changes the behavior to the one you want, I'd expect there to be a value somewhere between 'ne' and 'ney' that is causing the result you see. I think if you look at the names in that range you'll get more insight what exactly is happening. If you do, be sure to tell the rest of us! SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Solid Waste Management > Hallo Ginny, > [quoted text clipped - 40 lines] >> > >> > Thanks in advance. Hallo Ginny, neat tool, this TableDirect... how come I had never discovered it in over three years of Mobile development? Huge speed advantage over SQL queries, but (and it is a big 'but') there is no easy way to implement DISTINCT. Three options are 1. a new table with only unique elements (blows the database size), 2. filter row for row in C# (slow) or 3. use the ToTable() function of a DataView (memory hungry). Hummm, now it looks like the SQL query is infact the better option! All very interesting and my original issue has solved itself anyway: a new production with changed data (happens every 14 days) - in effect the same as producing the index using DESC. But I think this is ignoring the real problem - the index was faulty and this must be regarded as a bug by the developers at Microsoft! Who know where it will appear next! > Puddles, > [quoted text clipped - 49 lines] > >> > > >> > Thanks in advance. Well, TableDirect is a bit of a secret ;-) but I think people who do know about it don't use if often (including me) because using T-SQL is more natural for most developers and easier. It is fast though, as you found, and in low memory situations it can be the only viable option. I don't know if your index was faulty or not since you didn't show a list of the data values that were indexing "incorrectly". If the index is wrong, I can report that to Microsoft to get it resolved, but they'll want to see a reproducible example. SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Scalehouse and Billing Software for Waste Management > Hallo Ginny, > [quoted text clipped - 77 lines] >> >> > >> >> > Thanks in advance. Hallo Ginny, Obviously I could give you my database to test - but it is over 50Mb big! Please let me know how I can deliver this. I did look at the data relevant to an index over this area of the data and I can assure you there was nothing fishy there! Basically, if the data was bad, why is it being delivered without a problem using the shorter LIKE string? Apart from that, what iconstitutes a special character in a Unicode database????? > Well, TableDirect is a bit of a secret ;-) but I think people who do know > about it don't use if often (including me) because using T-SQL is more [quoted text clipped - 87 lines] > >> >> > > >> >> > Thanks in advance. What I need is for you to provide a very small app that creates the database and adds enough values to demonstrate the issue. I'm not doubting your word or anything like that, I just need something to pass along to the developers. I'd be interested in any characters in the key column with values > 127, but whatever it takes to reproduce the problem is what I need. Thanks, SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Scalehouse and Billing Software for Waste Management > Hallo Ginny, > [quoted text clipped - 119 lines] >> >> >> > >> >> >> > Thanks in advance. Hallo Ginny, like I said, a SPECIFIC production of my database displays this unusual feature: either you take this database in it's completness or nothing! A connection from SQL Server Managemant Studio is all you need to issue a select with the specific LIKE values. > What I need is for you to provide a very small app that creates the database > and adds enough values to demonstrate the issue. I'm not doubting your word [quoted text clipped - 127 lines] > >> >> >> > > >> >> >> > Thanks in advance. > Hallo Ginny, > [quoted text clipped - 162 lines] > > > >> >> >> > Thanks in advance. If it's a specific DB among many that behaves this way, it could be due to corruption. DB's have been known to get corrupted, especially the index. - Jin Good point, Jin. SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Scalehouse and Billing Software for Waste Management On Feb 14, 9:52 am, Puddles <Pudd...@discussions.microsoft.com> wrote: > Hallo Ginny, > [quoted text clipped - 205 lines] > > > >> >> >> > Thanks in advance. If it's a specific DB among many that behaves this way, it could be due to corruption. DB's have been known to get corrupted, especially the index. - Jin Hi Ginny , Puddels, This sounds pretty much like the issue I run into useing the OleDB provider and SqlServer CE. I found a limitation on characterfields > 127 also. See the thread at http://groups.google.nl/group/microsoft.public.sqlserver.ce/browse_thread/thread /93ae1aded52d6020/3d6f1b571ff3a0e2?lnk=gst&q=erik+127#3d6f1b571ff3a0e2 or http://tinyurl.com/23dgru Erik > What I need is for you to provide a very small app that creates the > database and adds enough values to demonstrate the issue. I'm not doubting [quoted text clipped - 133 lines] >>> >> >> > >>> >> >> > Thanks in advance. Sorry, I have misread, and confused Charvalue > 127 with Char Columnwidth > 127 Erik Hi Erik, Yes the column width bug with the OleDb provider is a strange one, but easily reproducible. Since I don't know what the data values that Puddles is seeking aren't found correctly, I can't tell if the char values > 127 might be a factor or not. SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Scalehouse and Billing Software for Waste Management > Sorry, I have misread, and confused Charvalue > 127 with Char Columnwidth > > 127 > > Erik Hi! We ran into this problem ourselves just recently. In our case a select with a single where condition fails. The condition is a simple 'like' against an indexed nvarchar(255) column. If we change the direction of the index to desc, it works with one value, and fails with others, so you're right about changing the direction is not a perfect solution. Our best advice is to use parameters instead of plain text based sql statements. If you use a parameter to construct your where condition it always work with any value. The only drawback is that the query becomes somewhat slower. We think the whole problem is MBCS related, it has something to do with collation, string comparison or alike. I'd guess someone forgot to multiply the buffer size by 2 somewhere... If you look for this error code you'll find some MBCS and COM related issues. We still join you in waiting for the exact answer as to the root of this problem and a perfect solution as well. > This is weird! My medical database works just fine and has done since ages, > until someone treid to look for a medicin begining with 'ney...' [quoted text clipped - 10 lines] > > Thanks in advance. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.