With SQL Server I run this question quickly and easily...

SELECT SUM(Esi) AS Dispo FROM [mdb].[dbo].[Query1] AS A
                INNER JOIN [mdb2].[dbo].[TieCol] as B ON A.Alias=B.IDAlias 
                WHERE Alias LIKE 'SETUP%'

I join two tables that live in two different databases (mdb and mdb2). But how do i get it done during my .Internet application? When I have to make use of this statement

string cmdText = "SELECT SUM(Esi) AS Dispo FROM [mdb].[dbo].[Query1] AS A
                INNER JOIN [mdb2].[dbo].[TieCol] as B ON A.Alias=B.IDAlias 
                WHERE Alias LIKE 'SETUP%'";

this.OP = new SqlConnection(ConfigurationManager.ConnectionStrings["mdb2"].ConnectionString);
SqlCommand sqlCommand = new SqlCommand(cmdText, this.OP);

However I can't perform line like code...since this.OP may be the link with mdb2... As well as for mdb?

But how do i connect with both database simultanously (mdb and mdb2)?

Thank you.



The SQL Connection would be to the server - the first Catalog inside a connection string reacts like 'use' - it sets the default DB.

So that your 3 part sql query should work 'as is'. So possibly

  1. Make certain the SQL login utilized by your application (or even the account of the AppPool if using Web and Integrated Security) has got the necessary use of both databases. (use RunAs on SQL Enterprise Manager because this account and check out run the query)
  2. You could try getting away [Alias]
  3. Also, if there's coupling between mdb1 and mdb2 (e.g. SPROCS in mdb1 use tables in mdb2 etc), for easy maintenance, you may attempt to add sights in mdb1 for mdb2 objects. This enables for simple identification of mix-database dependencies. Within this situation, your query may use sights which look as if they are within the same database, even though underlying reliance upon mdb2 remains.

I am unsure if there's a method to do that inside the connection string. However, you can most likely get it done utilizing a four part mention of table. [server].[database].[table].[column]