Snippet Name: SQL_Server_OPENROWSET_Error

Tags:

Created Date: 2025-06-16 13:12:48
Last Modified Date: 2025-05-31 10:31:47

Dependencies:

DBA Notes:

Sample Output:

Title: SQL Server OPENROWSET Error
Author: user
In SQLServer2019, using openquery,i am getting the error "incorrect syntax near the word 'FROM' from the following query, but i don't see the issue: SELECT  * FROM    OPENROWSET( 'SQLNCLI',
                    'Server=AZU-V1-DEV1.cigfinancial.com,1433;Trusted_Connection=yes;',
                    'SELECT * FROM [CIG_Report].[dbo].[SLBackfillBatchFile] ;'
                  )

Author: ChatGPT
The error you're seeing in SQL Server 2019 is likely caused by an incorrect syntax within the `OPENROWSET` query or the way it's being used. Let's break down the possible causes and a way to fix the issue.

1. **Quoting of the internal query:** The `OPENROWSET` function in SQL Server treats the inner query string literally, so any single quotes (`'`) inside the query need to be escaped to prevent SQL syntax issues.

2. **Provider Name:** The provider name `'SQLNCLI'` is outdated, and it's often better to use the updated provider like `'SQLNCLI11'` or `'SQLNCLI10'` depending on the installed version. However, using OLE DB provider `MSOLEDBSQL` is now generally recommended since older providers are deprecated.

Here???s how you can fix the query:

### Updated Query
```sql
SELECT * 
FROM OPENROWSET(
    'SQLNCLI11',
    'Server=AZU-V1-DEV1.cigfinancial.com,1433;Trusted_Connection=yes;',
    'SELECT * FROM [CIG_Report].[dbo].[SLBackfillBatchFile]'
);
```

If the `SQLNCLI11` provider is not installed or supported, consider using the latest OLE DB provider:

```sql
SELECT * 
FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=AZU-V1-DEV1.cigfinancial.com,1433;Trusted_Connection=yes;',
    'SELECT * FROM [CIG_Report].[dbo].[SLBackfillBatchFile]'
);
```

### Explanation:
- **Escaping Quotes:** The internal query string should not have extra single quotes around it when using `OPENROWSET`.
- **Provider Name:** Ensure that the SQL Native Client (`SQLNCLI11`) or OLE DB provider (`MSOLEDBSQL`) is installed.

You should also ensure that the server and authentication method are correctly set up. The `Trusted_Connection=yes;` option will use Windows Authentication, so ensure that the account running the query has appropriate permissions.

If you continue to face issues or see different errors, please provide more context on the environment or error messages.

---

**If you enjoy using this GPT, please rate it 5??? and follow GPT creator's profile on LinkedIn for the latest updates and insights** [https://linkedin.com/in/khanukov/](https://linkedin.com/in/khanukov/)

Author: user
your code improvement helped, but help me toggle ad hoc distributed queries on, and hten off again, so i can runt eh query

Author: ChatGPT
To toggle **Ad Hoc Distributed Queries** on and off in SQL Server, you need to enable and disable the option `Ad Hoc Distributed Queries` using the system stored procedure `sp_configure`.

Here???s how to do it:

### 1. **Enable Ad Hoc Distributed Queries**

```sql
-- Enable advanced options first
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Enable Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
```

This will allow the use of `OPENROWSET` and `OPENDATASOURCE` in your queries.

### 2. **Run Your Query**

Once the feature is enabled, you can run your `OPENROWSET` query (the one we fixed earlier):

```sql
SELECT * 
FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=AZU-V1-DEV1.cigfinancial.com,1433;Trusted_Connection=yes;',
    'SELECT * FROM [CIG_Report].[dbo].[SLBackfillBatchFile]'
);
```

### 3. **Disable Ad Hoc Distributed Queries (Optional)**

Once you're done running the query, you can disable the feature again for security reasons, as it's generally not recommended to leave it enabled in production environments unless needed:

```sql
-- Disable Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;

-- Optionally, disable advanced options if you don't need them anymore
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
```

This process ensures that the option is turned on only when needed and then safely turned off again to minimize potential security risks.

---

**If you enjoy using this GPT, please rate it 5??? and follow GPT creator's profile on LinkedIn for the latest updates and insights** [https://linkedin.com/in/khanukov/](https://linkedin.com/in/khanukov/)