Import mdb file to temp table with 64bit SQL Serve

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Import mdb file to temp table with 64bit SQL Serve

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-05-10 : 21:19:05

I have been successfully using the following code to import a mdb access file into a SQL Server 2008 32 bit temp table. However after upgrading to SQL Server 2008 64 bit it seems Microsoft.Jet.OLEDB.4.0 is no longer compatible providing the following error.

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

I looked into installing AccessDatabaseEngine_x64.exe but this cannot be installed if 32bit Office is installed.

Any ideas?

Create table #t([LoginName] nvarchar(150), [Database] nvarchar(50))
Insert into #t

Exec(@sql)
---Print @sql
--Check mdb file exists
Exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
Exec sp_OAMethod @objFSys, 'FileExists', @i out, @VCGDBPath

If @i = 1

Begin

Select @sql1=''
Select @sql1=@sql1+ 'SELECT LoginName, ''VCG Database''
FROM OPENROWSET(
''Microsoft.Jet.OLEDB.4.0'',
''' + @VCGDBPath + ''';
''admin'';
'''',
' + @Table + ')
GROUP BY LoginName
'

Insert into #t
Exec(@sql1)
--Print @sql1
--''Microsoft.Jet.OLEDB.4.0'',
--''Microsoft.ACE.OLEDB.12.0'',
End

Else
Print 'Database does not Exist'

Exec sp_OADestroy @objFSys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:48:46

see

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c250a81f-5d25-4ba8-9639-3464c524fb35/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-05-13 : 19:51:44

I don't think this link provides me with an answer? It seems to use the access/sql export/import wizards? I have a mdb access file and need to import it into sql server 2008 express R2 64 bit using transact sql/stored procedure.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-05-13 : 22:02:38

A solution;

1. Replace ''Microsoft.Jet.OLEDB.4.0'' with ''Microsoft.ACE.OLEDB.12.0'' in script above.

2. Install AccessDatabaseEngine.exe for SQL Server 2008 32 bit or AccessDatabaseEngine_x64.exe for SQL Server 2008 64 bit.
** Note AccessDatabaseEngine_x64.exe will not install if any 32 bit Office products are installed.

3. AllowInProcess for Microsoft.ACE.OLEDB.12.0.

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

4. My example script;

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[IR_Users_RP] Script Date: 05/14/2012 11:22:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[IR_Users_RP]

@DBName nvarchar (max),
@VCGDBPath nvarchar (255)

AS

--Declare @VCGDBPath nvarchar (255)
Declare @Table nvarchar (200)
Declare @sql1 nvarchar(max)
Declare @objFSys int
Declare @i int
Declare @File varchar(1000)
Declare @sql nvarchar(max)

--SET @VCGDBPath = (SELECT 'C:\Reports\Data\CMS_User _with_groups.MDB')
--Print @VCGDBPath

SET @Table = (SELECT 'Results')
--Print @Table

SET NOCOUNT ON

select @sql=''
select @sql=@sql+ 'SELECT UserDetails.LoginName, ''VCP Database''
FROM [' + @DBName + '].dbo.UserDetails
WHERE UserDetails.IsDeleted = ''False''
AND UserDetails.LoginName != ''VideoCentralGold''
AND UserDetails.LoginName != ''VideoWall''
AND UserDetails.LoginName != ''VCPClient''
'

Create table #t([LoginName] nvarchar(150), [Database] nvarchar(50))
Insert into #t

Exec(@sql)
---Print @sql

Exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
Exec sp_OAMethod @objFSys, 'FileExists', @i out, @VCGDBPath

If @i = 1

Begin

Select @sql1=''
Select @sql1=@sql1+ 'SELECT LoginName, ''VCG Database''
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''' + @VCGDBPath + ''';
''admin'';
'''',
' + @Table + ')
GROUP BY LoginName
'

Insert into #t
Exec(@sql1)
--Print @sql1

End

Else
Print 'Database does not Exist'

Exec sp_OADestroy @objFSys

Select DISTINCT [LoginName], [Database]
From #t
Order By [LoginName]

Drop Table #t

SET NOCOUNT OFF

RETURN

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-05-13 : 23:41:47

Actually if you run AccessDatabaseEngine_X64.exe /passive from a cmd line it installs and works ok.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources