Avatar of MohitPandit
MohitPandit
Flag for India asked on

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

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
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
MohitPandit

8/22/2022 - Mon
funwithdotnet

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.
ASKER CERTIFIED SOLUTION
Daniel Jones

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Vitor Montalvão

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).
MohitPandit

ASKER
@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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Vitor Montalvão

@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.
Daniel Jones

@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
Vitor Montalvão

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
MohitPandit

ASKER
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
Vitor Montalvão

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

ASKER
@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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Vitor Montalvão

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

ASKER
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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
MohitPandit

ASKER
Okay, thanks. I'll access of SQL 2008 R2. Once, It's done then I'll close this thread.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
MohitPandit

ASKER
I got access SQL 2008 R2 and it was straight to create Linked server.
MohitPandit

ASKER
Thanks