Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
With OPENROWSET, you can access remote data using OLE DB. OPENROWSET is a system function as you will see below.
With this method, we provide one-time access to data by establishing an AD HOC connection. If one-time access to the data is required, it can be used instead of the linked server. If you need to access the data frequently, you can choose the linked server. For more detailed information about Linked Server, you can write “Linked Server” to the Seach section of our website.
You can use OPENROWSET as a one-time alternative to the linked server as follows. You must type the instance name that you want to access instead of DestinationInstanceName.
1 2 3 4 5 6 7 | SELECT Employees.* FROM OPENROWSET( 'SQLNCLI', 'Server=DestinationInstanceName;Trusted_Connection=yes', 'SELECT * FROM AdventureWorks2014Yeni.dbo.MyEmployees' ) AS Employees |
If “Ad Hoc Distributed Queries” is turned off on the instance you execute the above query, you will get the error as follows.
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’
because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure.
For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.
To avoid this error, you must execute the following script on the instance where you execute the above query.
1 2 3 4 5 6 7 8 | EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE; GO |
If your target server is Access database, your query should be as follows.
1 2 3 4 5 6 7 | SELECT Employees.* FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'C:\MSSQL\Employees.mdb';'admin';' ', 'SELECT * FROM AdventureWorks2014Yeni.dbo.MyEmployees' ) AS Employees |
You can upload your files to the database using the OPENROWSET with the BULK option. You can find the details in the article “How To Import Images To SQL Server with TSQL Codes“.
You can join the data fetched from OPENROWSET with the data in your table and insert this result set to another table.
