Unable to import from an excel into a Temp table

• Hakuna Matata

  SSCertifiable

  Points: 5577

  More actions

  November 17, 2011 at 5:40 am

  #252093

  Hi,

  I am unable to import from an excel file into a temporary table.

  When I try to run the below query on SQL Server 2008 R2:

  SELECT * INTO #TempTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=D:\WTM.xls;HDR=YES',

  'SELECT *

  FROM [Sheet1$]')

  I get the following error message:

  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.

  How can I resolve this issue?

  Thanks & Regards,

  Nithin

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  November 17, 2011 at 5:43 am

  #1409114

  It would've probably been quicker to enter that error into Google than it was to ask here.

  sp_configure 'show advanced options', 1;

  GO

  RECONFIGURE;

  GO

  sp_configure 'Ad Hoc Distributed Queries', 1;

  GO

  RECONFIGURE;

  GO

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Hakuna Matata

  SSCertifiable

  Points: 5577

  More actions

  November 17, 2011 at 5:57 am

  #1409122

  Thats right I did get it from google

  EXECUTE SP_CONFIGURE 'show advanced options', 1

  RECONFIGURE WITH OVERRIDE

  GO

  EXECUTE SP_CONFIGURE 'Ad Hoc Distributed Queries', '1'

  RECONFIGURE WITH OVERRIDE

  GO

  EXECUTE SP_CONFIGURE 'show advanced options', 0

  RECONFIGURE WITH OVERRIDE

  GO

  I did try searching on google , but couldnt find the solution. I tried again after posting on SSC & got the solution.

  Thanks a loooooooooooot!!!!!!! Cadavre for the help:-):-):-):-):-)