OPENDATASOURCE

SQL Server

OPENDATASOURCE( provider_name , init_string )

• provider_name: This is the name of the OLE DB provider that is used to access the data source.
• init_string: This parameter refers to the connection string that is passed to the specified OLE DB provider. This string contains the properties required to establish the connection, or database session, with the data source.

Example

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=ServerName;Integrated Security=SSPI')
    .Northwind.dbo.Categories;

Output

CategoryID | CategoryName | Description
-----------|--------------|-------------
1          | Beverages    | Soft drinks, coffees, teas, beers, and ales
2          | Condiments   | Sweet and savory sauces, relishes, spreads, and seasonings
3          | Confections  | Desserts, candies, and sweet breads
4          | Dairy        | Cheeses
5          | Grains/Cereals | Breads, crackers, pasta, and cereal
6          | Meat/Poultry   | Prepared meats
7          | Produce         | Dried fruit and bean curd
8          | Seafood         | Seaweed and fish

Explanation

In the example, OPENDATASOURCE is used to execute a query on a remote server specified by ‘ServerName’. SQLNCLI indicates the provider name. It then accesses the Categories table in the Northwind database on the remote server. The output is a list of categories in the Northwind database’s Categories table.