Linking SQL Server to an Access database

Even if importing an old Access database to SQL Server is pretty easy using available tools, this might not always be the optimal solution. Linking an old Access mdp file to your SQL Server enables you to gradually move old legacy databases and systems to leverage the power and stability of a server database as you can leave the old dependencies untouch while implementing new ones to use the server.

Setting up an Access database as a linked server on a SQL Server requires a number of steps. For those in a hurry and not wanting to read the whole article, this is what you need to do:

1. Install the 64 bit database driver
2. Configure the Access Database Engien provider settings
3. Allow users full access to the temp directory of the SQL Server service account
4. Setup the linked server

First thing to do is to get the Jet database drivers installed on the server. As Access first did not support 64 bit operating the drivers are not available out of the box. Don’t worry, you will not need to restart after installing this.

Then go to Server Objects — Linked Servers — Providers and you should see the newly installed “Microsoft.ACE.OLEDB.120.0” driver. Open it’s properties and check both “Dynamic parameters” and “Allow inprocess”.  

Then complete this  first step by enabling the ‘Ad Hoc Distributed Queries’ setting with the the following command:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO

 Now you will avoid getting errors when trying to browse or access items in the database.

SQL Server needs to use the temp directory of the SQL Server service account for handling the linked server. For things to work and you to avoid any “Unspecified errors” you need to make sure also the account accessing the linked server has access to that same temp directory (the security settings of the link seem to have no affect on this). The quick and dirty solution to this is to give the account(s) or all users access to the temp directory under the application data folder under the account profile. The proper solution is to:

1. create a new temp folder for the service account (or use c:\temp)
2. change the TMP and TEMP environment variables of the service account to point to the new folder — this will require restaring the server for the changes to take effect
3.  setup a group with the accounts that need access to the database to give it full control over the folder.

As a final touch before setting up the actual linked server you might need to take the time to define a SPN for the service account in cases when the database file is not on the same server as the SQL Server.

Finally go to Server Objects — Linked Servers and add a new linked server. Give it any name you like to identify it and select Office Access Database Engine as the driver. The define the connection parameters as follows:n

• Product name can be anything you like, usually the type of the database e.g. ACCESS
• Data source defines the path to the database file
• Provider string can be used to define additional connection string parameters

Make sure to check that the settings on the Security page are has you want to have it. Then click on OK.

Now to make sure the linked server actually works, first open the context menu of the new linked server and select Test Connection. Then browse through the structure down to the tables to see if SQL Server is able to display you the objects in the database. If no errors are displayed the link is now ready and you can go and get your self your well deserved hot beverage.

Don’t go (Entity Framework) code first

Code first (Entity Framework) allows developers to forget about SQL, deploy schemas and have everything under source control. As long as you don’t think about the future all is just fine and dandy. But the life cycle of a solution and especially it’s database is only starting when it is first developed. The new Fluent Migrations framework and SQL Server Data Tools have got me thinking about what in the end would be the tooling that will retain its value also later in the life cycle.

Using code first getting started is easy and the initial deployment is close to zero effort. Unfortunately this is where the sunshine ends and the dark clouds appear. As time goes buy, the production database gets full with masses of business data and other systems start relying on all the views and schema. Some monday morning your boss asks you to to add a couple of columns, remove some depricated ones and maybe do some renaming while your at it. To be able to test your changes you of course start by copying a part of the production data to to the test server. Now then fun starts! You first edit your (code first) entities classes, then fire up SQL Server Management Studio to edit the database and finally give the changes a thorough testing. To finish off you prepare SQL scripts to get the production database updated. As this is all manual work, you find your self thinking that life could be easier – and so it can!

Imagine coding your schema first with Fluent Migrations, then letting the standard Entity Framework to generate your entities based on the database. Now, no mather what changes come you can always do them first by code, then let Visual Studio update the entities and finally deploy you changes without leaving your IDE. All the promises of code first, without code first. Oh happy days!

JDBC: SQL Server and Named Instances

It’s been a while since I worked with Java and especially JDBC. Today I spent  a while trying to get the Oracle BI Publisher to connect to a SQL Server 2005 instance.

I found various examples of different ways to define the instance name in the connection string. After trying them all I could only conclude that defining the instance specific port number is the only way to get a connection established.

jdbc:hyperion:sqlserver://<server>:<port>;DatabaseName=<database>...


Reading the MSDN documentation a bit closer also revealed that it is the recommended way.

How do I know what the instance specific port number is? Easy!

1. Fire Up the SQL Server Configuration Manager
2. Expand the the SQL Server 2005 Network Configuration node
3. Click on the instance you want
4. Double click on the TCP/IP item
5. Switch to the IP Addresses tab

The port number needed is the item named TCP Dynamic Ports.

SQL Server CE 3.5: ISNULL not supported

Working with SQL Server CE 3.5 I noticed that it does not support the ISNULL function. Instead you have to use the COALESCE function to achive the same thing.

The coalesce function can be used much in the same way as the isnull function. The coalesce function returns the first none-null value in the parameter list.

This will give you all the rows where some_id_field is not null and equals the @filtering_id parameter:

SELECT some_value WHERE COALESCE(some_id_field, -1) = @filtering_id;


SQL Server Compact Edition

For so many years I have been forced to work with Microsoft Access and application using it as a local and even shared network data storage, resulting in endless problems with corrupted and bloated database files.

I was very pleased when I found out that Microsoft had finally introduced a good (and free) replacement for the light weight Access databases called SQL Server Compact Edition.  As Access it is a file based database but with many of the features of  it’s big brother SQL Server.

Finally testing the new database I quite quickly got more and more dissapointed, not at the database engine but the tools.  When building on my first compact database using Visual Studio 2008 I almost immediately started getting errors like “The parameter is incorrect” and “The path is not of a legal form” for no particular reason. After googling for help I found out  that the only solution is to restart Visual Studio, every time! Also the useability leaves much to hope for  as now simple stuff like setting up relations and indexes requires switching between numerous dialogs.

Hoping the SQL Server Management Studio would provide better tools I found out the old 2005 version does not support the latest 3.5 CE database. After installing the new 2008 version, I was not surprised to see the same tools and dialogs as in Visual Studio. I guess a visual tool for defining the schema is out of the question.

I’m planning to use SQL Server Compact Edition in my next projects, hoping the engine will prove to compensate for the weak tools. You can be sure to read more about this subject in my later posts!