Said both of the techniques (OpenRowSet
and 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:
OPENROWSET
(
{ 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
|
'provider_string' }
,
{ [ catalog. ] [ schema. ] object
| 'query'
}
|
BULK 'data_file' ,
{ FORMATFILE
= 'format_file_path' [ ]
| SINGLE_BLOB
| SINGLE_CLOB | SINGLE_NCLOB }
}
)
[
, 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 ]
OpenSource:
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
@server='LinkToAceess',
@srvproduct='AccessDatabase',
--if you have
older version of access then kindly use old jet provider
--Microsoft.Jet.OLEDB.4.0
@provider='Microsoft.ACE.OLEDB.12.0',
@datasrc='D:\db1.accdb'
GO
--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
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'D:\db1.accdb';
'';'',Employee);
--if you get
following error
--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', see "Surface Area
Configuration" in SQL Server Books Online.
--you have to
enable "Ad Hoc Distributed Queries"
sp_configure 'show advanced options',
1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries',
1
reconfigure
GO
--now try again
same query
SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'D:\db1.accdb';
'admin';'',Employee);
Hope you have enjoyed!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”
2 comments:
Thanks Mate.. It work well.. ayos!
Thanks mate it work well.. ayos!
Post a Comment