Create a view joining 2 servers?

Hi everybody.

I was wondering if it is possible to create a view or a query using 2 different servers in the same manner as it is possible to join 2 databases(select A.*, B.* from db1.dbo.table1 A INNER JOIN db2.dbo.table2 B on A.ID = B.ID).

Is it possible or is it a complete waste of time?

Thanks, Christophe
LVL 1
FraggleRockAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mcallarseCommented:
It's possible using linked servers in MS SQL Server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FraggleRockAuthor Commented:
Thanks mcallarse.
I've looked into linked database and came across some explanations. I tried to apply it but I have a problem.
In the query analyser, I've typed the following (of course, I've changed the login details before posting it here!):

USE master
GO
EXEC sp_addlinkedserver
   'SERVER1',
   '',
   'MSDASQL',
   NULL,
   NULL,
   'DRIVER={SQL Server};SERVER=companyserver;UID=userid;PWD=password;'
GO


select * from SERVER1.mydatabase.dbo.rtcompanies

##################
companyserver is the name of an ODBC Connection setup on my machine.
mydatabase is the database I want to access.
rtcompanies is the table I'd like to display.

I have the following error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.]

I've checked the ODBC connection and it's fine.
I've tried the query:
select * from SERVER1.dbo.rtcompanies
but it says "Invalid object name 'SERVER1.dbo.rtcompanies'."

What am I doing wrong?
(I've increase the points to 300 available if you can help me sorting this out)

Thanks,
Christophe
0
FraggleRockAuthor Commented:
Well, I've found the right syntax:

USE master
GO
EXEC sp_addlinkedserver
   'SERVER1',
   '',
   'MSDASQL',
   'companyserver;UID=userid;PWD=password'
GO


select * from SERVER1.mydatabase.dbo.rtcompanies


Anyway, thank you mcallarse as you pointed me to the right topic to dig into. You can have those points.

Thanks a lot,
Christophe
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.