December 4, 2007 at 7:09 am
WILLIAM MITCHELL (12/4/2007)
Tyshdude, I think you stated the answer in your first post:"Thinking it might be a problem with Access 97, I manually converted some test data up to Access 2000, and then got my queries to work, so I know the queries themselves work. They just don't seem to like the Access 97 data."
I remember a thread about this a few months ago, at the time the conclusion was that SQL 2005 does not work with the Access 97 format.
If that is indeed the case, perhaps you could use MSDE in-between Access 97 and SQL 2005.
I created a linked server to Access97 last night (based on this thread), so I know it works.
Actually Tyshdude - have you tried that approach? (create the linked server and then use OPENQUERY against it)?
Also - you might care to look at the access solutions to this kind of thing. 97 had an Upsizing wizard which would move data into SQL Server. There's a newer version of that, called the SQL Server Migration Assistant, that allows for moving all sorts of fun things into SQL 2005. It's specifically stated as working with Access97.
SSMA is on Microsoft's download site:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 4, 2007 at 8:36 am
Matt, can you supply some details how you accomplished that? Here's what I'm using:
sp_addlinkedserver
@server = 'Test97',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = '',
@datasrc = 'C:\BILL\Test97.mdb'
GO
sp_addlinkedsrvlogin
@rmtsrvname='Test97',
@useself='False',
@rmtuser='Admin',
@rmtpassword=''
GO
I get the "Command(s) completed successfully." and it does create the linked server, but I cannot access any of the tables or views - I get "Failed to retrieve data for this request" with an error code 7303.
Bill
December 4, 2007 at 8:56 am
William,
That's nearly the same script I tried, and I'm getting errors too.
EXEC sp_addlinkedserver
@server = N'Data97',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'C:\MyApps\Data_Complete.mdb'
GO
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'Data97',
@useself = N'TRUE',
@locallogin = NULL,
@rmtuser = N'Admin',
@rmtpassword = NULL
GO
SELECT *
FROM OPENQUERY(Data97, 'SELECT * FROM Stats1')
It gives me the following errors:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Data97" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Data97" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Data97".
December 4, 2007 at 9:06 am
Interestingly enough - I set up the linked server to use NO security context when it queries the remote server, so I don't set up the server login at all. Because the Access DB is using the default workgroup file with no security modifications, the file opens right up....
I can't say I'm familiar with how to set that programmatically.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 4, 2007 at 9:07 am
And if I convert the db to Access 2000, the same script does work - just like you described.
Matt, do you actually have Access 97 installed? I don't have a 3.51 provider listed, maybe that's the missing link?
Bill
December 4, 2007 at 9:10 am
WILLIAM MITCHELL (12/4/2007)
And if I convert the db to Access 2000, the same script does work - just like you described.Matt, do you actually have Access 97 installed? I don't have a 3.51 provider listed, maybe that's the missing link?
Bill
Not anymore - but this machine used to have it installed, so I imagine it STILL has it. I will have to check once I get home tonight.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 4, 2007 at 11:00 pm
No evidence of any 3.5 drivers on the machine.... Not sure what to tell you about that.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 5, 2007 at 6:41 am
Well sir, I am stumped. Are you using SQL 2005, and are you absolutely certain that the Access file is 97 format?
On my old SQL 2000 server, which does not have any version of Access installed, I can run this script:
sp_addlinkedserver
@server = 'Test97',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = '',
@datasrc = 'C:\BILL\Test97.mdb'
GO
sp_addlinkedsrvlogin
@rmtsrvname='Test97',
@useself='False',
@rmtuser='Admin',
@rmtpassword=''
GO
which creates the linked server, and then use either
select * from test97...client
or
select * from openquery(test97,'select * from client')
and everything works as expected.
On my SQL 2005 server, it fails with the Access 97 file, but it works with the 2000 file (except that the 1st select works, the openquery doesn't). Can you script out your linked server & post the script?
Thanks,
Bill
December 6, 2007 at 5:59 am
I did it several ways just to make sure it IS a v.97 file:
- dusted off some of my old FLOPPIES (yeah - I had to remember what they looked like too)
- puleed that file that Access 2K3 tells me is 97
- REVERTED a access 2000 file to 97.
and....they all seem to work. Must be something in the contents....
here's the script for one of them - it put a linked server login with nulls all around into the script.
/****** Object: LinkedServer [DB1] Script Date: 12/06/2007 07:54:13 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DB1', @srvproduct=N'sv1', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'c:\temp\db1.mdb'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB1',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DB1', @optname=N'use remote collation', @optvalue=N'true'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 6:53 am
I updated my script to match yours, and still no joy. Can you actually open a table e.g. using
SELECT * FROM DB1...SomeTable
remember the original problem is that it LOOKS like it creates the linked server but we can't get to the data, it gives me this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test97" returned message "Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test97".
Also, what versions of SQL and OS are you running, here's my setup using select @@version:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
December 6, 2007 at 7:37 am
Same version as yours, and the tables open up...
I'm thinking it's file specific... maybe something in the files it just doesn't like... ugh.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 8:41 am
OK, so I created a new blank database with 2003, then converted to 97 format. Ran the script. When I test the connection to the linked server, it fails. Must be something in my SQL Server configuration, but not sure where to look.
EDIT: What are "FLOPPIES" :laugh:
December 6, 2007 at 6:10 pm
Update: I took the original 97 file and copied it to my Windows Server 2003 with SQL 2005 Standard, and guess what - it worked. So there is definitely some config thing that has to happen in order to connect from SQL 2005 to Access 97.
December 7, 2007 at 8:30 am
William,
You've become my avatar in this thread - I thank you for all your efforts.
So, you were able to get a different installation of SQL Server 2005 to read an Access 97 file. Was this using "both" methods - defining the linked server first AND with the Opendatasource method?
December 7, 2007 at 9:25 am
Yes, the other installation did work, I could use either of these to read the data:
select * from test97...client
select * from openquery(test97,'select * from client')
... I did not try using opendatasource, I suppose that would be one more thing to try. But I do know that you have to use the Surface Area Configuration Tool to enable opendatasource and openrowset, those features are disabled by default in 2005.
I posted a question at the MS newgroups yesterday. maybe one of the gurus over ther can explain the server config issue.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply