Snippet Name: Linked_Server_CSV_SQL2022
Tags:
Created Date: 2025-06-16 13:12:42
Last Modified Date: 2025-05-31 10:32:09
Dependencies:
DBA Notes:
Sample Output:
Title: Linked Server CSV SQL2022
Author: user
in older 32 bit architecture, i used to be able to create a linked server that referenced a folder full of local csv files. can you help me re-establish that capability in SQL2022?
Author: ChatGPT
Yes, in older 32-bit architectures, you could use **Microsoft.Jet.OLEDB.4.0** or **Microsoft.ACE.OLEDB.12.0** to create a linked server referencing a folder full of CSV files. However, in **SQL Server 2022 (64-bit)**, these providers are not available natively. Instead, you have a few modern options:
### **Option 1: Use PolyBase (Recommended)**
SQL Server 2022 includes **PolyBase**, which allows querying external CSV files directly.
#### **Steps to Set Up PolyBase for CSV Files**
1. **Enable PolyBase Features**
- Ensure that **PolyBase Query Service** is installed.
- Run:
```sql
EXEC sp_configure 'polybase enabled', 1;
RECONFIGURE;
```
2. **Create an External Data Source**
```sql
CREATE EXTERNAL DATA SOURCE CsvSource
WITH (
LOCATION = 'C:\CSVFolder',
FORMAT = 'TEXTFILE'
);
```
3. **Create an External File Format**
```sql
CREATE EXTERNAL FILE FORMAT CsvFormat
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2)
);
```
4. **Create an External Table**
```sql
CREATE EXTERNAL TABLE CsvData (
Column1 NVARCHAR(100),
Column2 INT,
Column3 FLOAT
)
WITH (
LOCATION = 'myfile.csv',
DATA_SOURCE = CsvSource,
FILE_FORMAT = CsvFormat
);
```
5. **Query the External Table**
```sql
SELECT * FROM CsvData;
```
---
### **Option 2: Use OPENROWSET with BULK INSERT**
If PolyBase is too complex, you can use **OPENROWSET (BULK)**:
```sql
SELECT *
FROM OPENROWSET(
BULK 'C:\CSVFolder\myfile.csv',
FORMAT = 'CSV',
PARSER_VERSION = '2.0'
) AS DataFile;
```
To allow this, enable ad-hoc queries:
```sql
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
```
---
### **Option 3: Use a Linked Server with ODBC**
1. **Install and Configure ODBC Text Driver**
- Install **Microsoft Access Database Engine (64-bit)** if needed.
- Configure a **System DSN (ODBC Data Source)** for **Microsoft Text Driver**.
2. **Create a Linked Server**
```sql
EXEC sp_addlinkedserver
@server = 'CSV_LINK',
@provider = 'MSDASQL',
@datasrc = 'CSV_ODBC_DSN';
```
3. **Query the CSV Files**
```sql
SELECT * FROM OPENQUERY(CSV_LINK, 'SELECT * FROM myfile.csv');
```
---
### **Which Option Should You Choose?**
- **PolyBase**: Best for large datasets and persistent queries.
- **OPENROWSET**: Good for one-time queries without extra setup.
- **Linked Server with ODBC**: If you need compatibility with older linked server solutions.
Let me know if you need a step-by-step guide for a specific method!
Author: user
when creatign the exteernal data source, i get the error: Msg 102, Level 15, State 1, Line 24
Incorrect syntax near 'FORMAT'.
Author: ChatGPT
The error suggests that the **CREATE EXTERNAL DATA SOURCE** statement is incorrect. Let's fix it step by step.
### **Corrected Approach for Creating an External Data Source in SQL Server 2022**
For accessing CSV files, the **LOCATION** must point to an **Azure Blob Storage**, **S3-compatible storage**, or **HDFS**, as SQL Server **PolyBase does not support local file system paths** directly.
#### **Option 1: Use Local Files with BULK INSERT or OPENROWSET**
Since PolyBase does not natively support local folders, you can use **BULK INSERT** or **OPENROWSET** instead:
```sql
SELECT *
FROM OPENROWSET(
BULK 'C:\CSVFolder\myfile.csv',
FORMAT = 'CSV',
PARSER_VERSION = '2.0'
) AS DataFile;
```
If you must use a **linked server** to read from a folder of CSVs, then **an ODBC text driver method is better**.
---
#### **Option 2: Using PolyBase with an External Data Source**
If you **really** want to use PolyBase, you must use a **network share** instead of a local drive.
1. **Ensure File Sharing is Enabled**
- Share the folder `C:\CSVFolder` as a network share, e.g., `\\MyServer\CSVFolder`.
- Grant `READ` access to the SQL Server service account.
2. **Create an External Data Source**
```sql
CREATE EXTERNAL DATA SOURCE CsvSource
WITH (
LOCATION = 'file://MyServer/CSVFolder',
CREDENTIAL = NULL -- Needed if using authentication
);
```
???? **Important:** `file://` is only supported if the files are on a remote share.
---
#### **Option 3: Use an Azure Blob or S3 Bucket**
If your CSV files are in **Azure Blob Storage**, you can define an external data source like this:
```sql
CREATE EXTERNAL DATA SOURCE CsvSource
WITH (
TYPE = HADOOP,
LOCATION = 'wasbs://[container]@[storage_account].blob.core.windows.net'
);
```
If your files are in an **S3-compatible storage**, you can use:
```sql
CREATE EXTERNAL DATA SOURCE CsvSource
WITH (
TYPE = HADOOP,
LOCATION = 's3://mybucket',
CREDENTIAL = MyS3Credential
);
```
---
### **Key Takeaways**
1. **PolyBase does not support `FORMAT = 'TEXTFILE'` with local files.** The `FORMAT` clause is only used when defining **external file formats** separately.
2. **Use `OPENROWSET` or `BULK INSERT` for local files.**
3. **Use an external data source pointing to a network share (`file://`), Azure Blob Storage (`wasbs://`), or S3 (`s3://`).**
Would you like help setting up an ODBC text driver as a linked server instead? That would be closer to the old method from 32-bit SQL Server.
Author: user
with the openrowset command modified to work on my paths, i get this error: Msg 102, Level 15, State 9, Line 31
Incorrect syntax near 'PARSER_VERSION'.
Author: ChatGPT
The error **"Incorrect syntax near 'PARSER_VERSION'"** indicates that the **PARSER_VERSION = '2.0'** option is not supported in your SQL Server instance. The `PARSER_VERSION` option was introduced in SQL Server 2019 and later, but it only applies to **Parquet format**, not CSV.
### **Corrected `OPENROWSET` for CSV Files**
Try the following **simplified** version of `OPENROWSET`:
```sql
SELECT *
FROM OPENROWSET(
BULK 'C:\CSVFolder\myfile.csv',
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
) AS DataFile;
```
### **Explanation of Fixes:**
- **Removed `PARSER_VERSION`** ??? This is only used for Parquet.
- **Added `FIRSTROW = 2`** ??? Skips the first row if it contains headers.
- **Added `FIELDTERMINATOR = ','`** ??? Specifies a comma as the delimiter.
- **Added `ROWTERMINATOR = '\n'`** ??? Ensures proper line breaks.
---
### **Additional Fixes if You Still Get an Error**
#### **1. Enable Ad-Hoc Queries (Required for `OPENROWSET`)**
SQL Server **disables `OPENROWSET` by default** for security reasons. To enable it:
```sql
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
```
#### **2. Ensure the SQL Server Service Account Has Access**
If your CSV file is stored in `C:\CSVFolder\`, ensure that the **SQL Server service account** has **read** permissions on the file.
---
### **Alternative: Use `BULK INSERT` Instead**
If `OPENROWSET` still fails, try `BULK INSERT`, which is more reliable:
```sql
BULK INSERT MyTable
FROM 'C:\CSVFolder\myfile.csv'
WITH (
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
TABLOCK
);
```
---
### **Summary**
- ??? **Remove `PARSER_VERSION = '2.0'`** (not needed for CSV).
- ??? **Ensure `Ad Hoc Distributed Queries` is enabled.**
- ??? **Verify file permissions.**
- ??? **Use `BULK INSERT` if `OPENROWSET` still fails.**
Let me know if you need more debugging steps! ????
Author: user
can you modify the Msg 5369, Level 16, State 1, Line 34
Option 'FIELDTERMINATOR/DELIMITER' is not supported for locations with 'C:\Data\CSVDataSources\Untitled2.txt' connector when specified FORMAT is 'CSV'.
Author: ChatGPT
The error **"Option 'FIELDTERMINATOR/DELIMITER' is not supported for locations with 'C:\Data\CSVDataSources\Untitled2.txt' connector when specified FORMAT is 'CSV'"** occurs because **SQL Server 2022's `OPENROWSET(BULK...)` for `FORMAT='CSV'` does not allow manual specification of `FIELDTERMINATOR` and `ROWTERMINATOR`**.
### **Fixed `OPENROWSET` Query**
Remove the `FIELDTERMINATOR` and `ROWTERMINATOR` options:
```sql
SELECT *
FROM OPENROWSET(
BULK 'C:\Data\CSVDataSources\Untitled2.txt',
FORMAT = 'CSV',
FIRSTROW = 2
) AS DataFile;
```
---
### **Explanation of Fixes**
- **Removed `FIELDTERMINATOR` and `ROWTERMINATOR`** ??? `FORMAT='CSV'` already assumes a standard CSV delimiter (`,`).
- **Kept `FIRSTROW = 2`** ??? If the first row contains column headers, this skips it.
---
### **If the CSV Uses a Different Delimiter**
If your file is **not comma-delimited** (e.g., uses `|` or `;` as a delimiter), you must **use `BULK INSERT` instead**, because `OPENROWSET` does not allow changing the delimiter for `FORMAT='CSV'`.
```sql
BULK INSERT MyTable
FROM 'C:\Data\CSVDataSources\Untitled2.txt'
WITH (
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDTERMINATOR = '|', -- Change this to match your delimiter
ROWTERMINATOR = '\n'
);
```
---
### **If `OPENROWSET` Still Fails**
Ensure **Ad Hoc Distributed Queries** is enabled:
```sql
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
```
---
### **Key Takeaways**
1. ???? **Remove `FIELDTERMINATOR` and `ROWTERMINATOR`** from `OPENROWSET(BULK... FORMAT='CSV')`.
2. ??? If your file is not comma-delimited, use **`BULK INSERT`** instead.
3. ???? Enable **Ad Hoc Distributed Queries** if needed.
Let me know if you need further troubleshooting! ????
Author: user
Msg 472, Level 16, State 1, Line 41
Either a format file or one of the three options SINGLE_BLOB, SINGLE_CLOB, or SINGLE_NCLOB must be specified.
Author: ChatGPT
### **Fixing Msg 472: "Either a format file or one of the three options SINGLE_BLOB, SINGLE_CLOB, or SINGLE_NCLOB must be specified."**
This error occurs because SQL Server **requires an explicit format specification when using `OPENROWSET(BULK...)` for CSV files.** By default, it expects a **format file** unless `SINGLE_BLOB`, `SINGLE_CLOB`, or `SINGLE_NCLOB` is specified.
---
## **??? Corrected `OPENROWSET` Query for CSV Files**
To fix the error, you **must** specify `SINGLE_CLOB` for text-based files:
```sql
SELECT *
FROM OPENROWSET(
BULK 'C:\Data\CSVDataSources\Untitled2.txt',
SINGLE_CLOB
) AS DataFile;
```
### **???? Explanation:**
- **`SINGLE_CLOB`**: Reads the entire CSV file as a **single `VARCHAR(MAX)` string**.
- **`SINGLE_NCLOB`**: Use this if your file is **UTF-16 (Unicode)**.
- **`SINGLE_BLOB`**: Use this for **binary files** (not needed for CSV).
---
## **???? If You Need Column Splitting (Instead of Just a Text Blob)**
If you need **structured data (column-separated values),** you must **use `BULK INSERT` or an XML format file.**
### **??? Corrected `BULK INSERT` Query for CSV Files**
```sql
BULK INSERT MyTable
FROM 'C:\Data\CSVDataSources\Untitled2.txt'
WITH (
FORMAT = 'CSV',
FIRSTROW = 2
);
```
### **???? If Your CSV Uses a Different Delimiter (e.g., `|`, `;`)**
```sql
BULK INSERT MyTable
FROM 'C:\Data\CSVDataSources\Untitled2.txt'
WITH (
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDTERMINATOR = '|', -- Change this to match your delimiter
ROWTERMINATOR = '\n'
);
```
---
## **???? Summary of Fixes**
| Issue | Fix |
|---|---|
| **Msg 472: Requires format file or `SINGLE_CLOB`** | ??? Use `SINGLE_CLOB` in `OPENROWSET` |
| **CSV file is read as one long string** | ??? Use `BULK INSERT` instead |
| **Custom delimiters (e.g., `|`, `;`)** | ??? Use `BULK INSERT` with `FIELDTERMINATOR` |
Would you like help setting up a **format file** for full control over the CSV parsing? ????