I have Employee database, Customer database, Employee_address database and Customer database Now i just wanted to pull the data from Multiple database to the single database.
I have Employee database, Customer database, Employee_address database and Customer database Now i just wanted to pull the data from Multiple database to the single database.
My guess is that your users set up an Access database with the people/products or whatever that they are interested in working with and that's why you need to select across the two databases. Create scalar function to accept RequestID and return all corresponding "AdverseParty" values concatenated in one string.
You could still use Access as a frontend to the data AS LONG AS you never exceeded the limit of 2 gigabytes of data when you where running your queries. If that's the case, the Access table is probably smaller than the SQL Server table(s). Assuming Sql Server can get to the Access databases, you could construct an OPENROWSET query across them.
First you need to do something on the server - reference the Access DB as a "Linked Server". Then you will be able to query it from within the SQL server, pulling out or stuffing in data however you like. With the structure you have outlined you might be able to return the information but depending on how the data is segmented and the relationships between the tables.
Bookmarks