SQL Server OPEN Functions

A quick overview of OPEN functions that open files or access data sources:

OPENQUERY:
MSDN – OpenQuery
Run a query against a linked server.

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT ID, Name From TABLE');

Normally, we use four part syntax to query a linked server:

select * from LinkedServer.Database.Schema.Table;

OPENQUERY may be useful in a case where you want to query a linked Oracle server using Oracle syntax in PL/SQL.
It may also be useful to execute a function like GETDATE() on the remote server.

OPENDATASOURCE:
MSDN – OpenDataSource
Allows access to a remote data source when a linked server hasn’t been set up.

SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=Server\Instance;Integrated Security=SSPI').Database.Schema.Table

Where SQLNCLI is a provider name, in this case the SQL Server Native Client OLE DB Provider.

OPENROWSET :
MSDN – OpenRowset
Similar to OPENDATASOURCE, but we can specify the query to execute.

SELECT t.* FROM OPENROWSET('SQLNCLI', 'Data Source=Server\Instance;Integrated Security=SSPI', 'SELECT Id, Name FROM Database.Table') as t;

OPENXML:
MSDN – OpenXML
Used to query an XML document, to shred the document into a rowset view.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: