Create query from 2 different SQL sources into datagridview

[peterg2000]

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

 

[Sheepings]

That ain't possible. You can't do cross server queries like that.

You have two options.... you can either use federated tables, or you can select the data from whatever server/database engine and try to merge it by inserting from one to the other server. What you're doing is very complicated and should be avoided. Is it possible you could merge databases or at-least have them on the same database?

How did you end up with data on different servers, moreover, how are they on different databases in different engines?

 

[peterg2000]

That ain't possible. You can't do cross server queries like that.

You have two options.... you can either use federated tables, or you can select the data from whatever server/database engine and try to merge it by inserting from one to the other server. What you're doing is very complicated and should be avoided. Is it possible you could merge databases or at-least have them on the same database?

How did you end up with data on different servers, moreover, how are they on different databases in different engines?

As with most major companies you end up with multiple applications on different databases, which is my case. I was hoping to be able to port the report application over from Access VBA
where I linked the databases. Made it easy to write queries across multiple database types. What I've decided to do is create and copy the table into my database (the one I have write privileges)
every time I run the report! At least with a monthly report the overhead for doing this is quite acceptable even though the table has 1.9 million rows!
I was just hoping there was a way to NOT have to copy it over!

Thanks for the reply!
Pete

 

[Sheepings]

I was just hoping there was a way to NOT have to copy it over!

There are no direct ways to do that. Have you looked up federated tables yet? It's likely as close as you are going to come to doing what you want? Let it be known I've not used it with different engines. Only MySQL. Try that and let us know how it goes.

The only other thing would be to download the data you want to query against into arrays of data or sorted collections, and with that you would process the queries for the second server. However, 1.9 million rows will likely cause its own issues, and I can understand why you wanted database to database querying. I would be incline to ask whoever is in charge of your project, to allow you to permanently port the databases onto MySQL engine, and have them all on one server. (Unless there are security reasons for having them separated?) This would make using federated tables much easier.

 

[peterg2000]

OK , I was just looking into linked servers on MSSQL. I don't have the proper credentials to do it via the MySQL side!
I believe its the equivalent to federal tables.
Pete

 

[Sheepings]

It's federated tables - and if you had the credentials for MySQL, its easier, and there is also a lot more information on that topic, as you're not the only one looking for cross server querying, and federated tables makes that a possibility.

Read this one yet? Linked Servers in SQL Server - Might help, lets know if it does?