November 11, 2007 at 11:00 pm
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.".
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.
If anybody has ever dealt with a similar issue please be so kind to share your suggestions.
Regards
Ryno
November 12, 2007 at 4:25 am
I haven't dealt with this kind of situation, but if you can access data with openquery, i think there should not be much problem.
Why don't you try to store the whole xml file into a temporary table with xmldatatype column having max as size. Once it is copied, parse the xml in sql server and see if you get your desired result.
It is just a suggestion and i dont mind in trying.
Anam
November 12, 2007 at 9:39 pm
Microsoft has a tool "SSMA Access" for the migration.
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
http://cfpayne.wordpress.com/2006/07/23/how-to-import-microsoft-access-into-sql-server-2005/
November 13, 2007 at 6:22 am
ryno,
Keep in mind that the SQL that is executed inside the OPENQUERY is parsed by the 'Microsoft.Jet.OLEDB.4.0' driver which is different than T-SQL. Meaning that you must use MS Access SQL syntax when pulling data into SQL Server.
It may be worth your time to look into using SSIS package(s) as an alternative as well.
Regards,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply