Cannot join tables from different databases

[knobboll]

Hi,

 

I am using MS SQL Server and I need to join two tables stored in two different databases on different servers. The query I run looks like:

 

 

SELECT x.name

FROM hostA.databaseA.dbo.tableA x, hostB.databaseB.dbo.tableB y

WHERE x.id = y.id

 

 

 

I use iReport to design my Report and from within iReport I can run it without problems. But when I move the Report to JasperServer 1.2.1 and execute it from there I get the following error:

 

 

*****************************************************

org.springframework.webflow.engine.ActionExecutionException: Exception thrown executing [AnnotatedAction@bdf98d targetAction = com.jaspersoft.jasperserver.war.action.ViewReportAction@1f0a2a0, attributes = map[[empty]]] in state 'verifyData' of flow 'viewReportFlow' -- action execution attributes were 'map[[empty]]'; nested exception is com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : test02

 

*****************************************************

 

 

If I execute reports where the tables are hosted in the same database everything works fine.

 

Does anyone know what the problem is? Is there a solution to it?

 

Thanks