Data migration from Access to SQL server 2005

Hi,

I am trying to migrate the data from the Access database to Microsoft SQL server on my machine using some scripts. I have enabled the OPENROWSET and OPENDATASOURCE support through the surface area configuration. The script that accesses the excel file works fine but when i run the script that trys to open the .mdb file it gives me following error.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.".

Msg 7303, Level 16, State 1, Line 35

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Any help would be greatly appreciated. Thanks.

-padu

Make sure that the startup account for sqlserver is not a service account (i.e. localsystem/service/etc.). Just use a domain acct.|||

Hi oj,

How do you check the startup account is not a service account? I am pretty new to SQL server.

Thanks.

-padu

|||You can run SQL Server Configuration Manager. It allows you to change service account there (if desired).