distal-attribute
distal-attribute
distal-attribute
distal-attribute

Find text on server not working with MS-SQL

pierpa posted 10 months ago in General
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!
ansgar posted 10 months ago
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.
pierpa posted 10 months ago
Thanks for your quick answer.

Should you need somebody to test it for you I'll be happy to.
pierpa posted 9 months ago
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! wink

Regards

pierpa
ansgar posted 9 months ago
You could help too, by posting ideas how to compose the same query for MSSQL. Ideas?

pierpa posted 9 months ago
Hi,

thanks for your answer.

The solution maybe something like:

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



HTH
ansgar posted 9 months ago
Also, I need to remove the "USING utf8" from the "LOWER(CONVERT(`funcname` USING utf8))"
ansgar posted 9 months ago
Done in r4743. Please test.
pierpa posted 9 months ago
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.
ansgar posted 9 months ago
Done in r4744

pierpa posted 9 months ago
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.
ansgar posted 9 months ago
Also done, in r4745.

Thanks for the quick and consistent feedback!
pierpa posted 9 months ago
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

Please login to leave a reply, or register at first.