Tuesday, 22 March 2016

Creating Linked Servers in SQL Server 2012 to SQLServer 2000

 Linked server is Equivalent to Database link in Oracle database.
It is easy to create linked Server in SQL Server 2012 to any version till SQL Server 2008 using SQL Server management studio.
Challenges are encountered while creating linked server for a DB from SQL2012 to SQL2000

While creating a Linked Server, it throws following error reason being that SQL Server 2012 does not support Native Client 11 to connect to SQL 2000.

Resolution Steps:
  • Downloaded and installed the SQL Native Client 10.
  • Install the SQL Native client 10 and create Linked Server as below.
EXEC sp_addlinkedserver @server = N'TESTLNSVR',

  • This will create the  link server  but while accessing it could throw error as:

To rectify the above error , following steps should be followed:
Define ODBC data source using SQL native client 10 in ODBC tool in Windows server.

Once the ODBC Data source is completed ,perform the connectivity test and it should be successful.
1)     Now create the linked server using script.
EXEC sp_addlinkedserver @server = N'TESTLNK_SVR',
                        @provstr=N'DRIVER={SQL Server};SERVER=xx.xx.xx.xx;UID=test;PWD=Test123;',
Server:  TESTLNK_SVR is the link server name through which DB on target server is accessed.

Provider: MSDASQL is an OLEDB provider that connects to ODBC, instead of a database.

Provider string: SQL Server Is the OLE DB provider-specific connection string that identifies a unique data source.

Server in Provstr: Server name where the SQL Server 2000 instance is setup

Catalog: DBName is the database name that we are trying to access in target SQL Server instance.

Once the Linked Server is created, it can be accessed using below SQL.

Mohammed Fazulullah
Kovaion Consulting-PeopleSoft Practice


  1. The blog gave me idea to create linked server Thanks for sharing it
    Dot Net Training in Chennai

  2. Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
    Digital Marketing Company in India
    seo Company in India

  3. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care and we take your comments to heart.As always, we appreciate your confidence and trust in us.

    Home Cleaning Services Mumbai

  4. You post explain everything in detail and it was very interesting to read. Thank you. nata coaching centres in chennai

  5. Very Nice Blog I like the way you explained these things. I’ve been looking for ways to improve my website and overall rankings.I hope your future article will help me further.Take SEO Training in Chennai to mould yourself.

  6. This blog is having the general information. Got a creative work and this is very different one.We have to develop our creativity mind.This blog helps for this. Thank you for this blog. This is very interesting and useful.
    Pest Control in Chennai

  7. Really incredible and interesting article. Keep the good work. I really appreciate your effort on putting such thoughts in the post. Thanks for sharing with us. Latest php jobs in hyderabad

  8. Good one, very informative.. thanks for sharing your views and ideas.. it is very useful to me.. thanks once again

    Software Testing Training in chennai

  9. Really Good blog post.provided a helpful information.I hope that you will post more updates like this.
    Digital marketing company in Chennai

  10. I have attended the PHP course. Course content is excellent. I would recommend this course anyone wants to make career in PHP. Instructor describes you course in detail. All Presentations are very good. The Course Material is truly great,and good thing is that you will get Real time Project Explanation with Case Studies.Thank you ASTERHR IT SOLUTION PHP training in chennai

  11. Thanks for your marvelous posting! It is very useful and good. Come on. I want to introduce the best aso services for you, I try it and I feel it is so good to rank app to top in app store search results, have you ever heard it?

  12. This comment has been removed by the author.

  13. What a really awesome post this is. Truly, one of the best posts I've ever witnessed to see in my whole life. Wow, just keep it up. wordpress

  14. There are three popular approaches to server virtualization: the virtual machine model, the paravirtual machine model, and virtualization at the operating system (OS) layer.
    minecraft server hosting

  15. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad

  16. Very interesting blog Really excellent information and thank you for giving your valuable information

    Oracle Fusion HCM Online Training

  17. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Oracle Fusion SCM Online Training