No data returned from SELECT when a field contains an ampersand? (SQL/ODBC query)

Business Central/NAV Developers Forum
1

Hello,

I upgraded to the Microsoft Dynamics NAV ODBC Driver v6.0.32964.00 and got the following strange behaviour. Lets say I want to select an entry from a table. If I use something like this and the entry with the name ‘Meier & Sohn’ exists

SELECT Nr_,Name FROM Kreditor WHERE Gesperrt=0 and Name LIKE 'Meier & Sohn’

a result will be returned. However, if I change the statement to:

SELECT Nr_,Name FROM Kreditor WHERE Gesperrt=0 and Name='Meier & Sohn’

NO RESULT will be returned.

  • this only happens, if the field contains an ampersand “&”.

  • everything worked fine before the update

  • the 2nd statement works fine in Oracle and MS SQL server using different tools (and it really SHOULD work anyway)

  • the problem can be reproduced using different clients (MS Excel, MS Access, Lotus Notes R7.03 and R8.53, Squirrel SQL Client)

Some background:

  • we updated the Navision server to v6, but kept the database at v5. According to our Navision support partner this approach is fine.

  • database will be upgraded in a while, so (hopefully) this might be a temporary thing

Questions:

  • did that happen to you before?

  • could/can you solve the problem or can you give any hints on what is going on here and where to look for a solution

Any help is appreciated, thanks in advance,

Th.

2

& is the concatenation sign in SQL.

The first query works because “Meyer & Sohn” CONTAINS both “Meyer " & " Sohn”, the later fails because no

record has an name that is both “Meyer " and " Sohn”…this is impossible, sinve a field cannot have two different

values simultaneously