none
SQL Server Compact OLE DB Provider

    Question

  • Hello,

    if I connect a SQL Server CE 3.5 Database (created though a Pocket PC .NET Application) with the SQL Server Compact OLE DB Provider (Connection String: "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Source=C:\test.sdf"), I get the error message "Multiple-step OLE DB operation generated errors" when opening ADO connection.
    I tried this with SQL Server 2005 Compact Edition and the new 3.5 Version.
    This is the case for Visual Studio connections (OLE DB) and Delphi (TAdoConnection). The other interesting part is that the OLE DB Provider is installed (i can connect the database if I press the test button in Visual Studio) but not listed as possible Provider.
    I need the OLE DB Provider because I want to access the database through my Delphi VCL (no .net) Application. Anyone here that can help please?

    Thanks,
    Joachim
    Wednesday, June 04, 2008 2:10 PM

Answers

  • If you are using managed code, it is recommended to use: System.Data.SqlCeClient rather that System.Data.OleDbClient. If you are using other langauage, try to change the connection string to

    "Data Source=" (not "Source=") 

     

    This works fine on my PC:

     

    Code Snippet

    private void button1_Click(object sender, EventArgs e)

    {

    OleDbConnection c = new OleDbConnection();

    c.ConnectionString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\35Merge.sdf";

    c.Open();

    }

     

     

    Thursday, June 05, 2008 11:27 AM
    Moderator

All replies

  • This blog post demonstrates how to use the OLEDB provider from VBA with ADO (if that is of any help in relation to Delphi, I am not sure): http://erikej.blogspot.com/2008/04/import-sql-compact-data-to-excel.html

     

    Wednesday, June 04, 2008 6:45 PM
    Moderator
  • Hi Erik,

    the connection string in this blog is the same as I use. It does not matter if it the ADO Connection comes from Delphi, VBA, Exel etc. (well I hope so). If I try to connect the database with Vistual Studio and the OLE DB Provider and press the test button, then the connection can be established. Then I press OK and I get the error "Multiple-step OLE DB operation generated errors" (even in Delphi and I think in all other applications). I think this is an ADO error, but I don´t know where the problem is.
    Could it be that the OLE DB Provider is not registered correctly by the installation process?

    Thanks,
    Joachim
    Wednesday, June 04, 2008 8:42 PM
  • The OLEDB provider is not a fully featured desktop OLEDB provider, it originates from Windows CE. You may be able to use it in code (as in the sample), but not with Visual Tools, like VS or Office.

     

    Wednesday, June 04, 2008 8:52 PM
    Moderator
  • Hello Erik,

    I made a simple example:


            private void button1_Click(object sender, EventArgs e)
            {
                OleDbConnection c = new OleDbConnection();
                c.ConnectionString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Source=C:\test.sdf";
                c.Open();
            }

    Then I get a OleDbException: "Fehler bei einem aus mehreren Schritten bestehenden OLE DB-Vorgang.  Prüfen Sie die einzelnen OLE DB-Statuswerte, falls vorhanden. Daten wurden nicht verarbeitet ." 
    The english error message should be: "Multiple-step OLE DB operation generated errors ...".

    Here I don´t use visual tools. I think this shoud work, shouldn´t it?

    The provider I installed:  http://www.microsoft.com/downloads/details.aspx?FamilyID=7849b34f-67ab-481f-a5a5-4990597b0297&displaylang=en
    I hope this is the right one. I also tried the 3.0 Version (2005) where I get the same error.
    The dlls are under: C:\Programme\Microsoft SQL Server Compact Edition\v3.5
     including the sqlceoledb35.dll

    Thanks a lot,
    Joachim
    Thursday, June 05, 2008 8:48 AM
  • If you are using managed code, it is recommended to use: System.Data.SqlCeClient rather that System.Data.OleDbClient. If you are using other langauage, try to change the connection string to

    "Data Source=" (not "Source=") 

     

    This works fine on my PC:

     

    Code Snippet

    private void button1_Click(object sender, EventArgs e)

    {

    OleDbConnection c = new OleDbConnection();

    c.ConnectionString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\35Merge.sdf";

    c.Open();

    }

     

     

    Thursday, June 05, 2008 11:27 AM
    Moderator
  • Hello Erik,

    I tried around now everywhere and the simple error was that I used "Source" and not "Data Source". Well - sorry for my stupidity. Now it works - even in Delphi!

    Thanks a lot !!!!!!!

    Joachim
    Friday, June 06, 2008 7:20 AM
  • hi guys

    jstuemp, please can u help me accessing sdf file from delphi. please send me a brief post. some like wat to install, or a piece of code etc.



    Sunday, August 31, 2008 8:25 PM
  • Hi Ender,

    this is quite simple. Install the OLE-DB Provider (somewhere on msdn) and use the following connection string for TADOQuery, TADOConnection or else: "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\db.sdf" where you have to replace db.sdf with your file. Personally I have decided not to use oledb because it is sooooo slooooow...

    Hope that helps,
    Joachim
    Monday, September 01, 2008 8:04 AM
  • thanks for the reply joachim, i thougt u wont see my message.

    i tried that connection string before my previous message but it failed (with an exception like "provider not found") i installed sql server 2000 windows ce edition, sqlserver 2005 windows ce edition (that is sqlce3.1) and still the same. i think still ive installed the wrong one.

    wat i expect was installing a provider and see sqlserver ce in the common data provider list dialog. but no matter whether its there or not, if its working

    by the way, i have to use sqlce 2.0 sdf files.

    q1)  is it possible to access sqlce 2.0 sdf files with sqlce 3.5 provider? (some like backward compatiblity)
    q2) can u send me a link to the right ole db provider installation

    my e-mail is enkaradag@msn.com if someone needs about this topic

    Regards,
    Ender
    Monday, September 01, 2008 10:13 AM
  • Hi Ender,

    I dont have much experience with sql server ce, but if you do not have sqlce 3.5 you have to use an other connection string.
    Take a look at:
    http://www.connectionstrings.com/?carrier=sqlserver2005ce
    Take care on the provider string. The older versions have Microsoft.SQLSERVER.MOBILE.OLEDB ... not Microsoft.SQLSERVER.CE.OLEDB..

    Regards,
    Joachim
    Monday, September 01, 2008 11:58 AM
  • The 3.5 SP1 desktop provider you can install from here:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=dc614aee-7e1c-4881-9c32-3a6ce53384d9&displaylang=en

    The provider can be used from code, will not appear in the standard OLEDB privider GUI lists.

    You can only access SQL CE 2.0 files on a device, not on x86.

     

     

    Monday, September 01, 2008 2:32 PM
    Moderator
  • Ok ive given up accessing sqlce2.0 sdf files from desktop environment. Ive seen some programs that open sqlce2.0 and view its contents but i noticed that all of these programs connect to device, want the user to select a sdf, when user selects, view the contents, so they do access operation on device side, and getting accessed data to the pc.

    so for people googling this topic;

    - sqlce 2.0 oledb
    - sqlce 2.0 ole-db
    - sqlce 2.0 ole db
    - sqlce 2.0 data provider for pc
    - how to access sqlce2.0 from desktop
    - how to access sdf sqlserverce2.0 from desktop
    - how to access SSCE2.0 from desktop

    im working on it for a week and our conclusion is its IMPOSSIBLE..

    Monday, September 01, 2008 8:01 PM
  • ErikEJ said:

    The 3.5 SP1 desktop provider you can install from here:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=dc614aee-7e1c-4881-9c32-3a6ce53384d9&displaylang=en

    The provider can be used from code, will not appear in the standard OLEDB privider GUI lists.

    You can only access SQL CE 2.0 files on a device, not on x86.



    Is there a special reason for not appearing in the standard OLEDB provider GUI lists? Is there a work arround in order to appear to GUI lists? It would help data migration between Office appilcations and Compact SQL databses...


    George J.

    Microsoft MVP (VSTO)
    Friday, January 02, 2009 1:11 PM
  • The OLEDB provider is a port of the OLEDB provider from Windows CE (Mobile), so it is not a fully featured/compliant as a "normal" OLEDB provider. It has problems with fields of type: image and ntext, for example. Therefore (I assume) it is not a visible as other OLEDB providers, and in my experience, does not work well with Office.

    You can expose it with the following reg settings:

    Windows Registry Editor Version 5.00  
     
    [HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{F49C559D-E9E5-467C-8C18-3326AAE4EBCC}\OLE DB Provider]  
    @="SQL CE OleDB Provider"  
     
     

    Some of the tools from www.primeworks-mobile.com may be able to help you, depending on your scenario.

    Which direction do you want to migrate data, from Office (Access?), or from SQL Compact? Do you have a SQL Server (Express) instance involved? Then maybe I have some other possible solutions.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - Please mark as answer, if this was it.
    Friday, January 02, 2009 1:21 PM
    Moderator
  • I want to migrate data from Access Databases to SQL Compact.

    I do not want to use Compact SQL with mobile applications. I want a desktop portable 64-bit database alternative, to the lack of 64-bit support of the OLEDB drivers.


    George J.
    MVP VSTO Athens, Greece
    Thursday, January 08, 2009 12:05 AM
  • Then I think your best bet is the Primeworks tools
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - Please mark as answer, if this was it.
    Thursday, January 08, 2009 2:50 PM
    Moderator
  • Man, tanks for the reg settings Now I can run Sql Server compact in my Delphi Application
    Sunday, August 23, 2009 4:50 AM
  • Hi Erik,

    the registry key almost helped me, but it seems to be for 3.5 and my DB is 4.0.

    Do you also have a key for 4.=

     

    Michael

    Friday, August 05, 2011 8:39 AM
  • I think this does not work with 4.0:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{2006C53A-C915-41EA-BAA9-9EAB3A1FBF97}\OLE DB Provider]
    @="SQL Server Compact 4.0 OLEDB Provider (not supported)"

     


    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    Friday, August 05, 2011 9:09 AM
    Moderator