C# supporting different versions of SQL Server with same application

I had to write a piece of code that would work for both SQL Server 2008 R2 and SQL Server 2014

There were several issues - first of them is how to include two versions of the same assembly (Microsoft.SQLServer.ManagedDTS v.10 and v.12) in the same project.

This was solved with extern alias after following instructions in this article: http://kentb.blogspot.ca/2008/11/visual-studio-referencing-same-assembly.html

Then there was a problem of ILMerge.exe which stopped working with extern alias complaining that "Unresolved assembly reference not allowed". I had to ditch ILMerge and use an alternative method described here - http://blogs.msdn.com/b/microsoft_press/archive/2010/02/03/jeffrey-richter-excerpt-2-from-clr-via-c-third-edition.aspx

Then I had to use reflection to maintain same code for both versions. All compiled and worked well until I tried the application on a server which really had a single version of SQL Server - 2008 R2.

I started getting the very obscure "Object reference not set to instance of an object" exception just before entering the function that used reflection.

 As it turns out you have to separate the code that uses a possibly missing library in a separate function and then all will work as it is supposed to.

Here is the code:


using dtr120 = DTS120::Microsoft.SqlServer.Dts.Runtime; 
using dtr100 = DTS100::Microsoft.SqlServer.Dts.Runtime; 

//DTS120 and DTS100 are extern aliases of two different versions of the library 

//generic method
LoadPackage<T>(string sDTSPatch)
{....} 

this doesn't work:

if (is120) //if 2014
   bFlag = LoadPackage<dtr120.Application>(sDTSPatch);
else if (is100)  //if 2008 R2
   bFlag = LoadPackage<dtr100.Application>(sDTSPatch);

but this works:

protected bool Load120(string sDTSPatch)
{
  return LoadPackage<dtr120.Application>(sDTSPatch);
}

protected bool Load100(string sDTSPatch)
{
  return LoadPackage<dtr100.Application>(sDTSPatch);
}

....

if (is120) //if 2014  
    bFlag = Load120(sDTSPatch);
else if (is100) //if 2008 R2
    bFlag = Load100(sDTSPatch);


Update:
I forgot to mention that you would have to automate refreshing of .dll files into the root project directory. EmbeddedResourse dll files work only if they are in the root of the project directory

I did it by writing a Pre-build event

if $(ConfigurationName) == Release (
echo f | xcopy /F /Q /Y $(ProjectDir)ProjectX\bin\Release\ProjectX.dll $(ProjectDir)ProjectX.dll
echo f | xcopy /F /Q /Y $(ProjectDir)ProjectY\bin\Release\ProjectY.dll $(ProjectDir)ProjectY.dll)

if $(ConfigurationName) == Debug (
echo f | xcopy /F /Q /Y $(ProjectDir)ProjectX\bin\Debug\ProjectX.dll $(ProjectDir)ProjectX.dll
echo f | xcopy /F /Q /Y $(ProjectDir)ProjectY\bin\Debug\ProjectY.dll $(ProjectDir)ProjectY.dll)

Ever wanted to use pretty Calendar Controls of Report Builder 2.0 in reports based on Analysis Services?

How to use SSRS Calendar Controls for Time dimensions in the reports based on SSAS (Analysis Services) in Report Builder 2.0:



1. Start creating the SSRS report in the Report Builder 2.0 with a Wizard.

2. Select a Time dimension and drop it in the filter area, select hierarchy, use Range (Inclusive) and check both parameters checkboxes, add measures and other dimensions to the report. Close the Query Designer.




3. Go to the parameters of the report (they are automatically created) and modify "From" and "To" Time parameters - change their Data type to Date/Time and set Available Values to None





4. Open the DataSet Properties and press fx button to the right of Query





5. Remove "CONSTRAINED" from "STRTOMEMBER" function that is using Date parameters in the MDX query



6. Go to parameters tab of the DataSet Properties window and enter the expression as a parameter value. Expression has to generate a Uniquename of the Time dimension

In my example unique name is [Date].[Date].[Day].&[20111216]

To generate this string you will need following expression:
="[Date].[Date].[Day].&[" + Format(Convert.ToDateTime(Parameters!FromDate.Value), "yyyyMMdd") + "]"



Repeat this for the second parameter.

GO statement breaks SSIS "Execute SQL Task"

I have lost an hour today trying to find a bug in my SSIS package, and it was so small - just two letters - "GO"

I had an Execute SQL Task with an input parameter and I had an SQLStatement with some lengthy logc and a "GO" at the end.

Every time I tried to execute it I got an error message "[Execute SQL Task] Error: Executing the query "
" failed with the following error: "The variable name '@StartFrom' has already been declared. Variable names must be unique within a query batch or stored procedure."

I got desperate trying to find where am I declaring the @StartFrom. I even removed it completely from script and still got this error. Eventually, gloomily poring over my script I saw a "GO" statement and decided that maybe he is the culprit - causing Execute SQL Task to declare the Input Parameter twice, and I was right. That is exactly what happens.

When Execute SQL Task sees GO, it redeclares the variable specified in the Parameter Mapping, and then himself not liking it complains about it being declared twice. Isn't that just ingenious?

NOT IN or IN ?

I have been having a hard time believing my eyes when I noticed that these queries give different results:

1. select * from TableA a where a.a_id not in (select b.a_id from TableB b)

2. select * from TableA a where not exists (select b.a_id from TableB b where a.a_id=b.a_id )

3. select a.a_id, b.a_id from TableA a
left join TableB b
on a.a_id = b.a_id

where b.a_id is null


1. returns nothing whereas 2. and 3. return the same result set

Isn't it that in theory 1., 2. and 3. must give exactly same result?

Why is that 1. is giving different result than 2. and 3.?

As it turns out TableB contained some entries with NULLs in a_id column, so when NOT IN was evaluated, the presence of NULL in the list of returned values broke the logic, and statement was incorrectly interpreted

Googling gave me the reference to a similar post: http://sqlwithmanoj.wordpress.com/2011/02/15/not-in-not-exists-joins-with-null-values/

oh, devious NOT IN, how art thou misleading...

T-SQL formula for %

I have needed a formula that will get me a number if it is <=100 and 100 for anything greater than 100, because clients required that % cannot be greater than 100%

I wanted a formula without IF or CASE statements.

And, here it is!

DECLARE @rr FLOAT

SET @@rr = 120

@rr - 0.5*(ABS(@rr - 100) + (@rr - 100))

PerformancePoint 2010 - connecting Filter to Analytic Chart from different Data Source

I am working on a PerformancePoint 2010 Dashboard which contains filters and Analytic Charts from different Data Sources. I wanted to share one of the challenges and my solution to it, and if possible to discuss any other alternative solutions.

My challenge was to connect an Analytic Chart from one Data Source (OLAP Database A) to a "Period" filter from another (OLAP Database B). I could not use Time Intelligence Filter to solve this, because the calendar control and Time Intelligence functions (Current Month, Previous Month) were not giving us what was needed - we needed just to display a list of Period names in the filter.

To implement this funtionality, Period dimension members had to be translated into Month hierarchy members of a Time dimension when connecting to a Chart by using string manipulation in the MDX query of the Analytic Chart.

Here are the steps to make it work:

1. Drop Period filter and Analytic Chart to the Dashboard

2. In Analytic Chart, create Parameter <<Period>>

3. Create Connection between Filter and Chart with Member Unique Name as a Source Value:





4. Specify <<UniqueName>> in Connection Formula



5. In Analytic Chart MDX Query create a Calculated Member FilterDate

WITH MEMBER FilterDate
AS "[Time].[Date].[Month]." + vba!Right("<<Period>>", vba!Len("<<Period>>") - vba!InStr("<<Period>>",".&["))

this will remove the dimension and hierarchy name sent from Period filter and append the rest to the dimension and hierarchy name of the Time dimension of the cube that is used in the chart

5. in the WHERE condition, put WHERE StrToMember(FilterDate)

This solution will work only for single-selection filter because it replaces the dimension name only once. I only wish REPLACE funciton was available in MDX, then we would be able to implement multiple-selection solution as well.

There is another condition in the cube design to make this work - Dimension members of Periods and Month hierarchies must have same keys, ex: March 2010 has a key "201003", and it must mean the same thing in both dimensions - Period and Time.

My special thanks to Chris Webb for the link to VBA functions in MDX - http://cwebbbi.wordpress.com/2007/03/03/vba-functions-in-mdx

.

PARTITION BY clause works incorrectly when in nested select over large volumes of data

I have been witnessing a very strange behaviour of ROW_NUMBER() PARTITION BY clause in complex join and nested select or CTE statements over large volumes of data with Max Degree of Parallelism <> 1. The ROW_NUMBER() or RANK() were incorrectly marking records within a partiton, and that behaviour was nondeterministic.

Let's say I have three tables girls, shoppingmall and times

girls and shoppingmall have from and to dates, and times table has a row for every date. shoppingmall has shopid which can appear in different mallid.

ex:


Now, I want to get first mallid where the girl was shopping for every day.

I join girls table with times by from and to. Then I create a nested select from shoppingmall also joined to times by from and to, which would use ROW_NUMBER () PARTITION BY in order to give me first mallid for a given date and given shop.


select * into #resultset from girls
left join times ON times.timeid >= girls.datefrom and times.timeid < girls.dateto
left join
(select mallid, shopid as shopidx, times.timeid as timeidx, ROW_NUMBER() OVER (PARTITION BY shopid, times.timeid ORDER BY ordering ASC) Lenin
FROM shoppingmall
INNER JOIN times ON times.timeid between shoppingmall.datefrom and shoppingmall.dateto
) AS trip ON girls.shopid = trip.shopidx AND times.timeid = trip.timeidx and Lenin = 1

Now, what I should get is only one row per date, per shop from shoppingmall

However, if I run this query on a very large dataset ROW_NUMBER() occasionally marks as 1 two rows for same shopid and timeid!

As soon as I add a very restrictive WHERE condition which limits the dataset, ex: WHERE girls.girlid = "Kathy", the query starts behaving correctly (whereas without strict WHERE and on the same record it was marking two rows PARTITIONED BY shopid and timeid as 1)

Also, if I fist insert result of inner select in the temporary table, and then use it in the join - results are correct.


I am running on SQL Server 2008 10.0.2531.0

I will rewrite all my inner selects to use temporary tables instead of nested selects and CTE, but still I do not like the unexpected and unexplainable behaviour of PARTITION BY. I guess that this behaviour is due to parallelism because when I changed the Max Degree of Paralellism to 1 this behaviour disappeared and PARTITION BY started working correctly?