CODEWITHSUNDEEP
sqlsql-serveropenrowsetadhoc-queries
Wonwoo Jeon
Wonwoo Jeon

Reputation: 1

How to use the OPENROWSET without setting Ad Hoc Distributed Queries to true?

I’ve been trying to use OPENROWSET function in SQL to upload data from an Excel file to SQL Server. According to my research, Ad Hoc Distributed Queries must be set to true. Unfortunately, I cannot modify the Server Configuration due to security risk. Are there any alternatives here?

Here’s a sample code:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=filepath;HDR=YES’,'SELECT * FROM [Sheet1$]');

I’ve tried executing the SQL query without setting the Ad Hoc Distributed Queries to true but of course it causes an error. I just want to upload the data from the excel file to the SQL Server efficiently using a SQL query.

Upvotes: 0

Views: 197

Answers (0)

Related Questions