Server and column collation conflicts in properties of a login

Problem:

Error: Cannot resolve the collation conflict between “Danish_Norwegian_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the UNION operation. (Microsoft SQL Server, Error: 468) when clicking “Securables” in properties of a server login.

Setup:

Server is set up with collation: SQL_Latin1_General_CP1_CI_AS
The database, or more precisely, the columns are created with collation: Danish_Norwegian_CI_AS
Login is set up with default language: English

The following steps will reproduce the error:

1. See properties for the server-login:

2. Click “User Mapping”

3. Click “Securables”

4. Observe error:

Cannot resolve the collation conflict between “Danish_Norwegian_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the UNION operation. (Microsoft SQL Server, Error: 468)

Solution/Impact:

Can anyone say anything intelligent about this issue?

Reporting Services issue when installing local Domain Controller

Problem:
At a client we had to produce a server, in order for us to have a development environment, since they couldn’t spare the server power. Odd situation, but that’s not the point. The point is that, somewhere during the installation process of this server, after installing SQL Server, we noticed that the server wasn’t a domain controller. Now the SQL Server and in particullar Reporting Services (SSRS) was installed with credentials granted to the build-in Network Services account. This account gets a new SID once we installed the domain controller, and after that SSRS started acting on us.

Solution:
The new Network Services account needs to be granted modify permissions to the following folders:

  • C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles
  • C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\RS\TempFiles

SQL Server Data Tools being really anoying

Before today I have only been fiddling about in with new SQL Server Data Tools Integration Services projects, without any problems what so ever. I’ve seen demos done without any problems what so ever.
And now that I finally get to grind some data with the new tool, it rears its ugly head, for just about every five minutes. I can’t seem to do any, even simple, tasks without Visual Studio hanging/locking up on me. Most of the times I need to kill the process and re-open the project. I do, occasionally, get the kind popup message that Visual Studio is busy. DUH!
VS Busy
It’s not like I’m doing any fancy stuff, at all… Observe CPU load as I do a rename of a sequence container:
CPU Load

Sometimes I’m lucky and just get hit by non critical errors like this one (deleting an ADO destination):

In my previous experience with SSIS, I’ve often been able to go ’round problems like these by deleting the, in this case an Excel Source, component and the add the component again. While trying to do so, I deleted the flow between the Excel Source and the Derived Column task. This simple procedure took several hours, and resulted in this message:

I really hope Microsoft deals with this, and soon… Five breakdowns within the hour is not very productive.
Vote at MS Connect: https://connect.microsoft.com/SQLServer/feedback/details/743111/ssis-data-tools-locking-up-hang

Update:
In fact there was a Cumulative Update 1 at the time of writing, but there doesn’t seem to be a fix intended for this issue. The update was brought to my attention by Bill Fellows (blog|twitter) as I was ranting about it on twitter using the #SSISHELP hashtag.

Rolling period with shell dimension SSAS

Often I meet the business requirement of listing data for the last X months. In this blog post, I will try to give my shot at, how this is done elegantly and with maximum flexibility.

We start of backwards and enhance the Data Source View in the cube with a custom Named Calculation called UtilityKey (or whatever you like). The query is probably the most simple Named Calculation you’ll ever write, since it should be looking awfully a lot like the next figure:

When this Named Calculation is ready to go, we need to make use of the fact that having a dimension with the key = 0, means we have a hold of every row in that fact table! Enter shell dimension.
We create a table in SQL Server that holds the dimension we want to be able to slice by, using the utility key; In this case: A periodicity dimension. A dimension that hold the members by which we want to slice data. The table in this example looks as follows:

We then populate with data that matches our business requirements, in this case we need the members All, 1 Month, 3 Months, 6 Months and 12 Months:

This dimension table, we then use to create a dimension in the cube, based on the key and name columns.

Ending up with a result, after trimming the names, setting IsAggregatable = False etc:

The clever part of this shell dimension is that we then define a SCOPE in the cube that allows us to slice the data as we see fit. We use the regular time dimension present in the cube to navigate through the five (5) different members of our Periodicity dimension, like this:

Slicing the cube by Periodicity and not by the regular Calendar dimension yields the desired result to the business requirement.

Parameter ordering in SSRS – Gotcha!

After banging my head against the screen for about three or four hours, trying to solve what seemed as a Reporting Services mystery, a coworker of mine came up with the solution. I was trying to inject a role into an embedded data source in a report, while loading data for several parameters, in order to utilize FORMS authentication on the website while respecting the Windows Authentication needed for Analysis Services.
I had one report going, 11 more to go, and then I got stuck on a data source string yada yada error. In Report Manager it looks like this:
The reason for this error, as it turns out, is that it would appear as if Reporting Services parses parameters one by one. In that way the first parameter in the list gets loaded by accessing the data source, and fails miserably if a parameter is used in the data source, that is not yet loaded/parsed/whatever…
Although Microsoft say they have several connect issue regarding this rather unfortunate issue, I can only find one: MS Connect