How to link/query SQL 2000 data from SQL Express 2008 R2

I have a production server with a legacy application which requires Microsoft SQL 2000.  I have written several asp.net based reports that query this data (read only).  Some of the functionality that I would like to add to our reporting would benefit from features of a more modern version of SQL (i.e. Multiple Active Result Sets).  I have setup a second server with SQL Express 2008 R2 and was considering replicating a copy of the data from the 2000 server to the 2008 server and updating it regularly.  Any suggestions on a better solution?  I cannot alter the 2000 server.  Is it possible to link the tables in 2008 so I can access the 2000 data and utilize the added features of 2008?
LVL 1
ShayB_CGSAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:

Try linking the server to your SQL 2008R2 Express server

http://msdn.microsoft.com/en-us/library/ff772782.aspx#SSMSProcedure
0
 
ShayB_CGSAuthor Commented:
Thanks for the link.  That was the instruction I was having trouble finding.  After configuring the Linked Server I had an additional error and had to also follow this:

http://support.microsoft.com/kb/906954

Thanks again
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.