OPENDATASOURCE

OPENDATASOURCE is a function in SQL Server that provides ad hoc connection information as part of a four-part object name without using a linked server name. It's typically used for accessing remote data from an OLE DB data source.

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.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.