The following is the steps to run a SQL statement against MS Access database using SSMS (SQL Server Management Studio)
1) Enable "Ad Hoc Distributed Queries
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
2)Use" OpenDataSource"
Select *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=\\ServerName\FolderName\DBFileName.mdb')...[TableName] AS T
Unfortunately, finally I got: :'(
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 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
http://connect.microsoft.com/SQLServer/feedback/details/284113/import-excel-data-with-openrowset-on-production-server
Looks like the file should not be open by someone else!!!
1) Enable "Ad Hoc Distributed Queries
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
2)Use" OpenDataSource"
Select *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=\\ServerName\FolderName\DBFileName.mdb')...[TableName] AS T
Unfortunately, finally I got: :'(
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 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
http://connect.microsoft.com/SQLServer/feedback/details/284113/import-excel-data-with-openrowset-on-production-server
Looks like the file should not be open by someone else!!!
No comments:
Post a Comment