I have a query which contains diacritics. The table being queried has fields set to NVarChar. The query is built up in a VBScript page, and is (somewhat simplified):
select l.id,l.comp,l.address3,l.address4 from log l where l.ID>0 and (address3 like N'Münster%' or address4 like N'Münster%')
The query works fine in SQL Server studio, but returns no rows when exactly the same query is sent through an ADO connection:
As far as I can work out, the problem is that the ü seems to be changed to something else - though not a u, because then it would return a record on the database that I'm using (if I change ü to u in the ADO search then I get that record returned).
If I put collate SQL_Latin1_General_CP1_CI_AI in the query:
select l.id,l.comp,l.address3,l.address4 from log l where l.ID>0 and (address3 collate SQL_Latin1_General_CP1_CI_AI like N'Münster%' or address4 collate SQL_Latin1_General_CP1_CI_AI like N'Münster%') and status <> N'DL' order by l.comp
then I get nothing returned, but if I use that query but with Münster changed to Munster then I get the records that I would expect.
Again, if I use these queries in SQL Server Studio then they do exactly what I would expect them to use.
How do I get the diacritics sent through the ADO query correctly, please?
Thanks for looking at my question.
The problem seems to be more to do with what character is passed through from the ADO request to SQL Server - the ü in Münster is being changed to something else - not even u. I need to do something at the code end, rather than the SQL Server end, I think.