Open Query + Linked Servers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Open Query + Linked Servers

Author

Topic

jmill130
Starting Member

26 Posts

Posted - 2009-03-16 : 22:09:08

I am wondering the best way to handle this situation:

--
I have 2 SQL servers which contain data i need to use both to feed another database on another sql server database. We will call the servers A1 and B1 and C1. B1 and A1 have share data and same primary keys on tables i am interested in.

A1 = the main sql server which i have db admin rights
B1 = another sql server which has a few data fields i need, i only have read access and access to a particular db to create store procs and views.
C1 = the sql server containing the data that eventually feeds a .NET application.

My initial thought is combine data in a sql view from A1 and B1. The main reason i thought to do this is because I dont want to move the data from B1 to A1. It will just take up space and i only need it for specific records based on the .NET app data entry criteria on C1. I was thinking I could bring all tables into a view from A1 and then use openquery to either create another view that has openquery reference to pull the additional fileds i need and use that view inside my final view. I really dont want to move the data from B1 to A1. It doesnt make sense to store it twice. The data on B1 is dynamic and changing all the time, ts easier just to grab it at the point in time its needed.

Currently i have processes in sql agent jobs that move other data elements from one B1 to A1 for cleaner data and better reporting but in this instance i would just like to "point to" the data on server B1.

Any suggestions, should i try a stored procedure, create a temp table dynamically and then delete after the data is moved to C1??

all suggestions are greatly appreciated.

TIA
-J

andre.wihardjo
Starting Member

4 Posts

Posted - 2009-03-17 : 07:04:31

Hi,

From your email, I get a situation like this:
a. You have three servers.
b. You use A1 and B1 data has to fulfill C1.
c. You have agent to move cleaner data’s from B1 to A1.

You can use a linked server and querying with OPENQUERY syntax for grab data from A1 and B1.
You can add new linked server from server C1 to A1 and B1. Then to get a data from A1 and B1, you can use JOIN syntax with OPENQUERY, and you execute in server C1. Therefore, you do not need to create temporary table.
If you want automatically get data, you should create a view or stored procedured and execute by your agent.

Execute from server C1.
Initial: C1 has 2 linked servers, LIKEDA1 (connect to server A1) and LINKEDB1 (connect to server B1)

USE database_x

SELECT C.*, A.COLUMN1, B.COLUMN4
FROM TABLE_IN_C1 C
LEFT JOIN OPENQUERY(LINKEDA1, ‘SELECT * FROM TABLE_IN_A1’) A
ON C.ID = A.ID
LEFT JOIN OPENQUERY(LINKEDB1, ‘SELECT * FROM TABLE_IN_B1’) B
ON A.SAME_ID = B.SAME_ID

--OR

SELECT C.*, AB.COLUMN1, AB.COLUMN4, AB.COLUMN12
FROM TABLE_IN_C1 C
LEFT JOIN
(
SELECT A.*, B.*
FROM OPENQUERY(LINKEDA1, ‘SELECT * FROM TABLE_IN_A1’) A
INNER JOIN OPENQUERY(LINKEDB1, ‘SELECT * FROM TABLE_IN_B1’) B
ON A.SAME_ID = B.SAME_ID
) AB

The JOIN syntax is depending on your situation and data.

Warm regards,

Andre Wihardjo

jmill130
Starting Member

26 Posts

Posted - 2009-03-17 : 08:21:43

Andre,

thanks for your solution. I programmed something similar to your solution and it will not let me save my view. I use same logic but saving on server A1, i dont even know where server C1 is and dont have access to it, i just know that is where the data is eventually going to for another group. I run the view and the data looks perfect.

Here is the error i am getting when i try to save on server A1:

"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE DB error tract [OLE/DB provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c]

any ideas why it runs fine and wont save?

andre.wihardjo
Starting Member

4 Posts

Posted - 2009-03-18 : 01:29:27

This problem may occur when the computer that initiates the distributed transaction.

Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for network transactions.

Windows Firewall is enabled on the computer.

By default, Windows Firewall blocks the Microsoft Distributed Transaction Coordinator (MSDTC) program.

Warm regards,

Andre Wihardjo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources