Hi Karen,

I've asked the OutSystems team to take a look at this.

Hi Karren,

Cross-database joins were never allowed because you simply cannot write sql that mixes tables that are reachable with different connection strings (not an OutSystems limitation).

I believe you can only "simulate" cross DB joins if either

1. The second DB server is DB Linked from the first (then its tables appear local to the 1st one, at a performance cost)
2. The two "servers" are actually two database connections to the same physical database (then the underlying connection string is actually the same);

Are you sure you didn't change the query since the upgrade?

Miguel

Thanks for your response.  I am certain that I did not change the query after the upgrade.  We performed the upgrade, opened the app and true change gave SQL Errors where my advanced query was.  We are unable to publish.  Because OutSystems no longer supports this code, I will try to refactor my query to work around cross-database joins.

With regard to cross-database joins, I showed your response to our Sr. DBA.  Below are his thoughts:

In SQL Server cross database joins are typically facilitated by using 3 part naming.

i.e. if I have two databases on a SQL instance, one named foo and one named bar, one with an employee table, and the other with a customer table,

a simple SELECT statement that joins the two tables together would look like this.

SELECT

*

FROM

Foo.dbo.employee a JOIN Bar.dbo.Customer b ON
 a.EmployeeID = b.SalespersonID

Will work every time. SQL Server has supported Cross database joins since at least SQL server 6.5 and continues to support them as of SQL Server 2017.

The only times that a cross database join doesn’t work is when using a contained database (where the security and logins are contained within the database itself, and not at the instance level) and in the case of SQL Azure (which implements SQL server in a contained database). 90% of SQL Server installs today support Cross Database Joins, and probably 70-80% of those installed instances of SQL are likely using them. The final time is when using a memory optimized table.

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/cross-database-queries

https://ppolyzos.com/2016/07/30/cross-database-queries-in-azure-sql-databases/

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-getting-started-vertical

From

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql

<table_source>
 …

If the table or view exists in another database on the same instance of SQL Server, use a fully qualified name in the form database.schema.object_name.

In my 20 years of experience Administering and developing for the Microsoft SQL Platform, cross database joins have and continue to function.

Mark Tassin MCITP | SR. DBA - MCITP/MCSA SQL Server Data Platform

Applied Systems, Inc.

www.appliedsystems.com | mtassin@appliedsystems.com

P: 708-534-5575

Hi Karren,

Your DBA is absolutely right, and what he is describing is what i meant by option 2. When I (more or less haphazardly) wrote "physical database", I strictly speaking meant "database instance" (for SQL Server).

If both your Database Connections (as defined in Service Center) point to the same instance, then I believe the joins should indeed work, provided the extension has the entities' physical table names fully qualified.

If not, you may be right, something may have changed in OutSystems 10...

Regards,

Miguel

Hi Karen,

If you are positive that it worked in 9, but no longer in 10, and it's not in the P10 breaking changes documentation, I'd advise you to contact OutSystems support.

Thanks so much for your recommendation.  We did jump from 9.0.1.25 to the latest version of 10, but we'll let them know.  I was able to work around this issue by changing the advanced query in the preparation to execute a stored procedure.  The stored procedure successfully accomplished the cross-database join and returned the results to the output structure of the advanced query.  

Thanks again for helping us with this issue.