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',
                        @srvproduct=N'MSSQL',
                        @provider=N'SQLNCLI10',
                        @provstr=N'PROVIDER=SQLOLEDB;SERVER=xx.xx.xx.xx'

  • 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',
                        @srvproduct=N'',
                        @provider=N'MSDASQL',
                        @provstr=N'DRIVER={SQL Server};SERVER=xx.xx.xx.xx;UID=test;PWD=Test123;',
                        @catalog=N'DBNAME'
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.
SELECT * FROM OPENQUERY(TESTLNK_SVR,’SELECT * FROM TABLE’)

Author:
Mohammed Fazulullah
Kovaion Consulting-PeopleSoft Practice

24 comments:

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

    ReplyDelete
  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

    ReplyDelete
  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

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

    ReplyDelete
  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.

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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

    ReplyDelete
  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

    ReplyDelete
  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?

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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

    Oracle Fusion HCM Online Training

    ReplyDelete