Hi Kota,

The problem is that you are doing the "Select *" so you are not guaranting that the columns being returned match the order of your entity.
Also in that case Service Studio is giving you a warning that you ignored!!
Doesnt it say "SELECT statements with '*' cannot be validated against the output structure in <query>" ?

Check the help related to that warning, It explains how you should use advanced queries correctly.
https://www.outsystems.com/help/servicestudio/8.0/Errors_and_warnings/Unexpected_Advanced_Query_Warning.htm

Regards,
João Rosado

Hi Joao, 
             It did say that but it worked fine in the development enviroment so I ignored the warning. It started failing on another server. I will try to use SELECT {Entity}.* approach. Thanks.

Regards

One more question. Is this a limitation from the outsystems side or sql server side ? AFAIK If I do Select * from ssms, it gives me the columns in the correct order. Also if an e-space is working on one server why would it show different behavior on another server. 

Hi kota,

We need to dig a bit deeper to really understand what's happening under the hood. Here's a suggest reading: https://www.outsystems.com/forums/discussion/8248/advanced-query-pitfalls-using-alias-and-wildcards/

Cheers

Hi Andre, 
             So if I am understanding it correctly, the mismatch between the order of columns in the entity and the database table is the root cause of it. Right ?

Regards

Right. If you use '*' you have no control on the order in which the columns are returned.

According to this 
https://technet.microsoft.com/en-us/library/ms189287(v=sql.105).aspx

"When an asterisk (*) is used, the order of the columns in the result set is the same as the order in which they were specified in the CREATE TABLE, ALTER TABLE, or CREATE VIEW statements.

Because SELECT * finds all columns currently in a table, changes in the structure of a table (by adding, removing, or renaming columns) are automatically reflected each time a SELECT * statement is executed."

So If the order of columns in the table and order of attributes in the entity match, It should work. 
 

The order of the attributes on an Entity isn't necessarily the order of the attributes in the table. It will only work if you haven't changed, added or deleted attibutes. Bottom line it is never a good idea to use just '*' it is bound to fail sometime in the future, near or far.

"The order of the attributes on an Entity isn't necessarily the order of the attributes in the table."
- I have already checked the order and they are the same.Also why would it work on one server and fail on another server with identical database and e-space.

"It will only work if you haven't changed, added or deleted attibutes. Bottom line it is never a good idea to use just '*' it is bound to fail sometime in the future, near or far."
- The reason behind using "*" was that it adds to the readibility. If you have 20 columns in a table than typing their names in the advanced query is error-prone. Also if you have to use 10 columns at one place and 11 at other , you need to create 2 more structure for output of advanced query which adds to duplication in the project.

1. Because most probably you published the eSpace in different times in the different servers, so the create statements for those entities had the attributes in different order, e.g.:

• published Entity as (id, Name, Email) this creates table with (Id, Name, Email)
• Then rearranged the attributes as (Id, Email, Name) and published again this has no effect in the database because the columns already exist there but the mapping to the entity will fail.
• published this same espace on a new server, now the table is created as (Id, Email, Name) and mapping works again.

2. if you have 10 columns in one case an 11 columns in another you should use different queries and you should really favor using simple queries they'll save you a lot of time as you keep on changing your application. What is really your use case for using an advanced query?