using wildcards doesn't work (SQL2008SP1)

wic

friday troubles... ](*,)

having following table:

No descr
=========
1000 toto
1010 titi
1234 mibuso
1305 explain
1535 thefive
2000 people
2020 rain
3456 onemore

No = CODE
Descr = TEXT
if you filter out *o only mibuso appears, no toto !!
if you do the same in SQL with the LIKE clause, same result... SQL :bug: ?
Exactly same behaviour with the number (in CODE fields)
if you filter out all thos finishing with 5, ( *5 ) then you only receive 1305 explain... not the 1535 thefive
Everytime that the char after the wildcard is part of the string the record doesn't appear!

My question: HOW to filter out only those finishing with "o" or with 5 or with...?

Any Explanation?

Regards

Chris

wic

I think I've found a "workaround"... maybe a SPx or R3 will fix it...into the near future \:D/

It's a SQL dependend bug.

Change the SQL collation of the DB from Latin1_General_100 to Latin1_General !!

After doing so, I could NOT reproduce the error.

But... a new problem occurs: if you have multiple DB's on the same server (like a dev server), you could have a problem with the schema and owner. (I've something like that!)
Here the SQL errror:
>>
The object 'Cronus$Item Analysis View Budg_ Entry$VSIFT$0' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
The object 'Cronus$Service Line$VSIFT$0' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation....

...and so on...

I didn't find the solution so I created a NEW db and restored the NAV backup into it and THEN changed the collation.

any additionnal infos?
Chris

michael_clarendon

We've had a similar problem where the executing code couldn't find records when running under a SQL2008 SP1 server but worked happily under SQL2005 SP2. Although the collations were different all problems were resolved after applying SQL2008 SP2, which is available for download.