How the OPENROWSET Function Works in SQL Server (With Examples)

SQL Server OPENROWSET isn't just a function. It's one of the most underrated data superpowers in SQL Server. Imagine being able to query an Excel sheet, a CSV file, a log dump, or even data in cloud storage as if it were a table in your database. No lengthy imports. No complicated ETL pipelines. No linked server setup headaches. Just one command, and the data is right there in your query window.

That's precisely what SQL OPENROWSET delivers.

Whether you're analyzing Excel reports, pulling raw CSV files into SQL Server for investigation, integrating data from external systems, or performing lightweight bulk data imports, OPENROWSET lets you do it in seconds.

Data engineers love it for rapid file ingestion. Analysts use it to explore external datasets. Developers rely on it for seamless system connections, and DBAs value its speed, flexibility, and integration with SQL Server, without any need for additional tools.

In this guide, we'll walk through how OPENROWSET in SQL Server works, its syntax, and real-world examples that you can use right away. You'll also learn best practices and innovative alternatives to keep in your toolbox.

Let's dive in.

What is OPENROWSET in SQL Server?

OPENROWSET in SQL Server is a Transact-SQL (T-SQL) function that provides ad hoc access to external data sources through Object Linking and Embedding, Database (OLE DB). In simple terms, it allows you to query data that lives outside SQL Server, such as Excel files, CSVs, text files, and even other database systems, and treat it like a regular table inside your query.

While OPENROWSET works similarly to OPENDATASOURCE, there's a significant difference between them:

• OPENROWSET is designed for ad hoc, one-time data access, and it's ideal for quick imports or exploratory queries.
• OPENDATASOURCE is more suited for referencing external sources in a structured, query-driven manner without permanently configuring a linked server. But it is still not as convenient as OPENROWSET for fast, on-the-fly use.

The simplicity of SQL OPENROWSET makes it valuable to database professionals, including DBAs, data engineers, developers, and data analysts who frequently work with external files or need to integrate external systems for reporting, analysis, or data migration.

Syntax of OPENROWSET

The OPENROWSET SQL function supports a flexible syntax that lets you query external data using OLE DB providers or import file-based data in bulk. The syntax you use depends on the operation you want to perform. Below is the general structure of the OPENROWSET syntax:

OPENROWSET ( 
    provider_name, 
    'provider_string', 
    query 
);

Key components

• provider_name: The OLE DB provider used to access the external source.
• provider_string: Connection details, such as server, database, login credentials, or file options.
• query: The SQL query executed against the external data source.

Syntax for remote queries

If you want to use the OPENROWSET to run queries against another database server (such as SQL Server or Oracle) via an OLE DB provider, here is the syntax structure to use:

SELECT * 
FROM OPENROWSET( 
    'SQLNCLI', 
    'Server=RemoteServerName;Database=RemoteDB;UID=User;PWD=Password;', 
    'SELECT Column1, Column2 FROM RemoteTable' 
); 

Example using an OLE DB provider

SELECT * 
FROM OPENROWSET( 
    'MSOLEDBSQL', 
    'Server=192.168.1.20;Database=SalesDB;UID=admin;PWD=StrongPassword;', 
    'SELECT CustomerID, OrderTotal FROM dbo.Orders' 
);

This approach is helpful for:

• Querying remote SQL Server instances.
• Accessing other RDBMS platforms through OLE DB providers (e.g., Oracle, MySQL).
• Migrating or synchronizing data between different systems.

Syntax for BULK data access

OPENROWSET also comes in handy for bulk data access, including importing text, log, or binary files into SQL Server. You can use the OPENROWSET to read data directly from these files using the BULK option. Here is the syntax for OPENROWSET bulk data access:

SELECT * 
FROM OPENROWSET( 
    BULK 'C:\Data\file.txt', 
    SINGLE_CLOB 
) AS DataFile; 

Also, when working with BULK, you can control how SQL Server reads the file using one of the following modes:

These modes determine how the file content is loaded into the BulkColumn field. They make it easy to store or process large files directly in SQL Server.

OPENROWSET examples

Below are practical examples that demonstrate how to use OPENROWSET for file access, Excel imports, remote queries, and binary file handling.

Reading a text or CSV file with BULK

To use SQL OPENROWSET to read the contents of a text or CSV file directly into SQL Server, use the SINGLE_CLOB option. When you do, SQL Server treats the entire file as one large text value and returns it in a special column named BulkColumn. For instance:

SELECT BulkColumn 
FROM OPENROWSET( 
    BULK 'C:\Data\data.csv', 
    SINGLE_CLOB 
) AS DataFile; 

How it works:

• BULK tells SQL Server to load data from a file stored on the server or an accessible network path.
• SINGLE_CLOB reads the file as a single ASCII text string (ideal for plain text or CSV files).
• The output appears in the BulkColumn field, which contains the full file contents in one row.

This method is particularly useful when you want to preview file contents quickly, perform text-based transformations, or stage raw data before breaking it into rows and columns using functions like STRING_SPLIT, JSON parsing, or external tools.

Importing Excel files with OPENROWSET

OPENROWSET is also handy for importing Excel files. With OPENROWSET, SQL Server can read data directly from Excel files using the Microsoft.ACE.OLEDB.12.0 provider. This approach allows you to query a specific worksheet just as you would a table. For example, the query below reads data from Sheet1:

SELECT * 
FROM OPENROWSET( 
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;HDR=YES;Database=C:\Data\SalesReport.xlsx', 
    'SELECT * FROM [Sheet1$]' 
);

How it works:

• Microsoft.ACE.OLEDB.12.0 is the provider used for reading Excel files.
• HDR=YES tells SQL Server that the first row contains column names.
• [Sheet1$] specifies the worksheet to query.

Requirements:

To import Excel files with SQL OPENROWSET, here are the essential setups you need:

• Download and install the Microsoft Access Database Engine (ACE) OLE DB provider if you don't have it.
• Grant the SQL Server service account permission to access the file path.
• Ensure the Excel file is accessible to the SQL Server instance.

Querying a remote SQL Server with OPENROWSET

OPENROWSET can also be used to run a query against another SQL Server instance without setting up a linked server. You simply provide the connection details and the query you want to execute on the remote server. For instance:

SELECT * 
FROM OPENROWSET( 
    'MSOLEDBSQL', 
    'Server=192.168.10.50;Database=HRDB;UID=dbuser;PWD=StrongPassword;', 
    'SELECT EmployeeID, FirstName, LastName FROM dbo.Employees' 
); 

How it works:

• MSOLEDBSQL specifies the OLE DB provider for SQL Server.
• The connection string includes the server name, database name, and login credentials.
• The third parameter is the query executed on the remote database.

This approach comes in handy when you need to complete any of the following:

• Pull data from another SQL Server for reporting or quick analysis.
• Transfer or synchronize data between database servers.
• Perform one-time admin or troubleshooting queries without permanent configuration.

Querying a remote SQL Server with OPENROWSET gives you flexibility for fast, one-off remote queries, but for ongoing or production-level data exchange, linked servers or ETL tools are usually a better long-term choice.

Importing binary files into SQL Server

You can use the SQL OPENROWSET to import binary files, such as PDFs, images, or other documents, into SQL Server. To achieve this, use the SINGLE_BLOB option. SQL Server reads the file as binary data and returns it in the BulkColumn field. For instance:

INSERT INTO dbo.Documents (FileName, FileData) 
SELECT 'Report.pdf', BulkColumn 
FROM OPENROWSET( 
    BULK 'C:\Files\Report.pdf', 
    SINGLE_BLOB 
) AS BinaryFile; 

How it works:

• SINGLE_BLOB reads the entire file as binary data (suitable for PDFs, images, media, etc.).
• BulkColumn stores the raw binary content returned by OPENROWSET.
• FileData should be a VARBINARY(MAX) column to hold the file data.

This method is useful when you need to store documents or media files directly inside SQL Server, such as for archiving, document management systems, or applications that handle BLOB data.

OPENROWSET vs alternatives

The table below compares OPENROWSET and its alternatives.

Conclusion

OPENROWSET is a powerful and versatile feature in SQL Server ideal for ad hoc data imports, accessing external files, and running remote queries. Whether you're a database developer, analyst, or DBA, this feature lets you quickly access external data without extensive setup, linked servers, or complex ETL processes.

However, while SQL OPENROWSET excels at on-demand tasks and one-time data processing, it isn't necessarily the best choice for production pipelines. Because it relies on security configurations, actual file paths, and system-level settings, it may require ongoing maintenance and increase risk in enterprise environments.

For long-term, scalable, and secure data automation, consider alternatives such as BULK INSERT, SSIS, PolyBase, or external data tools that offer stronger governance and operational control.

If you need a more advanced solution for safe, flexible, and user-friendly SQL workflows that cover everything from data import/export to advanced queries and full-scale database development, your best bet is dbForge Studio for SQL Server. It's a powerful, all-in-one IDE designed to streamline your work and accelerate the performance of your databases.

Additionally, if your workflow includes connecting SQL Server to BI systems, reporting tools, or external apps, the ODBC Driver for SQL Server is your perfect companion. It offers high-performance data access, secure encryption, full ODBC compliance, and cross-platform support, making it ideal for stable and efficient data connectivity in any environment.

Download the free trial of the dbForge Studio for SQL Server and experience a better and faster database development and management process.

FAQ