SQLTeam.com | Weblogs | Forums

SQL Server Compact Provider in SQL Server 2014 for OPENROWSET

sql2012

#1

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.


#2

Instead of looking for a positive statement that you can do it, just try it. AFIETAP


#3

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?


#4

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?