Firstly, let me start by explaining what it is I'm trying to achieve:I need to import data from one source to another. The source being MS Access and the destination source being SQL Server 2005. The columns that I need to import from the related tables gets generated from the application's business logic layer generating xml schema files which is the format file that I pass to the OPENROWSET - BULK function when importing from lets say MS Excel.The above strategy works nicely when importing from lets say MS Excel but not with MS Access. 1. I'm not being able do to import data from MS Access using OPENROWSET... I get the following error: OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".2. I have to use format files to tell what columns I have to import. After a bit of research I established that I needed to create a linked server using the sp_addlinkedserver stored procedures etc.This now works fine using the OPENQUERY function executing my query like this: EXEC sp_addlinkedserver 'AccessSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0','C:\ImportData\1.mdb', NULL
EXEC sp_addlinkedsrvlogin 'AccessSource', FALSE, NULL, NULL, ''
select * from openquery(AccessSource, 'Select * from tGrain')
Now to get to my question... Is it possible to pass my format file to an OPENQUERY - BULK function, or how can I get the linked server to work with an OPENROWSET - BULK function as I do have to use the XML schema format file. If anybody has ever dealt with a similar issue please be so kind to share your suggestions.RegardsRyno
Importing data from MS Access & using format files
Posted - 2007-11-12 : 00:41:10
