Solved

OPENQUERY vs. OPENROWSET

Posted on 2007-04-09
3
3,284 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:YZlat
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 18876951
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
 
LVL 35

Author Comment

by:YZlat
ID: 18877038
thanks Angel!
0
 

Expert Comment

by:hpsuser
ID: 23006165
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now