May 17, 2004 at 7:18 am
Hi,
I am working on a VB project that involves retrieval of data from two Sql Server databases. My app needs to search and update records that are contained on different servers. Currently, I am creating a temp table on one of the servers and importing data from the other through VB. This process is decreasing my performance and I am looking for a more efficient way. I am not familiar with distributed partitioned views and if that approach is the correct one.
Any suggestions would be helpful.
May 17, 2004 at 8:21 am
I've never looked into using partitioned views, so that may be the way to go.
However, to get this thread started, I suggest you have a look at Microsoft Message Queue Server. This will do exactly what you require and ensures that if a transaction is rolled back on one server, it is rolled back on the other.
This is a very important issue when working on databases that are different types or are on different servers.
E.g.
Step 1 on Database/Sever 1 - You have an orders table on a database that is used to accept an order from a supplier. This transaction is committed and written to disk. The supplier is billed etc.
Step 2 on Database/Server 2 - You have another table that holds the orders so the dispatchers can send out the order. This transaction failes and is rolled back and the goods are not shipped.
Some EAI tools are available on the market to perform a similar task but I know little apart from what I've heard second hand.
May 18, 2004 at 6:03 am
It sounds like you need to make one of the servers a linked server on the other server. Then you can select information from both servers in the same select statments by referring to data on the linked server using the four part name:
select * from table1 a
LEFT JOIN server2.databasename.owner.table2 b ON a.somefield = b.somefield
Peter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply