Retreive Data from Remote SQL Server

Hi All,

We have a local SQL 2008 server and a remote SQL 2005 server at the other end of a VPN tunnel. We want to programatically retrieve data from the remote server. What is the best approach? Any experience is appreciated,
PrimediusAsked:
Who is Participating?
 
bitrefCommented:
You may create a linked server on the local SQL 2008 server to reference the remote SQL 2005.
0
 
brutaldevCommented:
While connected to the 2008 server, create a linked server to the 2005 machine:
 
USE master;
GO
EXEC sp_addlinkedserver 
   N'My2005Server',
   N'SQL Server';
GO

Open in new window

More info: http://msdn.microsoft.com/en-us/library/ms190479.aspx

Secondly you will need to add the login information you want to use to access the server from the remote 2008 machine (more than likely necessary):
 
EXEC sp_addlinkedsrvlogin 'My2005Server', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u'

Open in new window

More info: http://msdn.microsoft.com/en-us/library/ms189811.aspx

Lastly, when you access your 2005 server you can use syntax like:
 
SELECT * FROM My2005Server.DatabaseToUse.dbo.TableToUse;

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
If in network then not an issue. but on different network then need to be on live ip and use it using link server
0
 
TempDBACommented:
you can try openrowset too but linked server is better option.
0
 
PrimediusAuthor Commented:
Thank you, This is what we did.
0
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.

All Courses

From novice to tech pro — start learning today.