none
create query from 2 different sql servers to use in gridview RRS feed

  • Question

  • I need to create a new dataset from 3 tables and 2 SQL sources 1 MSSQL and the other MySQL.

    file 1 contains 30 fields with the index on a network number say 'NetworkID'. (3000 records) file 2 contains 8 fields with the index also on a Network number say 'NWK_ID'. (1,900,000 records) file 3 contains 3 fields with index on field 'code' (50 records)

    In the dataset designer I placed relationships between file 1 'Network' and file 2 'NWK_ID'. and another relationship between file 3 'code' and file 2 'activity'

    The reason for the first relationship is to only include records from file 2 that have a record in file 1.

    The 2nd relationship is to show the code type based on the activity code!

    I just can't figure out how to get these combinded into a query! This is code being ported over from an Access application!I have no problems doing it there!

    SELECT MSSQLDB.NetworkID, MySQLDBA.activity, MySQLDBA.cap_plan, MySQLDBA.cap_act, MySQLDBA.cap_var, MySQLDBA.exp_plan, MySQLDBCode.typedef
    FROM (MSSQLDB INNER JOIN MySQLDBA ON MSSQLDB.NETWORKID = MySQLDBA.NWK_ID) INNER JOIN MySQLDBCode ON MySQLDB.activity = MySQLDBCode.code

    So now I have a dataset1.xsd file via the dataset designer with 3 tableadapters with relations applied but can't seem to figure out how to build a new table with parts of the 3 adapters!

    In case you haven't noticed I'm new to vb.net! I'm using VS2015 as VS2019 has too many bugs!

    Thanks
    Wednesday, September 25, 2019 4:21 PM

All replies

  • Hello,

    I have done this but using "Linked Servers" should be looked at. Here is a walk through or look at OPENQUERY.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, September 25, 2019 5:44 PM
    Moderator
  • Hi,

    Do you resolve the issue? If you resolve the issue, please mark the helpful as answer. It will be beneficial to other community.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 30, 2019 9:47 AM
    Moderator