Create query from 2 different SQL sources into datagridview

peterg2000

New member
Joined
Aug 21, 2019
Messages
4
Programming Experience
10+
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

Senior Programmer
Joined
Mar 7, 2014
Messages
17
Location
UK
Programming Experience
10+
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?
 
Last edited:

peterg2000

New member
Joined
Aug 21, 2019
Messages
4
Programming Experience
10+
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

Senior Programmer
Joined
Mar 7, 2014
Messages
17
Location
UK
Programming Experience
10+
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

New member
Joined
Aug 21, 2019
Messages
4
Programming Experience
10+
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

Senior Programmer
Joined
Mar 7, 2014
Messages
17
Location
UK
Programming Experience
10+
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?
 
Top Bottom