Accessing SQL Compact from SQL Server "Linked Server"

SQL Compact users would like to have access to SQL Compact data (read only) from within SQL Server for various reasons: Import the data to a SQL Server table, use the data in Reporting Services, join SQL Compact data with SQL Server data etc. I have made a sample which I will describe in this blog post, that demonstrates two types of access: Using a SQLCLR table-valued function (UDF) and a SQLCLR stored procedure.

Due to security restrictions of SQLCLR, the solution uses the OLEDB provider and not the ADO.NET provider. This is the cause for the following limitations of the sample: "ntext" and "image" columns are not supported, as data in those BLOB type fields cannot be retrieved from the OLDEB provider.

The sample consists of 2 main methods, the one for the table valued UDF is: GetDataFromSqlCe.

        [SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read)]
        public static IEnumerable GetDataFromSqlCe(string dataSource)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" + dataSource);
            conn.Open();
            using (OleDbCommand cmd = new OleDbCommand("SELECT [Category Id], [Category Name] FROM Categories", conn))
            {
                // Ensure that the connection is closed when the reader is closed
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
 

The sample method uses a SQL statement, which output must match the parameters of the FillRow method, meaning you will have to code a function for each table you would like to expose.

 private static void FillRow(Object obj,
                        out SqlInt32 catId,
                        out SqlString catName
                        )
        {
            DbDataRecord row = (DbDataRecord)obj;
            int column = 0;
            catId = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
            catName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
        }
 

The advantage of using the UDF approach is that you can use the output from the function as if it was an ordinary SQL Server table, since the output is "strongly typed", so to speak.

The second approach GetTable returns a SQL result set, and works for any table in the sdf file (minus ntext and image fields).

public static void GetTable(String connectionString, String tableName)
 

Based on metadata from INFORMATION_SCHEMA.TABLES a SqlMetaData array is constructed and used for defining the SqlRecord to be returned. The records are then dynamically built and returned.

To test the sample, do as follows:

1. Download the zip file below with the source code for the SQLCLR dll file.

2. Open and compile in Visual Studio 2008

3. Use the indcluded install.sql script to configure, install and test the SQLCLR dll on your SQL Server instance.

Hope you will find this useful...