Error while eporting the table data to excel file using OPENROWSET function.

• Sarath Vellampalli

  SSCertifiable

  Points: 5822

  More actions

  November 2, 2011 at 9:06 am

  #144499

  Hi All,

  I am trying to export the table data to excel file using OPENROWSET sql function.

  my code is like below:

  EXEC sp_configure 'show advanced options', 1;

  GO

  RECONFIGURE;

  GO

  EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

  GO

  RECONFIGURE;

  GO

  INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 12.0;Database=F:\contact.xls;',

  'SELECT * FROM [Sheet1$]')

  SELECT TOP 5 Policy_no, po_policy_master_pk

  FROM po_policy_master

  GO

  I have closed the excel file also, but when I execute the above code, I am getting the below error.

  OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

  Msg 7303, Level 16, State 1, Line 1

  Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

  Please help to resolve this.

  Kindest Regards,

  Sarath Vellampalli

• seth delconte

  SSCertifiable

  Points: 6388

  More actions

  November 2, 2011 at 10:19 am

  #1402253

  I think it's a file system permissions issue:

  "This is because the SQL Server Service is trying to write the temp DSN to the temp folder for the login that started the service."

  Resolutions here.

  _________________________________
  seth delconte
  http://sqlkeys.com