Category Archives: SQL Server

Once something takes more than an hour to figure out, it is always a good candidate for a new blog post. This one definitely took longer than an hour.

I am supporting a data mart with compensation data for an international company. There is a currency dimension table that gets populated from a view in an operational database. The currency key is an IDENTITY column and gets populated with sequential values, starting at 1 with an increment of 1. Continue reading →

The Business Intelligence Showcase demonstrates our expertise with Microsoft’s business intelligence application suite. These technologies include the SQL Server database, Analysis Services, and Reporting Services. Recently added to the stack was the .Net chart control, a very powerful component that greatly improves productivity for producing visually appealing charts and diagrams on web sites and Windows Forms applications.
The initial release of the showcase application can be viewed at the following location:

A SSAS cube with data from the National Highway Traffic Safety Administration’s FARS (Fatal Accident Reporting System) for the year 2007
The demo is a simple 2-dimensional representation of the cube for web presentation, and the dimensions can be selected from a list and transposed

A .Net Charts application with data for a subset of the (controversial) HEDIS measures for California from 2007These measures are published annually by the Pacific Business Health Group, a health insurance purchasing group on the West Coast.

This article is no longer relevant. In Visual Studio 2008 SP1, setup projects allow you to select the ‘User’s Application Data Folder’ as a deployment target. This is where any SDF files and other application data and content should go. The ‘Common Application Data Folder’ is no longer accessible for write operations in Windows 7 and should not be used.

The application should use System.Environment.SpecialFolder.ApplicationData to obtain the physical path to this folder. The part about the Data Source string below remains valid, however.

It is no longer an acceptable practice to deploy data files to the same location as an application’s executable. Vista’s more strict security model by default assumes read-only access for the files in the Program Files folder. Unfortunately, even the folks at Microsoft seemed to struggle with this change for a while, because the deployment of project files to the Application Data folders is not implemented very well in Visual Studio (2008). I found quite a few instances in various forums on the Web where people stumbled over this issue, but not a single instance of a good answer. I hope this is one here.

The setup project gives the user the option to install the app “for the current user only” or “for all users”. Consequently, the database file would end up in either the current user’s or the All Users application data folder. The setup would have to write this information somewhere so that the application can later retrieve it, when it comes to accessing the database. How else would it know which application data folder to look in?

To avoid this issue, I just wanted to install the database in the All Users/Application Data folder, regardless of whether the application was installed for one user or for all users. I realize, of course, that two users could not install the application on the same computer without overwriting each other’s data. This is such a remote possibility, though, that I don’t want to consider it.

This turned out to be not so easy, and I figured it would be worth-while posting the solution here (in addition to the my answer on Stackoverflow.com). The first piece of the puzzle I got here:

Form_Load(object sender,EventArgs e){// Set the db directory to the common app data folderAppDomain.CurrentDomain.SetData("DataDirectory",System.Environment.GetFolderPath(System.Environment.SpecialFolder.CommonApplicationData));}

The DataDirectory property in the AppDomain will be used to expand a place holder in the connection string. The CommonApplicationData special folder points to the holder that the database was installed to.

Now we need to make sure that the data source contains the DataDirectory placeholder. This piece came from here. In the DataSet designer, find the DataSet’s properties, open the Connection node and edit the ConnectionString property to look as follows:

DataSource=|DataDirectory|\YourDatabase.sdf

Then I followed Lyman Enders Knowles’ instructions (from his own answer to the question on Stackoverflow) for how to add the Common Application Data Folder to the setup project, and I placed the database file in that folder.

I then followed Ove’s suggestion from Stackoverflow, i.e. I checked the “Enable ClickOnce Security Settings” and selected “This is a full trust application” in the Visual Studio project settings.

After that, the application deployed fine on Vista and the database file was accessible for both reads and writes.

6/07/2009 Update Version 1.2 for Android is available. Search for TripsNMiles on Android Market from your device.

7/29/2009 Update Version 1.2 is available via the download link below and from Cnet. The forms use docking and work on all form factors and resolutions. Windows Mobile 6.1 was tested as well and works fine.

Sometimes we need to reduce the granularity of date ranges by combining serveral contiguous rows into one. While this is a common classic SQL problem, I was unable to find an elegant solution that also performs well, and came up with my own. This article explains the problem and outlines the solution using a Common Table Expression (CTE) with recursion in SQL Server 2005. Continue reading →

When you have a query that does complex calculations, maybe even has function calls in the select list

select
costly_function_A() as result_A,
costly_function_B() as result_B
from
table_T

and you want to return the result of the calculation or function call, but also manipulate it further and return the result of this operation as well,

select
costly_function_A() as result_A,
costly_function_B() as result_B
result_A/result_B as result_ratio
from
table_T

then you face the following dilemma:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'result_A'.

It is not allowed to reference named colums of a result set in the same query. The trivial workaround is to do the calculation or function call twice in the query:

select
costly_function_A() as result_A,
costly_function_B() as result_B
costly_function_A()/costly_function_B() as result_ratio
from
table_T

This would work but is not optimal because it nearly doubles the execution time (the same functions are evaluated twice). So what does SQL Server offer in terms of query-writing tricks that let us get around this issue?

There are 2 choices – neither is particularly elegant.

First, we can put the costly stuff into a derived table where it gets executed only once, and do the secondary calculation in an outer query:

select
costly_T.result_A,
costly_T.result_B
costly_T.result_A/costly_T.result_B as result_ratio
from
(
select
costly_function_A() as result_A,
costly_function_B() as result_B
from
table_T
)
as costly_T

Or we can use a view. If you do not already use views, don’t introduce one, but go with the derived table. If you do have views, you may already have the necessary layer of abstraction that is needed here.