I'm unfortunately well aware from extensive reading that SQL Server Compact 4 can no longer be opened as an object in SQL Server 2014 Management Studio.
However I can't find any definitive information on whether or not I can add Microsoft.SQLSERVER.CE.OLEDB.4.0 as a provider in SQL Server 2014.
My intention is to automate data extraction from a number of SQL Server Compact databases using OPENROWSET - I already have a similar automation process in place to open Visual FoxPro DBF files with the VFPOLEDB provider.
It seems beyond belief that I can access an archaic database format via this method but not SQL Server Compact 4.
Instead of looking for a positive statement that you can do it, just try it. AFIETAP
If I knew how to just try it I surely would!
At first I attempted the following (assuming that if the provider were there it would work:
SELECT * FROM OPENROWSET('Microsoft.SQLSERVER.CE.OLEDB.4.0','Data Source=D:\2015-07.sdf;','SELECT * FROM Orders')
However this returned the following error:
Msg 7373, Level 16, State 2, Line 1 Cannot set the initialization properties for OLE DB provider "Microsoft.SQLSERVER.CE.OLEDB.4.0" for linked server "(null)".
When I look at linked server providers in SQL Management Studio I cannot see the provider in question, hence my question about whether or not I can add it. My Visual FoxPro provider was simply a case of download and install, but I already have SQL Server Compact 4 installed and am unable to find a separate provider (if one exists).
So I shall explicitly rephrase my question: how can I do it?
did you try removing 'Data Source=' from the second parameter? I don't see that being used in the MS examples, e.g.
OPENROWSET
Also to consider. are you running 64-bit SQL Server? If so, do you have a 64-bit driver for CE?