Use OPENROWSET and OPENDATASOURCE to Access a Linked Server

SQL Server supports ad hoc access for infrequently accessed data using OPENROWSET and OPENDATASOURCE. However, you achieve the best results using the OPENQUERY command.

The following examples show how to use OPENROWSET and OPENDATASOURCE to access the Historian OLE DB provider as a linked server in Microsoft SQL Server.

Example 1: Use OPENROWSET With a SQL Query

Note: The following example uses double quotes surrounding date times, because single quotes do not work inside the overall single-quoted query. It is important for you to use double quotes in this scenario.

SELECT * FROM OPENROWSET('ihOLEDB.iHistorian.1', 'MY_SERVER';'';'','SET starttime="2002-01-30 10:00:00", endtime="2002

Example 2: Use OPENDATASOURCE to Access a Table

SELECT * FROM OPENDATASOURCE('iHOLEDB.iHistorian.1', 'Data Source=MY_SERVER')...ihTags

Example 3: Use OPENDATASOURCE With a SQL Query and Security

SELECT * FROM OPENDATASOURCE('iHOLEDB.iHistorian.1', 'Data Source=MY_SERVER;User ID=user1;Password=thepassword')...[SE