August 6, 2007 at 10:11 pm
Does anyone have any ideas on how to join tables in different DB's without the possiblility of linked servers or replication? I am looking for something along the line of a Data Access Layer in .Net.
August 6, 2007 at 11:21 pm
Do you mean different DB's on the same server or different DB's on different servers?
If the databases are on the same server, no problem just qualify the databases and tables in your query (e.g. dbnamea.dbo.tablename, dbnameb.dbo.tablename...)
If the databases are on different SQL Servers a linked server is your best bet for one very simple reason - a lot of time and effort has been spent on making the query engine/optimizer handle remote queries efficiently. Given a linked SQL Server, SQL Server will attempt of optimize/coordinate the retrieval of the remote data if at all possible. For example, if you are selecting data from a remote table and it is possible to filter the data on the remote server before returning it to the local server, SQL Server will do so (e.g. if you're querying against a remote "customers" table and your criteria include a sargeable criteria like say customerid as part of the join or where clause criteria, SQL Server will retrieve only the necessary rows from the remote server).
Joe
August 7, 2007 at 2:16 am
if you are doing it in your DAL, you can use a datatable/dataset into which you populate data from two different connections provided you have the same column names and types. alternatively you can use a business object. below is the pseudocode
Customer Class
Customers Class : Collection of Customers
in Customers class create a proc that will connect to server 1 and get all the customers and add them to the collection
in the same proc point your connection to a different server and get all the customers add them to the collection
But then you will need to keep track of which db server the customer is stored on if you want to do any updates.
i would suggest connecting to a single instance of a server when doing any updates e.g. if a person wants to view and update EMEA data they should log into that db and if they want to view and edit China data they should log into the China db
August 8, 2007 at 2:19 am
Hi,
My first message in this site.....
Its not advisable to connect to two different server to do the operation. If you had access to two different server. whether you are trying to retrieve those datas and do manipulation in the business logic layer.... oops its little bit tedious job and if the datas are huge... then my web application will loose its patience to give a desired output. Instead you can have a linked server with one connection to db from dal...... is advisable. Because, my data filteration will be happening in the backend itself.
Thanks and Regards,
Venkatesan prabu. J
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
August 23, 2007 at 10:17 am
select crap from tbljunk inner join on uselessdata..tblmorejunk on stupidcolumn = dumbercolumn
August 23, 2007 at 10:27 am
I am glad you throughly read the question before posting because if you had, you would know that you can't execute the following statement across two different SQL Servers. The statement will only work if it is run on different databases on the same server.
August 24, 2007 at 8:17 am
... so the 4th qualifier is missing in one place ... bfd ... that's what QA and etsting are for ...
... besides that ... it is a really cool query ! ! !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 24, 2007 at 8:35 pm
Deltatuk -
I just have to ask, why can't you use linked servers or replication? Sounds to me like somebody has made a decision without all of the facts.
Joe
August 27, 2007 at 11:34 am
get a copy of MS Access, dump your data into the Access db via linked tables and run your queries against the access db
August 27, 2007 at 12:02 pm
Thanks for the reference to a non-enterprise DB (Access). We prefer something a little more robust. As for linked servers and replication. We can use replication but it seems like such a waste to replicate a entire db (50 gig) or even select tables to another server so that we do joins on them. As for linked servers the DBA's have put their foot down on not using that.
August 27, 2007 at 12:49 pm
what's wrong with linked servers?
the 50GB is only for the initial snapshot if you replicate all the tables. after that it's only the changes replicated. we replicate tables to a lot of subscribers into many different databases. you don't have to replicate the entire database.
this is what replication was made for. where is the waste?
you're just putting yourself into a corner refusing to use standard tools for accessing data across servers the easy way
August 28, 2007 at 7:01 am
Deltatuk,
How many rows (approximately) are there in the data sources? The reason I am asking is- do you think that moving the entire set of data from the source table(s) and then joining after moving the data- is that a feasible solution? Or do you need to worry about the optimization of the JOIN and the WHERE clauses within the SQL statement (e.g., in the case of large tables)?
There are some solutions to the above, but the appropriate answer will require more info, as per the question above.
In DB2 and federated database systems, the JOIN is built in natively in the database, server, and client tools. Similar options are not available in SQL server (yet). However, like I said before, there may be some architectural work-arounds.
In regards to the postings above- there are many cases where architectural, network, and security constraints that would prevent one from using linked servers, RPC calls, etc. The question asked is very valid, as the same features are available in other DBMS systems.
August 29, 2007 at 7:34 am
Another option might be to use the Merge Join transformation available in SSIS. It allows you to perform a join on disparate data sources when a normal SQL query join is not possible.
August 30, 2007 at 7:28 am
Is CLR Integration enabled? If it is you could write an assembly that accesses the remote data and use the assmbly to create a Table Valued function you can then join to.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply