CODEWITHSUNDEEP
sql-serverstored-proceduresms-access-2003
Khan
Khan

Reputation: 5224

Export data from SQL Server to MS Access

I need to write a stored procedure which reads data from SQL Server and writes it into a MS Access 2003 table. I tried the following but it is not working:

Insert into OpenRowSet
('Microsoft.Jet.OLEDB.4.0',      
'D:\BEMF_LOAN_RECOVERY.mdb';  
''; -- User ID
'', -- Password
'Select * from access_tbl_name') 
    Select * 
    from sql_server_tbl_name

Error mesage

Msg 7308, Level 16, State 1, Line 2
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.

Upvotes: 1

Views: 798

Answers (1)

momar
momar

Reputation: 364

Are you sure that 'Ad Hoc Distributed Queries' is enabled on your SQL Server?

By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access.

https://msdn.microsoft.com/en-us/library/ms187569.aspx

Upvotes: 1

Related Questions