OpenRowSet and OpenQuery in SQL Server 2005/2008

Said both of the techniques (OpenRowSetand OpenQuery) are almost same and may confuse many developers. I have been asked many time personally by few of my team member in recent years as well as I have seen the same question in few forums like Experts-Exchange and Forums.Asp.Net.
Basically both commands requires to access remote data from other data source like other SQL Server or may be Oracle or may be Access or Excel or anything else. Basically both of these commands requires connection information of source database, table information etc.
OpenQuery gets all these information from Linked Server where as in OpenRowSet, you have to provide all these information at run time. There is no other difference.
Syntaxes of both the commands from Microsoft’s website:
( { ‘provider_name’ , { ‘datasource’ ; ‘user_id’ ; ‘password’
   | ‘provider_string’ }
   , {   [ catalog. ] [ schema. ] object
       | ‘query’
   | BULK ‘data_file’ ,
       { FORMATFILE = ‘format_file_path’ [ ]
} )
   [ , CODEPAGE = { ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’ } ]
   [ , ERRORFILE = ‘file_name’ ]
   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , MAXERRORS = maximum_errors ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,…n ] ) [ UNIQUE ]
OPENQUERY ( linked_server ,'query' )
Let us see how does it works, we are going to create one access file named “acc1.accdb” and will create one Employee table inside “acc1.accdb” file which will be stored in D drive of computer. You can have your own access file at your desired location.
Here is the query for the same:
–add link server
exec sp_addlinkedserver
–if you have older version of access then kindly use old jet provider
–add MDB’s credental, if any
EXEC sp_addlinkedsrvlogin ‘LinkToAceess’, ‘false’
exec sp_serveroption [LinkToAceess],‘Data Access’,‘true’
–check whether ‘LinkToAccess’ has been added
select * from sys.servers
–list all tables available in Testing.MDB
exec sp_tables_ex ‘LinkToAceess’
–now query with OPENSOURCE
SELECT * FROM OPENQUERY(LinkToAceess, ‘SELECT id,empname FROM employee’)
–now query with OPENROWSET,
–which will not use our linked server
   FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
–if you get following error
–Msg 15281, Level 16, State 1, Line 1
–you have to enable “Ad Hoc Distributed Queries”
sp_configure ‘show advanced options’,1
sp_configure ‘Ad Hoc Distributed Queries’,1
–now try again same query
Hope you have enjoyed!!!!
Reference: Ritesh Shah
Ask me any SQL Server related question at my “ASK Profile

4 thoughts on “OpenRowSet and OpenQuery in SQL Server 2005/2008

  1. Pingback: Populate table from Stored Procedure resultset in SQL Server

  2. Pingback: Error Fix Database Mail XPs error in SQL Server - Ritesh Shah (Extreme-Advice.Com)

Comments are closed.