SQL 2000 Linked Server in SQL 2017. All drivers fail to connect.

Romolo
Romolo used Ask the Experts™
on
Linked Server to 2000 SP4 from SQL 2017.

Will be upgrading this client within next 6 months.

I need to run some automation from the 2017 and access tables read/write on the SQL box.

Please advise how I can do this as every driver says does not support 2000 and I cannot get anything to connect at present.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
I can't setup such a test environment, but I guess to simple (and only) solution for keeping the linked server is to use the oledb/odbc driver instead of creating a direct SQL Server link.
RomoloIT Pro, Projects, Mentor, Trainer

Author

Commented:
Tried this ste5an using the Linked Server and using ODBC Data Sources.
All fail.

Did you mean something else?

R
ste5anSenior Developer

Commented:
This sounds like it maybe something different.

Create a new text file on the desktop, rename it and change it's extension to .udl.
Then double-click it.
Change the provider to OLE DB for ODBC.
Setup the connection to your SQL Server 2000.
Test the connection.

When the test step fails, then it is an issue with the network, firewall or the SQL Server 2000.
RomoloIT Pro, Projects, Mentor, Trainer

Author

Commented:
I get a failure straight away.

"Test connection failed because of an error in initializing provider. Unspecified error"

2000 Server is fine. Can RDP. It is providing data access to applications etc... through MSAccess on the Windows 10 clients... not a problem.
It is trying to get it to work on Windows 2016 and SQL Server 2017. I only need it as a Linked Server so I can undertake some work in the interim.

Any other ideas?

R
RomoloIT Pro, Projects, Mentor, Trainer

Author

Commented:
OK.. finally convinced to IT to bounce the SQL 2000 server. Can now get a Data Source in ODBC working.. Hooray.
Now need to configure in SQL Linked Server.
IT Pro, Projects, Mentor, Trainer
Commented:
Created SQL Login on Source 2000 box for the Linked Server

Then on the SQL 2017 Target box:
I created a 64bit ODBC DSN called MyDSN.
Then created a LinkedServer using the SQL ODBC Driver and specified the DSN and then using the SQL Cred under security as normal.

Worked fine.

I believe it was an issue on the source.. Bounce sorted it.

Thanks

R

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial