Q:

Join two tables from separate database

How can i open db connection from stored procedure. is it possible ? is there any command to do this ? 
I want use two db in one query but  ef return error for using two dbcontext. How can i join two different db's table anyway?
from a in dbContextArch.Archive
          left join dbContextMain.Envelope
          on 

A:

The best way of doing it is by using a Synonym, which will be mapped to the table on the other server. It can be created as below;
CREATE SYNONYM [dbo].[SYN_dbContextMain] FOR [dbContextMain].[dbo].[Envelope]

Then in your queries, all you need to do, is to use the synonym created instead of the server name;
from a in dbContextArch.Archive
left join SYN_dbContextMain
on ..

