two Databases in one select statement

Question

User-562051539 posted

"SELECT a.fibunr, " & _

            " a.Jahr, a.buchungnr, a.kto, a.buchdat, a.belegnr, a.soll, a.buchtxt1, a.buchtxt2, " & _

            " (a.buchtxt1 & ' ' & a.buchtxt2) AS buchtxt, " & _

            " a.betrag AS SollBetrag, a.betrag AS HabenBetrag, a.betrag AS Saldo, " & _

            " a.menge, a.gegkto, a.gebdat, a.sammbuchungnr, a.erfassjournnr, a.prov, " & _

            " a.kredibuchart, a.hknkdat, a.buchart, a.journalisiert, a.storniert," & _

            " b.Belegnr, b.Path, b.Request " & _

            " FROM " & dbpath & " a LEFT JOIN  tblBuchhaltungsBeleg b ON  a.belegnr = b.Belegnr " & _

            " WHERE [a.fibunr]=@fibunr " & _

            " AND [a.jahr]=@strJahr " & _

            " AND [a.gebdat]=@gebdat " & _

            " AND [a.sammbuchungnr] =  " & sammbuchungnr & _

            " ORDER BY a.soll DESC; "

I have the following code that works if the two tables are in same DB, but how would I go about it, if the two tables are in two different DB

sql="SELECT a.firstname, " & _

            " a.Jahr, a.pass," & _

            " b.weg, b.pass " & _

            " FROM table1 a LEFT JOIN  table2 b ON  a.pass = b.pass " & _

            " WHERE  [a.Jahr]=@strJahr "

  Using cn As New OleDbConnection(WebConfigurationManager.ConnectionStrings("connection1").ConnectionString)

    '        Using dbcomm As New OleDbCommand(sql, cn)           

Answer 1

User-1460196090 posted

select a.firstname, a.Jahr, a.pass, b.weg, b.pass

FROM Database1..table1 a LEFT JOIN Database2..table2 b on a.pass = b.pass

WHERE [a.Jahr]@strJahr

Database1 is your current Database

Database2 is the other database

use two dots (..) between DatabaseName..TableName

Answer 2

User236141260 posted

Sql queries run on a single databse base. because we specify database name in connection string, so it will run on single database.

Answer 3

User-1460196090 posted

Sql queries run on a single databse base. because we specify database name in connection string, so it will run on single database.

That's not true. We specify the Initial Catalog to define where we want to run the query, but if the user have enough permissions to run queries on other databases, he can do that using the two dots Db..table notation.

If you don't believe me, place GridView in your WebForm and using the following code snippet populate it with data (add your connection string & your command that will select data from tables in different databases)

        SqlConnection con = new SqlConnection(@"your-connection-string");
        SqlCommand cmd = new SqlCommand("select * from Table1 t1 left outer join Database2..Table2 t2 on t1.id = t2.id", con); //Table1 is the table from the Initial Catalog database specified in ConnectionString where Database2..Table2 is the Database2 name of the other database and the table inside it respectivelly
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        con.Open();
        da.Fill(ds);
        con.Close();

        GridView1.DataSource = ds;
        GridView1.DataBind();

All the best ;).

Answer 4

User-562051539 posted

select a.firstname, a.Jahr, a.pass, b.weg, b.pass

FROM Database1..table1 a LEFT JOIN Database2..table2 b on a.pass = b.pass

WHERE [a.Jahr]@strJahr

Database1 is your current Database

Database2 is the other database

use two dots (..) between DatabaseName..TableName

Thank you, that worked

Answer 5

User236141260 posted

Hajan thanks for the correction.

Answer 6

User-1460196090 posted

Hajan thanks for the correction.

Thanks to you too for the contributions you make in here.

Working with queries that demands manipulation of two or more databases leads to security issues, even though, DBA's should create the security rules for users that have permissions to work with the databases. That's why I said, the query will work as soon as the user have enough permissions to work with all databases included in the query statement.

Thank you for the reply.

Answer 7

User-562051539 posted

After it has worked for a long time, my select statement suddenly gives me error in FROM clause, what could be the problem?

Answer 8

User-1460196090 posted

After it has worked for a long time, my select statement suddenly gives me error in FROM clause, what could be the problem?

Hi @pulsmartin,

What error do you get in your FROM clause?

Can you paste the complete select statement you use?

Are your databases still on the same server (it is required!)?

Please give us some more details...

Thank you

Answer 9

User-562051539 posted

I haven't changed anything within the select statement and nothing in the databases.  If I put the two tables back in the same database, the select statement works perfectly, so it has to do something with the part of connecting the two databases.  Here my select statement

 Dim strQueryString As String = "SELECT a.fibunr, " & _

            " a.buchdat, a.soll, a.belegnr,  " & _

            " (a.buchtxt1 & ' ' & a.buchtxt2) AS buchtxt, " & _

            " a.betrag AS SollBetrag, a.betrag AS HabenBetrag, " & _

            " b.Belegnr, b.Path, b.Request " & _

            " FROM Fibubuchung a LEFT JOIN " & cs2 & "..tblBuchhaltungsBeleg b ON  a.belegnr = b.Belegnr " & _

            " WHERE (a.fibunr=?) " & _

            " AND (a.jahr=?) " & _

            " AND (a.sammbuchungnr = ?) " & _

            " ORDER BY a.gebdat DESC "

 Dim strQueryString As String = "SELECT a.nr, " & _

            " a.dat, a.soll, a.beleg,  " & _

            " (a.worktxt1 & ' ' & a.worktxt2) AS worktxt, " & _

            " a.bet AS SBet, a.bet AS HBet, " & _

            " b.Beleg, b.Path, b.Request " & _

            " FROM buchung a LEFT JOIN " & cs2 & "..tblBuch b ON  a.beleg = b.Beleg " & _

            " WHERE (a.nr=?) " & _

            " AND (a.jahr=?) " & _

            " AND (a.samm = ?) " & _

            " ORDER BY a.gebdat DESC "

If they are in the same db and I change the From to the following, it works

" FROM buchung a LEFT JOIN tblBuch b ON  a.beleg = b.Beleg "

Answer 10

User-562051539 posted

I took out an older version of my project (from about 3 weeks ago), where this part is working (still).  I compared this function with the one I have in the current project and it's the same !?! Why is it working there and not working here?

Answer 11

User-562051539 posted

I took out an older version of my project (from about 3 weeks ago), where this part is working (still).  I compared this function with the one I have in the current project and it's the same !?! Why is it working there and not working here?

After trying to figure it out for about 6 hours, i just gave up.  I took my old project, copied all the files of the new project into the old one, and now it works again.  I still don't understand why.

Answer 12

User-1460196090 posted

1. Send me the connection strings of both projects, the new and the old one.

2. Send me the SELECT statements of both projects, new & old.

3. Do both of your projects uses the same database to work with?

Your connection string has to connect to database where 'buchung a' table is  (which I see in your query) - it won't work if you connect to the other database and try to use it in the select statement.

Answer 13

User-562051539 posted

1. Send me the connection strings of both projects, the new and the old one.

2. Send me the SELECT statements of both projects, new & old.

3. Do both of your projects uses the same database to work with?

Your connection string has to connect to database where 'buchung a' table is  (which I see in your query) - it won't work if you connect to the other database and try to use it in the select statement.

 

same connectionstring, same select statement, same db (which I copied and put in both app_data folders).  Thanks for your time, at the moment it doesn't matter anymore, because as said above, I got it to work again.  I only wonder if this could suddenly come up again, if I don't change anything?

Answer 14

User-1460196090 posted

Something got changed, definitely!

- Your databases MUST be in the same server

- The FIRST TABLE in your SELECT statement has to be in the FIRST DATABASE which is in your connection string, while the SECOND TABLE should be in the SECOND DATABASE which is joined in your select statement with dbname..tableName