3

I have an ADO.NET Managed Data Provider that is registered in machine.config in DbProviderFactory - It is available for use from, say, Analysis Services, so I know it is correctly registered.

However, I need to be able to query the managed provider from Excel, but the managed provider doesn't appear as a choice from Data Link Properties | All Ole Db Providers.

How do I get an ADO.NET Managed Data Provider to appear there, or is there another technique I need to use?

Thanks in advance, Eli.

Community
  • 1
  • 1
Eli
  • 1,315
  • 1
  • 16
  • 29
  • As far as I know, Excel is still just a COM enabled application, so it's not possible to use .NET data providers. It would be sweet if you could though. – Josh Pearce Nov 23 '09 at 21:09
  • I really hope this isn't the case - ADO.NET has been out for years before Office 2007 and they were hyping managed providers - It would be foolishness on MS's part to ignore it in the Office lineup. – Eli Nov 24 '09 at 18:36

3 Answers3

3

Turns out there is no way to directly use an ADO.NET provider in Excel.

Had to write an OLE DB provider (in ATL C++ no less) to bridge the two - that was fun...

Eli
  • 1,315
  • 1
  • 16
  • 29
1

It is a late post, but now there is an ADO.NET way to this - http://support.microsoft.com/kb/316934#12

Ognyan Dimitrov
  • 6,026
  • 1
  • 48
  • 70
0

I have a potential workaround.

I am going to create a CLR stored procedure that will in turn perform a passed SQL query using the managed provider.

I can then invoke the stored procedure using SQLServer OLE DB or ODBC providers.

It should work, if Murphy stays away.

Eli
  • 1,315
  • 1
  • 16
  • 29
  • Murphy didn't stay away - Turns out, SQLServer doesn't permit my assembly to load because it is dependent on a managed C++ assembly that is not able to load into SQLServer, even if I allow unsafe. – Eli Dec 29 '09 at 19:52