How to Export Data from SQL server Compact To Access MDB

Question

I need a solution to transfer all data from SQL Server CE to Access mdb database.

I tried this approach http://www.codeproject.com/Answers/483989/HowplustoplusExportplusSQLplusTablesplusToplusAcce#answer2 (solution # 2) but getting an error "No database specified in connection string or IN clause."

The code works if I connect to non-compact SQL server.

I guess the problem is with connection string in IN clause but I cannot figure out how to change it.

Here is my code:

private void ExportTable(string tableName, string source, string destination)
{
    var connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", destination);
    var cmdText = string.Format("SELECT * INTO {0} FROM [{0}] IN ''[Data Source={1};Max Database Size='4000';Persist Security Info=False;];", tableName, source);

    using (var conn = new OleDbConnection(connStr))
    {
            conn.Open();
            using (var cmd = new OleDbCommand(cmdText, conn))
            {
                cmd.ExecuteNonQuery(); // error on this line
            }

            conn.Close();
    }
}

The connection string: Data Source={1};Max Database Size='4000';Persist Security Info=False; working ok when I connect to the database directly.

UPDATE: Apparently the format of the source DB in IN Clause should be as following:
[type; DATABASE = path]

(see: http://answers.microsoft.com/en-us/office/forum/office_2010-access/access-2010-runtime-error-3170-could-not-find/0b085797-618a-488f-b1b4-30af00f04b3f)

When I use

var cmdText = string.Format("SELECT * INTO {0} FROM [{0}] IN ''[SqlServer CE; DATABASE={1}];", tableName, source);

I am getting different error: Could not find installable ISAM.

Do you know correct type for SQLServer CE? Is it supported at all? I could not find any info about it.

I have also tried: SQL CE, SQLSERVER.CE, Microsoft.SQLSERVER.CE.OLEDB.3.5, Microsoft.SQLSERVER.MOBILE.OLEDB.3.0 etc. - Same error...

Answer 1

I think the stumbling block here is that the trick you are trying to use requires an ODBC connection to the SQL Server and as far as I know there is no ODBC driver for SQL Server Compact. I'm pretty sure that the syntax [ODBC;Driver=...] in Access has no OLEDB equivalent, so the trick won't work with SQL Server Compact. (As you discovered, it does work with "real" SQL Server because ODBC connections are supported for that platform.)

I was curious to see what I could accomplish in C# using an OLEDB connection to the SQL Server Compact database (which is supported, as @MrZak pointed out in his comment). I came up with the following. It pulls the SQL table into a DataTable, sets the status of each row to "Added", and then updates (inserts into) the corresponding table in Access.

string myConnectionStringMDB =
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data Source=C:\Users\Gord\Desktop\fromCE.mdb;";
string myConnectionStringSQL =
        "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" +
        @"Data Source=C:\Users\Public\test\myData.sdf;";

using (OleDbConnection conSQL = new OleDbConnection(),
        conMDB = new OleDbConnection())
{
    conSQL.ConnectionString = myConnectionStringSQL;
    conSQL.Open();
    conMDB.ConnectionString = myConnectionStringMDB;
    conMDB.Open();

    using (OleDbCommand cmdSQL = new OleDbCommand(),
            cmdMDB = new OleDbCommand())
    {
        cmdSQL.CommandType = System.Data.CommandType.Text;
        cmdSQL.Connection = conSQL;
        cmdSQL.CommandText = "SELECT * FROM [Table1]";

        var daSQL = new System.Data.OleDb.OleDbDataAdapter(cmdSQL);
        var dt = new System.Data.DataTable();
        daSQL.Fill(dt);

        foreach (System.Data.DataRow dr in dt.Rows)
        {
            // change row status from "Unchanged" to "Added" so .Update below will insert them
            dr.SetAdded();
        }

        cmdMDB.CommandType = System.Data.CommandType.Text;
        cmdMDB.Connection = conMDB;
        cmdMDB.CommandText = "SELECT * FROM [Table1]";
        var daMDB = new System.Data.OleDb.OleDbDataAdapter(cmdMDB);
        var cbuilderMDB = new OleDbCommandBuilder(daMDB);
        cbuilderMDB.QuotePrefix = "[";
        cbuilderMDB.QuoteSuffix = "]";
        daMDB.Update(dt);
    }
    conSQL.Close();
    conMDB.Close();
}