May 16, 2012 at 8:26 pm
I have a requirement to read a txt file from a query window & I tried with below statement.
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;','select * from Test.txt')
Error:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Then we set 'Ad Hoc Distributed Queries' to true. After setting this when i tried the same statement above i am getting below error
Error:
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.
Can anyone please help.
Thanks in Advance!
May 16, 2012 at 10:06 pm
Check the DisallowAdhocAccess property of the provider (under Linked Servers in Object Explorer).
I'll close by just saying it might be easier in the long run if you moved your efforts into producing an SSIS package to bring this data file into a table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply