That's bug?

• LOVER OF SQL

  SSCommitted

  Points: 1900

  More actions

  November 4, 2013 at 10:03 am

  #278897

  howdy everybody,

  when I wrote this:

  SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\OPENROWSET\AD_SE.xlsx', 'SELECT * FROM [Sheet1$]');

  I had this:

  OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.".

  Msg 7350, Level 16, State 2, Line 1

  Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

  What I've done:

  - I had Install DatabaseEngine 2007

  - I had Install DatabaseEngine 2010

  - Stop Service/ Restart Service

  - Restart Machine

  - Add permission (Everyone and full controle to service database and user sql server (my))

  - created .reg

  Windows Registry Editor Version 5.00

  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0]

  "AllowInProcess"=dword:00000001

  And nothing...

  and more:

  sp_configure 'Show Advanced Options', 1;

  RECONFIGURE;

  GO

  sp_configure 'Ad Hoc Distributed Queries', 1;

  RECONFIGURE;

  GO

  EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

  GO

  EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

  GO

  Nothing..

  Anyone help me?

  my version:

  Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

  Windows Server 2008 R2 Enterprise 64

• Dwain Camps

  SSC Guru

  Points: 86948

  More actions

  November 4, 2013 at 7:06 pm

  #1664016

  Sheet1 (referenced as Sheet1$) is the default name for a worksheet in Excel.

  Are you sure that you did not rename that worksheet to something else?

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St