none
How to join multiple tables in diffrent and same database

    Question

  • hi

    I have a problem i need to join two table from different sql servers and as a test i have tried to get data from two table but i realize

    the adapter can have only one connection statement as argument how do i get records from two table from diffrent database

    Please see my code

     Dim adapter As SqlDataAdapter
        Dim ds As New DataSet
        Dim myConnection As SqlConnection
        myConnection = New SqlConnection("Data Source=localhost\SQLEXPRESS;Initial Catalog=test1;User ID=sa;Password=deedee;pooling=false")
        myConnection.Open()
        adapter = New SqlDataAdapter("select * from emp", myConnection)
        adapter.Fill(ds)
        GridView1.DataSource = ds.Tables(0)
        GridView1.DataBind()
        myConnection.Close()
    

    1)is there a way to get all the data in to the ds and make alterations with it

    2)can any one suggest me how to do the same with two sql servers as well

    Thanks Roma

    Sunday, September 26, 2010 11:19 AM

Answers

  • Hi,

    If it's two different sql servers, then first you have to link them. After the linked servers are setup, you just have to prefix your tables names with server name, database name, table owner name in your SELECT queries. The following example links SERVER_01 to SERVER_02. Execute the following commands in SERVER_02:

    EXEC sp_addlinkedserver SERVER_01
    GO

    /* The following command links 'sa' login on SERVER_02 with the 'sa' login of SERVER_01 */
    EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SERVER_01', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'sa password of SERVER_01'
    GO

    SELECT a.title_id
    FROM SERVER_01.pubs.dbo.titles a
    INNER JOIN SERVER_02.pubs.dbo.titles b
    ON a.title_id = b.title_id
    GO

    Thanks

    Binze


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 30, 2010 9:35 AM

All replies

  • Hi Roma,


    Did you try, it I'm not aware of any problem to create more connections to different database Servers.

     


    Success
    Cor
    Sunday, September 26, 2010 11:42 AM
  • you can connect but i the two tables have diffrent columns and diffrentt databases

    in one table there are columns like  ID, NAME, DIVISION,DESIG and in another it has ID,EMAIL,APPROVED

    what i need is to query this two based on id and get the details in the dataset as ID NAME DIVISION, DESIG,EMAIL,APPROVED

    is it possible to create a ds with columns of two tables in two Database Servers

    Sunday, September 26, 2010 12:35 PM
  • I have merged it using a command

    ds.Merge(ds1,

    False, MissingSchemaAction.Add)

    but it gives me this result

    id desig  division  mobile  name  email  approved 
    6025 Programmer IT  508138761 
    6017 Network  IT  504446973 
    6015 Network  IT  50888777 
                      remo remo.dave@arg.com Yes 
                      Salim salim@gmail.com No 
                      Sunil Mathew sunil.mathew@keoic.com No 
                      Tyrone D Souza Tyrone@keoic.com Yes yes
    
    the columns of the second table is not merged contiuous it came after the first column i want both continuous like one table
    Sunday, September 26, 2010 1:10 PM
  • probably the best you can do is using Linq to Dataset to join the tables.

    http://msdn.microsoft.com/en-us/vbasic/bb738025.aspx


    Success
    Cor
    Sunday, September 26, 2010 4:24 PM
  • Hi,

    In addition to Cor's answer, you can also try to use stored procedure to achieve this by using the following statement:

    SELECT a.i, a.j
    FROM pubs.dbo.Table1 a
    INNER JOIN
    northwind.dbo.Table2 b
    ON a.i = b.i

    The call this stored procedure on your client application.

    Thanks

    Binze


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 30, 2010 7:07 AM
  • Hi,

    If it's two different sql servers, then first you have to link them. After the linked servers are setup, you just have to prefix your tables names with server name, database name, table owner name in your SELECT queries. The following example links SERVER_01 to SERVER_02. Execute the following commands in SERVER_02:

    EXEC sp_addlinkedserver SERVER_01
    GO

    /* The following command links 'sa' login on SERVER_02 with the 'sa' login of SERVER_01 */
    EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SERVER_01', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'sa password of SERVER_01'
    GO

    SELECT a.title_id
    FROM SERVER_01.pubs.dbo.titles a
    INNER JOIN SERVER_02.pubs.dbo.titles b
    ON a.title_id = b.title_id
    GO

    Thanks

    Binze


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 30, 2010 9:35 AM