Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2000: == Linked Server, With higher version ==

Posted on 2016-08-18
16
Medium Priority
?
481 Views
Last Modified: 2016-08-26
Hello Folks,

I've SQL Server 2000, I want to have linked server (i.e. SQL Server 2008) on SQL Server 2000.

Does SQL Server 2000 allow to make linked server with higher version?

Best Regards,
Mohit Pandit
0
Comment
Question by:MohitPandit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41761953
I don't know the answer to your question, however, you probably shouldn't be using SQL Server 2000. SQL Server 2008 supports SQL Server 2000 databases and the linking you want.
0
 
LVL 4

Accepted Solution

by:
Daniel Jones earned 1400 total points
ID: 41762028
Yes, SQL Server 2000 allow to make linked server with higher version.

For creating a linked server to SQL Server 2008 R2

1. Right-click Linked Servers under the server's Security node >  choose New Linked Server > name the link "SQL2008R2"
2. Choose  the "SQL Server" as the Server Type, enter servername\instance.
3. Choose "Be made using this security context", and entered in my new SQL Standard Account that exists on the target server only.

Note: Be sure new SQL Standard user has access to the database and table.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41762137
Linked servers doesn't care about versions but it requires the client to be installed in the server so it knows how to communicate with the linked server.
With that said you'll need to install SQL Server 2008 native client for SQL Server 2000 be able to create the appropriate linked server. You can download if from MSSQL 2008R2 Feature Pack (In Install Instructions search for sqlncli.msi).
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 5

Author Comment

by:MohitPandit
ID: 41762215
@Vitor, I can't install native client on production server.

@Daniel, I followed your steps. It looks configured but when I clicked on "tables" then it pops up message "Error 17: SQL Server does not exist or access denied"
Should I use "Other data source" as Server Type with different provider?

Best Regards
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41762223
@Vitor, I can't install native client on production server.
Then you should get someone that can otherwise you won't be able to create the necessary Linked Server.
0
 
LVL 4

Expert Comment

by:Daniel Jones
ID: 41762231
@Daniel, I followed your steps. It looks configured but when I clicked on "tables" then it pops up message "Error 17: SQL Server does not exist or access denied"
take a look at this link, this will show you the potential causes of the "SQL Server does not exist or access denied" error message: https://support.microsoft.com/en-us/kb/328306
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41762236
The error occurs because your SQL Server 2000 instance doesn't know how to contact a SQL Server 2008 instance. That's why you need MSSQL 2008 SNAC.
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 41762328
Hi Vitor - Thanks for your comment.
But I have other instance of SQL 2008 R2 and I tried to connect linked server of SQL 2014.
It works.

I believe that similar 2000 & 2008 R2 should connect through linked server. Do you have other thoughts?

Best Regards
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41762335
But I have other instance of SQL 2008 R2 and I tried to connect linked server of SQL 2014.
 It works.
SQL Server 2014 is higher that 2008R2 so it should knows how to connect to a previous version. The opposite might not work.

NOTE: In year 2000 Microsoft's developers wouldn't know that it would exist a SQL Server 2008 version in the future and which protocols it will use, right?
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 41764020
@Vitor, I got access for SQL 2012 on same network where SQL 2000.

I tried to create linked server for SQL 2012 from SQL 2000. I got access all tables & data.

I believe that similar will be happen with SQL 2008 R2.

Thoughts?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41764906
Yes, I'm expecting that a newer version of SQL Server will be able to connect to older versions without problem.
What for you need the Linked Server? i.e. it's something that can be done in the SQL Server 2008R2 side?
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 41765502
Actually, earlier you were strongly recommended for native client download.
This is the reason, I wanted to inform you that I was able to successful linked server between SQL 2000 vs. 2012.

Best Regards
0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 600 total points
ID: 41766432
Oh, I misread your comment then. So you're always able to install the native client on production server. :)

So, now that you have the Linked Server created (your main question issue) what do you need more?
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 41768033
Okay, thanks. I'll access of SQL 2008 R2. Once, It's done then I'll close this thread.
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 41772568
I got access SQL 2008 R2 and it was straight to create Linked server.
0
 
LVL 5

Author Closing Comment

by:MohitPandit
ID: 41772569
Thanks
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question