c# - Joining between multiple databases

Question

Update I get an Invalid object name error for the DataBaseA.dbo.templates

I wish to Inner join two tables from seperate databases on the same server (Local).

But i can't seem to get it work. Also i don't understand how i should be handling my connectionstring, as i'm currently setting the current connection to the connectionstring of my database. But in this example i'm trying to connect to two different databases in the same query.

This is my SQL code so far:

"SELECT a.template.*, b.c_template.* FROM DatabaseA.dbo.templates a INNER JOIN DatabaseB.dbo.c_template b ON a.id = b.template_id"

And this is my connectionstrings:

<add name="Unit" connectionString="Data Source=(LocalDB)v11.0;AttachDbFilename=|DataDirectory|DatabaseA.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient" />
    <add name="Content" connectionString="Data Source=(LocalDB)v11.0;AttachDbFilename=|DataDirectory|DatabaseB.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient" />

Answer 1

Do you know If the table "templates" has the schema owner as "dbo" or It is something else. Can you execute the following query and see If you find the table?

SELECT * FROM Unit.dbo.templates.

If you don't find the table, you should use the the following command to find out the schema owner:-

Use [Unit]
Go
sp_help templates

In the below example, the schema owner is dbo. When you execute yours, you would see some other schema owner (e.g xyz). Use that schema owner.

SELECT * FROM Unit.xyz.template

If you are still not able to find the table, It sounds like the default database for the query from the application is set to the database where the table "tenplate" does not exist. Can you try using a different form of connection string. "myUsername" in the below connection string should have same required privileges to both DatabaseA And DatabaseB:-

Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=myPassword; 

Example :-

Server=localDBv11.0;Database=DataBaseA;User Id=UserA;Password=UserAPassword; 

Create a database user named "UserA" and have appropriate access to both DatabaseA and DatabaseB.