SP accessing MS Access Database w/o DTS

[BradF]

I need to create a stored procedure that can read in a table from an access database on an FTP server. Is this possible?

We are not allowed to use DTS package.

 

[SQLDenis]

Yes, use OPENROWSET
below are 2 examples

C. Use the Microsoft OLE DB Provider for Jet
This example accesses the orders table in the Microsoft Access Northwind database through the Microsoft OLE DB Provider for Jet.


USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS a
GO

D. Use OPENROWSET and another table in an INNER JOIN
This example selects all data from the customers table from the local SQL Server Northwind database and from the orders table from the Access Northwind database stored on the same computer.


USE pubs
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[BradF]

I'm familiar with OPENROWSET to access other database types. I use it frequently for Oracle pulls. However, this access database isn't on my SQL Server machine, it's going to be on an remote FTP server.

 

[SQLDenis]

Can you map a drive to that machine?
Or use FTP to pull the file in run your query and then delete it

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[BradF]

That's going to take a lot of red tape... It'd be nice if I could fine a stored procedure that could either pull the file in to a temporary directory on the server before running the openrowset or run the openrowset across the ftp connection.