Find text on server not working with MS-SQL

Hi,

as in the subject, I tried to search some text using "Find text on server", but the queries are built using "concat" that is not recognized by MS-SQL.

You shoud use '+' instead, I think.

I'm not a fun of MSSQL, but have to use it for a customer building a business intelligence system and that features is invaluable for this kind of work: "where damn the ccounting software stores that datum?"

Many thanks for your hard work!

Yes, I know, that's missing in MSSQL mode. The MSSQL dialect is so different to MySQL, it's a mess to support each and every feature in HeidiSQL for MSSQL also. But I guess I could have a look and fix it if it's simple.

Thanks for your quick answer.

Should you need somebody to test it for you I'll be happy to.

Hi,

I've just downloaded 8.3 version but sedly this function is still broken for MSSQL.

As I can see the problem is just for the statment:

CONCAT(ROUND(100 / 1 * COUNT(*), 1), '%') AS "Relevance"

as "concat" isn't accepted by MSSQL.

I'm working on a database (not mine!) with hundred of tables and finding the right place for information is makeing me crazy!

Please, put an hand on your heart and help me!

Regards

pierpa

You could help too, by posting ideas how to compose the same query for MSSQL. Ideas?

Hi,

thanks for your answer.

The solution maybe something like:

CONVERT(VARCHAR(10),ROUND(100 / 1 * COUNT(*), 1) ) + '%' AS "Relevance"

HTH

Also, I need to remove the "USING utf8" from the "LOWER(CONVERT(`funcname` USING utf8))"

Done in r4743. Please test.

Hi,

many thanks for your effort!

Indeed it works!

There are just a couple of minor problems left:

- the "lower" function doesn't work whith "text" field type so the query returns a syntax error. As in MSSql text matching is case insensitive by default, I beleave the function may be left over without problems.

- if I flag the "Case senitive" search HeidiSql uses the "like binary" operator that, again, doesn't exists in MSSQL. I really don't care about that, but should you will to fix this problem maybe you can rewrite the query

WHERE "CodCondizPagEsolver" LIKE BINARY '%causale%'

as

WHERE "CodCondizPagEsolver" LIKE '%causale%' COLLATE SQL_Latin1_General_CP1_CS_AS

Thanks again.

Done in r4744

Perfect, all the queries are accepted now.

Just a little problem with the "case sensitive" search: even if I write the search string in uppercase, in the query the text is always lowercase... so the search is useless with uppercase text.

Anyway this isn't a problem at all for me and I'm very grateful for your help.

Also done, in r4745.

Thanks for the quick and consistent feedback!

Thanks!

All right now... case sensitive search work correctly.

It was really a pleasure to work with you and I'm happy to have somehow contributed to make your program even better.

Regards