Describe how OPENROWSET and OPENDATASOURCE function is used to access external data.
OPENROWSET: Includes all connection information that is required to access remote data from an OLE DB data source. It can be references from a FROM clause and is used to access the tables on a linked server. It can also be referenced as a target table of INSERT, DELETE, and UPDATE statements.
Syntax: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 }
} )
ExampleSELECT * FROM OPENROWSET('SQLNCLI',Server=Brighton1;Trusted_Connection=yes;', 'SELECT sal, employee_name FROM Company.employee.ID')
OPENDATASOURCE: Provides ad hoc connection information without a linked server name.
Syntax:OPENDATASOURCE ( provider_name, init_string )
Example: The example cretes a ad hoc connection to the payroll instance of a SQL server in US. It queries the Company.HumanResources.Employee table.
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=US\Payroll;Integrated Security=SSPI') . Company.HumanResources.Employee
