Here's what I did to successfully set up a linked server to a directory on a SQL 2000 or SQL 2005 server.
/* Remove any previous references to the linked server */
USE [master]
GO
/****** Object: LinkedServer [LOCAL_PATH] Script Date: 01/06/2009 14:04:46 ******/
EXEC master.dbo.sp_dropserver @server=N'LOCAL_PATH', @droplogins='droplogins'
GO
EXEC sp_addlinkedserver 'LOCAL_PATH',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'E:\MSSQL\Reports', NULL, 'Text'
GO
EXEC sp_tables_ex
@table_server='LOCAL_PATH'
—
— If you get this error
—
—
–OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH" returned message "Unspecified error".
–Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
–Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH".
–Simply log onto the server as the same account that the sql services run as.
–Then under my computer properties changes the TEMP and TMP environment variables to something like
C:\temp
or
C:\TempSQL
–which should have security permissions changed to allow local users to have RWC permissions.
You might also lookup this on the Microsoft site.
PRB: Error 7399 When You Run a Linked Server Query That Uses the OLE DB Provider for Microsoft Jet