Database Joiner with tables on different MS-SQL databases

KNIME Analytics Platform
#1

Hello,

I am trying to join two tables from two different databases that are running on the same MS-SQL Server.

Technically that is possible, but KNIME gives me the following error:
"Database URL is different"

The connection strings of the two database table connectors are:

jdbc:sqlserver://;servername=st-svpntppdb01\svp;databaseName=BSRPPDBSVPSTAGE_DB3;integratedSecurity=true
jdbc:sqlserver://;servername=st-svpntppdb01\svp;databaseName=BSRPPDBSVPSTAGE_MPDV;integratedSecurity=true

Is there a workaround for it?

Thank you and kind regards
Florian

#2

In general it's not possible to join tables from different databases. Some databases support it but this is a database specific feature that is not supported by general frameworks such as JDBC.

#3

I got it running! The trick is to move the database name to the SQL statement!

jdbc:sqlserver://;servername=st-svpntppdb01\svp;integratedSecurity=true
jdbc:sqlserver://;servername=st-svpntppdb01\svp;integratedSecurity=true

Example SQL database 1:

SELECT
	CAST(substring(auftrag_nr,5,8) AS INTEGER) PO, 
	a_status
FROM BSRPPDBSVPSTAGE_MPDV.dbo.a_auftrag_status

Example SQL database 2:

SELECT
        CAST(SUBSTRING(JOB.code,5,8) AS INTEGER) PO, 
        JOB.BILLET_ID 
FROM BSRPPDBSVPSTAGE_DB3.dbo.JOB

Now you are able to join!

Kind regards
Florian