OPENQUERY vs. OPENROWSET

Can someone explain to me what do OPENQUERY and OPENROWSET do, how and when are they used and what is the difference between the two. Please do not provide links to articles, I tried reading different articles but still cannot completely understand those two
LVL 35
YZlatAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
syntax:

OPENQUERY ( linked_server ,'query' )

OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
   | 'provider_string' }
      , {   [ catalog. ] [ schema. ] object
       | 'query'
     }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

so, openquery uses a predefined linked server, while openrowset has to sepcify all the connection options.

for the rest, it's the same

0
 
YZlatAuthor Commented:
thanks Angel!
0
 
hpsuserCommented:
One quick comment:  In limited experience using both, I have found that OpenRowSet can perform better (some queries that bomb out using OpenQuery after a while because of resource issues run using OpenRowSet)....I haven't done enough testing to know for sure that this is the cause was OPENQUERY vs. OPENROWSET, but FWIW I always use OpenRowSet when possible.
0
All Courses

From novice to tech pro — start learning today.