SQL search fails on database server

[PeteC]

I have always used EAP/EAPX files for models. We are looking to use a database server and searches that previously worked are failing "Invalid column name 'Requirement'". Strangely Requirement is not a column name but a test value.

I can't find anything in the guidance on searches that mentions differences, although I vaguely recall seeing something on these forums (but can't find that either).

What are the differences between file based models and those on an SQL server that cause issues? How can I change searches so that they work on either file or database hosted models?

[Geert Bellekens]

You should look for differences in SQL syntax between MS Access and SQL Server.

The error you mentioned is probably due to the fact that MS Access allows the use of double quotes as text indicator (standard SQL allows only single quotes)
So if you write something like

Where t_object.ObjectType = "Requirement"

You will get such an error. Change that to

Where t_object.ObjectType = 'Requirement'

and it will work in both cases.

Try to stay as close as possible to the ANSI SQL standard, and you will have the least problem.
If you can't avoid it, there are macro's you can use to specify which part of the query to use for which database type.

Geert

[PeteC]

Thanks Geert - double quote is the issue. Where do I find the macros you mention in case I need them later?

[Geert Bellekens]

Thanks Geert - double quote is the issue. Where do I find the macros you mention in case I need them later?

The manual

https://sparxsystems.com/enterprise_architect_user_guide/15.2/navigation/creating_filters.html

Geert