Thursday, October 23, 2008

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...

















9 comments:

Unknown said...

can it also work in visual studio 2010??

ErikEJ said...

Not sure what you mean, it is a SQLCLR feature/implementation

Ryan J Gillies said...

Great idea, but unfortunately I'm looking to use a solution like this for SQL CE 4.0 - last I checked there isn't an OLEDB provider for this version, is that correct?

Has there been any update since you wrote this in 2008 that might allow it to work with 4.0 and ADO.NET?

Ryan J Gillies said...

I took a punt and simply changed Microsoft.SQLSERVER.CE.OLEDB.3.5 to Microsoft.SQLSERVER.CE.OLEDB.4.0 and whilst GetCategories fails, GetTable works and gives me what I want!

However I'm struggling to be able to do anything with the result set it returns - I can't get the result set into any kind of table, temp table or table variable in order to work with it. I can only see the result set itself in SQL Server - any thoughts? Thanks!

ErikEJ said...

Can you use SELECT INTO? How does GetCategories fail?

Ryan J Gillies said...

GetCategories fails as follows:

Msg 6522, Level 16, State 1, Line 53
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetCategories":
System.Data.OleDb.OleDbException: The path is not valid. Check the directory for the database. [,,,Path,,]
System.Data.OleDb.OleDbException:
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at ErikEJ.SqlCe.ClrAccess.GetDataFromSqlCe(String dataSource)
.


And when I try to do anything with GetTable, such as the following:

CREATE TABLE #tmp (Id int)
INSERT INTO #tmp
EXEC dbo.GetTable 'Data Source=D:\test.sdf', 'test'


I get the following:

Msg 6549, Level 16, State 1, Procedure GetTable, Line 6
A .NET Framework error occurred during execution of user defined routine or aggregate 'GetTable':
System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.Transactions.TransactionPromotionException: MSDTC on server 'GENSHEN-PC\SQLEXPRESS' is unavailable. ---> System.Data.SqlClient.SqlException: MSDTC on server 'GENSHEN-PC\SQLEXPRESS' is unavailable.
System.Data.SqlClient.SqlException:
at System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
at System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
System.Transactions.TransactionPromotionException:
at System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
System.Transactions.TransactionAbortedException:
at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx)
at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
at System.Transactions.Transaction.Promote()
at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
at System.Transactions.TransactionInterop.GetDtcTransaction(Transaction transaction)
at System.Data.Common.ADP.GetOletxTransaction(Transaction transaction)
at System.Data.OleDb.OleDbConnectionInternal.EnlistTransactionInternal(Transaction transaction)
at System.Data.OleDb.OleDbConnection.Open()
at ErikEJ.SqlCe.ClrAccess.GetTable(String connectionString, String tableName)
. User transaction, if any, will be rolled back.


It looks like an issue with something on my server, but I'm at a loss as to what the cause may be.

ErikEJ said...

Ryan: Suggest we continue the conversation by email! Please feel free to mail me, I would like to see this Work with SQL CE 4.0 as well!

Unknown said...

Hi Eric, it was very good way to extract data from .sdf file. I have same issue that Ryan had regarding to extract those info in temp sql table. Did you get any idea about the same how to resolve it?

Regards

Vraj

ErikEJ said...

I have some code updates pending, that resolves the issue...
I will either update the blog post or make a new one!