Connect to a SQL 2000 db file

Question

Connect to a SQL 2000 db file

mike 61998

SSC Enthusiast

Points: 119
More actions
September 10, 2012 at 10:14 am

#281417

Hello everyone. I have a .mdb file from an instance of sql 2000 that I copied from the server and I need to connect to it from my office. I am currently running sql server 2012 and am getting many different types of errors. I've tried linked server, attaching with no success. Any ideas?
THanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can't attach a SQL 2000 DB to SQL 2012, you need to first upgrade the DB to SQL 2005 or SQL 2008 (by attaching it to one of those versions), then you can detach it and attach it to SQL 2012

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mike 61998 SSC Enthusiast Points: 119 More actions · Answer 2

Is there any other way that I can connect to it? I just need to connect and run queries against the DB for sending hourly reports.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No. SQL Server is not a file-based database like MS Access. To use a DB, the DB must be attached to a running SQL instance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abarona SSC Veteran Points: 203 More actions · Answer 4

You can created the Linked server using OLEDB Provider for ODBC Connection (You need to attach the file to an SQL 2000 Instance)

Data Source=SERVER;Initial Catalog=Data;User Id=YOURUSR;Password=yourpassword;

& you will be able to query data.

Select top 100 * from Server.Catalog.dbo.Tablename

Regards.

mike 61998 SSC Enthusiast Points: 119 More actions · Answer 5

I don't have the USERID/Password to Computer A. I use windows authentication to connect.

abarona SSC Veteran Points: 203 More actions · Answer 6

you can use

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

for the ODBC

mike 61998 SSC Enthusiast Points: 119 More actions · Answer 7

What about "Provider String" and "Location"?

Also, did you mean "Microsoft OLE DB Provider for ODBC Drivers"?

abarona SSC Veteran Points: 203 More actions · Answer 8

Provider:

Microsoft OLE DB Provider for ODBC Drivers

Product Name: (Not Required)

DataSource:

YourDesireDataSourceNAME

Provider String:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Location: (Not Required)

Catalog:

myDatabase

mike 61998 SSC Enthusiast Points: 119 More actions · Answer 9

Still getting errors:

Invalid Authorization Specification.

I noticed a "Security" tab. I've tried several options there with no success either.

I appreciate your help on this.