How to use OPENROWSET command in sql server

Dear all,

Sometimes we need to copy the data from access db to sql server. For transferring data we can use openrowset command.
For using openrowset you need the Ad Hoc Distributed Queries component turned on.
But, by default this component is being turned off for the security of server. But you can turn it on for using the openrowset command.

For turn it on you have to execute the following command in sql server……

sp_configure ‘show advanced options’,1
GO
RECONFIGURE
GO
sp_configure ‘Ad hoc distributed queries’,1
GO
RECONFIGURE

Now, you can use openrowset command. Such as…

SELECT CustomerID, CompanyName
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘H:\AS.mdb’;
‘admin’;”,Customers);
GO

And again you can turned off the Ad hoc Distributed queries by following command…

sp_configure ‘Ad hoc distributed queries’,0
GO
RECONFIGURE
GO
sp_configure ‘show advanced options’,0

GO
RECONFIGURE