November 30, 2007 at 3:14 pm
Hello Experts,
Background information:
I work for a company that sells hardware and software to clients. Often, these clients are large companies, with several branch locations, where each location has a separate copy of our software. The software itself monitors our hardware and records statistics about the hardware in an Access 97 database. These statistics are continuously generated 24 hours a day. You may be wondering, why did we use Access 97? We've been in business a long time, and when we first started writing our software, Access 97 was the state of the art, and we've had no compelling reason to upgrade to a newer version of Access (or any other database package).
So, some of these larger companies have been asking for some kind of solution to allow them to see all the statistics from all the branches in a central application. Therefore, we are developing a web application using SQL Server 2005 to store the data. We've deployed updates to our software that to enable branches to upload their latest Access 97 changes via FTP to the web site nightly. The FTP code is flexible enough to allow each location to upload either the latest changes or upload the entire table. The name of the uploaded file is based on whether the uploaded data is "partial" or "complete" (ie, Data_Partial.mdb or Data_Complete.mdb).
Question:
The task I'm stuck on is how to import this data into SQL Server 2005. I've been trying to write a query that will import the Access 97 data into SQL Server 2005, but I keep getting errors. 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.
Here is the script to import the Complete data from the Stats1 table:
USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Drop existing table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))
DROP TABLE [dbo].[Table1]
-- This script creates the new table and imports the Access table all in 1 step:
SELECT * INTO [dbo].[Table1]
FROM OPENDATASOURCE(
'Microsoft.JET.OLEDB.4.0',
'Data Source="C:\MyApps\Data_Complete.mdb";User ID=Admin;Password='
)...Stats1
And here is the error:
OLE DB provider "Microsoft.JET.OLEDB.4.0" for linked server "(null)" 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 7
Cannot initialize the data source object of OLE DB provider "Microsoft.JET.OLEDB.4.0" for linked server "(null)".
If I substitute Microsoft.JET.OLEDB.3.51 in the query, here is the error I get:
OLE DB provider "Microsoft.JET.OLEDB.3.51" for linked server "(null)" returned message "Syntax error (missing operator) in query expression '`Tbl1005`.`Field1` `Col1019`'.".
Msg 7321, Level 16, State 2, Line 7
An error occurred while preparing the query "SELECT `Tbl1005`.`Field1` `Col1019`,`Tbl1005`.`Field2` `Col1020`,`Tbl1005`.`Field3` `Col1021`,`Tbl1005`.`Field4` `Col1022`,`Tbl1005`.`Field5` `Col1023`,`Tbl1005`.`Field6` `Col1024`,`Tbl1005`.`Field7` `Col1025`,`Tbl1005`.`Field8` `Col1026`,`Tbl1005`.`Field9` `Col1027`,`Tbl1005`.`Field10` `Col1028`,`Tbl1005`.`Field11` `Col1029` FROM `Stats1` `Tbl1005`" for execution against OLE DB provider "Microsoft.JET.OLEDB.3.51" for linked server "(null)".
Here is the script to append the Partial data:
USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- This script appends the data from an Access table:
INSERT INTO [dbo].[Table1]
SELECT *
FROM OPENDATASOURCE(
'Microsoft.JET.OLEDB.4.0',
'Data Source="C:\MyApps\Data_Partial.mdb";User ID=Admin;Password='
)...Stats1
It also gives the same errors, Msg 7303 with Microsoft.JET.OLEDB.4.0 and Msg 7321 with Microsoft.JET.OLEDB.3.51.
Anyone have any clues about how I should proceed?
November 30, 2007 at 3:26 pm
Let's get the obvious question out of the way first: Have you verified that the MDB files can be opened by Access 97 after being FTP'ed ? FTP transfer mode is confirmed to be Binary and not Ascii ?
November 30, 2007 at 3:40 pm
Good question - I've seen FTP mangle some files in the past.
I just checked it out - yeah, I can get into the DB in Access 97 and open the table. Just to play around a little, I then compacted the database, and reran the query - same error, MSG 7321.
November 30, 2007 at 3:45 pm
Next up: File and folder security/permissions.
Calling OpenDataSource() may require that the service account for the SQL instance needs to have access to the folder containing the MDB files. Make the folder accessible to everyone, and if that solves the issue, trim back the permissions again to just the minimum needed to allow OpenDataSource() to function.
November 30, 2007 at 3:50 pm
Tyshdude (11/30/2007)
Good question - I've seen FTP mangle some files in the past.I just checked it out - yeah, I can get into the DB in Access 97 and open the table. Just to play around a little, I then compacted the database, and reran the query - same error, MSG 7321.
Just for giggles - after you opened the table, did you have it navigate to the end? (scroll to the last record?) If there IS something funky in it, you may find you get that error when you do that.
Access doesn't access all of the data at once in a local table. It will do that once you force it to scan all of the data.
----------------------------------------------------------------------------------
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?
November 30, 2007 at 3:51 pm
Assuming you CAN open it and scan all of the way to then end - how about just exporting the data from access? to CSV for example since it seems to be very good at 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?
November 30, 2007 at 4:11 pm
PW:
Well, the owners are listed as me and Administrators. I changed the options for me to "Allow" for all permissions, and then tried the import again. Still no luck...
Matt Miller:
Yes, I was able to scroll down to the end of the table.
I suppose I could export the file as a CSV file and then import that file. It seems like kind of a kludge fix, and I'd rather avoid it if possible, but if that turns out to be my only recourse, it's certainly an option.
November 30, 2007 at 4:17 pm
>>I changed the options for me to "Allow" for all permissions
What about the account that the SQL instance is running under ?
When you run this query, the OpenDataSource() is not executing as "you". The SQL is going off to the SQL server and running under some other credentials.
November 30, 2007 at 4:31 pm
Ah... I need to make a clarification. I have SQL Server 2005 installed locally for testing purposes. Once I get all the pieces figured out for my eventual stored procedure(s) and scheduled job(s), I'll post them on the actual web server, and work out any bugs that occur at that time.
Does this business with permissions still apply with a local copy of SQL Server 2005?
November 30, 2007 at 4:37 pm
>>Does this business with permissions still apply with a local copy of SQL Server 2005?
Yep. When you installed SQL Server locally, you were prompted for a service account for it to run under. Certain T-SQL commands will execute as that service account, not as "you".
November 30, 2007 at 6:50 pm
Tyshdude (11/30/2007)
.. Therefore, we are developing a web application ...
If you are using visual studio 2005, try integration services.
December 3, 2007 at 2:52 pm
PW,
Calling OpenDataSource() may require that the service account for the SQL instance needs to have access to the folder containing the MDB files. Make the folder accessible to everyone, and if that solves the issue, trim back the permissions again to just the minimum needed to allow OpenDataSource() to function.
How would this be done? Is it done in Management Studio or in Configuration Manager?
Yep. When you installed SQL Server locally, you were prompted for a service account for it to run under. Certain T-SQL commands will execute as that service account, not as "you".
When installing SQL Server, I selected "Use the built-in System account".
December 3, 2007 at 3:06 pm
In your original posting, this was the path:
'Data Source="C:\MyApps\Data_Complete.mdb
So, you'd want to ensure that your SQL Server service account has full read/write access to the folder "C:\MyApps".
You would do that via Explorer, or via a command like tool like CACLS that modifies access control lists.
For the purposes of confirming that this is a permissions problem, I'd suggest making "C:\MyApps" readable and writable by "everyone".
December 3, 2007 at 4:57 pm
Well, that's what I did before (made the C:\MyApps folder readable and writeable by everyone in Explorer), but just to double check, I did it again. Still getting error 7303.
December 4, 2007 at 5:21 am
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.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply