Home › SQL Server

SQL Server OPENDATASOURCE


Connecting to a SQL Server database from SQL Server using OPENDATASOURCE (Windows Authentication)

-- server: server1
-- database: AdventureWorks

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    Data Source=server1;Integrated Security=SSPI')
    .AdventureWorks.HumanResources.Employee

Reference

Connecting to a SQL Server database from SQL Server using OPENDATASOURCE (SQL Server Authentication)

-- server: server1
-- database: AdventureWorks
-- User name: mike
-- password: mike123

SELECT   *
FROM OPENDATASOURCE(
            'SQLOLEDB',
            'Data Source=server1;User ID=mike;Password=mike123'
        ).AdventureWorks.HumanResources.Employee

Reference

Connecting to an Excel File from SQL Server using OPENDATASOURCE

-- file name: c:\test.xls

SELECT * 
FROM OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source=C:\test.xls;Extended Properties=EXCEL 5.0'
)...[Sheet1$];

Reference

Connecting to an Access MDB File from SQL Server using OPENDATASOURCE

-- MDB File: c:\temp\payroll.mdb
-- Table Name: employees

SELECT * FROM 
OpenDataSource( 
    'Microsoft.Jet.OLEDB.4.0', 
    'Data Source="c:\temp\payroll.mdb";
    User ID=Admin;Password=;')...employees

Reference

Share |
 Cant find the page you are looking for?
 Help us to improve by adding the content that you are looking for.
 Leave a feedback
 We look forward to hear your comments and feedback.
  • 3122 reads