SQL Compact and MS-Access

So I saw SQL Compact 4.0 was released and I was excited for a whole hour. A data source is as useful as the tools that can connect to it. Just about everything can connect to an MS-Access database or to a SQL Server database, via Oledb, ODBC,  MS-Access linked tables, you name it.  You can even get MS-Access to connect to an XML file or a HTML table.  But HTML isn’t a real file based database and MS-Access’s JET-SQL is just different enough from TSQL to be annoying. So it would be nice to be able to have MS-Access use SQL  Compact as a datasource, esp as a linked table and not as an ADO datasource in VBA scripts.

But as far as I can tell, this just isn’t possible to do *without writing code*. MS-Access used ODBC to talk to unfamiliar datasources and AFAIK, there isn’t an ODBC driver to SQL Compact.  Googling turned up MSDASQL, but that solves the converse problem of getting an Oledb consumer to talk to ODBC. And MS-Access is an ODBC consumer.

So that leaves either importing all my data from SQL Compact into MS-Access (not too exciting) or writing VBA code, which might enable some scenarios.  This is one possibility I haven’t completely explored- MS-Access can bind to ADO recordsets, maybe including SQL Compact. It’s a possibility.

http://support.microsoft.com/kb/281998

Troubleshooting “Service ‘Astoria’ implements multiple ServiceContract types, and no endpoints are defined in the configuration file.”

This is a misleading error that means you forgot to put the connection string into web.config.

So if you get:


WebHost failed to process a request.
Sender Information: System.ServiceModel.Activation.HostedHttpRequestAsyncResult/63432468
Exception: System.ServiceModel.ServiceActivationException: The service '/Services/Astoria.svc' cannot be activated due to an exception during compilation. The exception message is: Service 'Astoria' implements multiple ServiceContract types, and no endpoints are defined in the configuration file. WebServiceHost can set up default endpoints, but only if the service implements only a single ServiceContract. Either change the service to only implement a single ServiceContract, or else define endpoints for the service explicitly in the configuration file.. ---> System.InvalidOperationException: Service 'Astoria' implements multiple ServiceContract types, and no endpoints are defined in the configuration file. WebServiceHost can set up default endpoints, but only if the service implements only a single ServiceContract. Either change the service to only implement a single ServiceContract, or else define endpoints for the service explicitly in the configuration file.
at System.ServiceModel.Web.WebServiceHost.AddAutomaticWebHttpBindingEndpoints(ServiceHost host, IDictionary`2 implementedContracts, String multipleContractsErrorMessage)
at System.ServiceModel.Web.WebServiceHost.OnOpening()
at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.ActivateService(String normalizedVirtualPath)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
--- End of inner exception stack trace ---
at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Activation.HostedHttpRequestAsyncResult.End(IAsyncResult result)
Process Name: WebDev.WebServer
Process ID: 8168

This is what a connection string for an entity data model looks like. If you created your edmx file in a different project from your .svc file, you will need to manually copy the connection string. In this case I’m using Sqlite (which can’t be design using Visual Studio Express, in case you were wondering)


<connectionStrings>
<add name="calendarEm" connectionString="metadata=res://*/CalendarV1.csdl|res://*/CalendarV1.ssdl|res://*/CalendarV1.msl;provider=System.Data.SQLite;provider connection string="data source=C:\code\CommunityCalendar\Calendar\App_Data\calendar.db3;useutf16encoding=True"" providerName="System.Data.EntityClient" />
</connectionStrings>

The given columnMapping does not match up with any column in the source or destination

The SqlBulkCopy object is good, but probably super twitchy. The fact that it is case sensitive to columnames to me is a sign of likely low code quality, and the error that gives this blog entry its title is another.

Sometimes bulk copy will import a table from text without explicitly setting the columns. Sometimes you have to explicitly set the columns and poof! it works. It may have to do with white space or case sensitivity in the columns, or it may have to do with drivers making inaccurate counts of fields.

SqlBulkCopy bcp = new SqlBulkCopy(
“Server=.;persist security info=True;initial catalog=target;Integrated Security=SSPI”,
SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls);

bcp.BatchSize = 50000;
bcp.ColumnMappings.Clear();

for (int i=0;i {
string name = reader.GetName(i).Trim().ToLower();
bcp.ColumnMappings.Add(name,name);
}

for (int i = 0; i < bcp.ColumnMappings.Count-1; i++)
{
System.Diagnostics.Debug.WriteLine(bcp.ColumnMappings[i].SourceColumn);
}
bcp.DestinationTableName = file.Name.Split(‘.’)[0];

bcp.WriteToServer(reader);

bcp.Close();

NOTE: Even explicitly listing the columns often is not enough. I eventually gave up and used alternate techniques. The command is poorly documented with respect to what text formats are acceptable for BIT, DATETIME, IMAGE when doing heterogeneous bulkcopy. Until MSDN documents it, this otherwise fantastic tool is only reliable for copying from SQL to SQL with identical schemas and probably identical version and patch levels.