MEF 1 - ImportMany generic interfaces

This is just an example how to import many implementations of a generic interface with MEF1.
Also you can see how to resolve a class from MEF1 container.

SQL - How to join two tables but return only matched rows or all rows if other table is empty

One of the common SQL tasks is to return matched data only or all data if other table is empty. In other way it should work like INNER JOIN when both table have data or LEFT JOIN when one table is empty. Real world scenario could be like this:
- Return all properties if filter table is empty. If filter table has any data then return properties that matches to filter.

This is how we can create such query:

CREATE TABLE MyData(Id INT, Something VARCHAR(10), OwnerId INT);
CREATE TABLE OwnerFilter(OwnerId INT);

INSERT INTO MyData VALUES(1, 'AAAA', 1);
INSERT INTO MyData VALUES(2, 'BBBB', 1);
INSERT INTO MyData VALUES(3, 'CCCC', 1);
INSERT INTO MyData VALUES(4, 'AASS', 2);

-- Other table is empty. Query returns all rows.
SELECT * 
FROM 
(SELECT NULL AS Gr) AS Dummy
LEFT JOIN OwnerFilter F ON (1 = 1)
JOIN MyData D ON (F.OwnerId IS NULL OR D.OwnerId = F.OwnerId);

-- Other table has data. Query returns only matched rows.
INSERT INTO OwnerFilter VALUES(2);

SELECT * 
FROM 
(SELECT NULL AS Gr) AS Dummy
LEFT JOIN OwnerFilter F ON (1 = 1)
JOIN MyData D ON (F.OwnerId IS NULL OR D.OwnerId = F.OwnerId);

Here you can try it on fiddler: http://sqlfiddle.com/#!6/0f9d9/7

Version store out of memory when building SSDT project with msbuild.exe

I use Bamboo to build my SSDT database projects. It is random that sometimes build fails with message:

error MSB4018: Microsoft.Isam.Esent.Interop.EsentVersionStoreOutOfMemoryException: Version store out of memory (cleanup already attempted)

Full stack trace is like this:

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(550,5): 
error MSB4018: The "SqlBuildTask" task failed unexpectedly.
error MSB4018: Microsoft.Isam.Esent.Interop.EsentVersionStoreOutOfMemoryException: Version store out of memory (cleanup already attempted)
error MSB4018:    at Microsoft.Isam.Esent.Interop.Update.Save(Byte[] bookmark, Int32 bookmarkSize, Int32& actualBookmarkSize)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.ModelStore.EseResultSet.Update(Action action)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.ModelStore.RelationshipEntry.set_Element(IModelElement value)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.ReferenceLinker.ResolveInternalElementReferences()
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.LoadExternals(TextReader input, String fileName, String logicalSourceName, IList`1 externalPartsSubstitution, Boolean suppressErrorsForMissingDependencies)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.AddReference(CustomSchemaData customData)
error MSB4018:    at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.OnCustomDataAdded(CustomSchemaData customData)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.AddCustomData(CustomSchemaData customData, Boolean raiseEvents)
error MSB4018:    at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlModelBuilder.Add(CustomSchemaData customSchemaData)
error MSB4018:    at Microsoft.Data.Tools.Schema.SchemaModel.ModelBuilder.AddOrUpdate(CustomSchemaData customSchemaData)
error MSB4018:    at Microsoft.Data.Tools.Schema.Sql.Build.SqlTaskHost.AddOrUpdate(CustomSchemaData customSchemaData)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.ProcessReferences(TaskLoggingHelper providedLogger, SqlTaskHost host, ErrorManager errors)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.LoadImpl(ITaskHost providedHost, TaskLoggingHelper providedLogger)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.TaskHostLoader.Load(ITaskHost providedHost, TaskLoggingHelper providedLogger)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlBuildTask.ExecuteLoadTaskHostStep()
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlBuildTask.ExecuteStep(Func`1 step)
error MSB4018:    at Microsoft.Data.Tools.Schema.Tasks.Sql.SqlBuildTask.Execute()
error MSB4018:    at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
error MSB4018:    at Microsoft.Build.BackEnd.TaskBuilder.d__20.MoveNext()

It has been already registered as a connect bug: https://connect.microsoft.com/SQLServer/feedback/details/749108/msbuild-fails-randomly-with-version-store-out-of-memory-cleanup-already-attempted-errors-msb4018
According to comments in the bug a solution is to edit project file *.sqlproj and add such section:

I just want to confirm that this fix works for me.

SSDT generates 2012 option for target 2008 when target is a dacpac

There is a bug in SSDT that causes database options to be generated for SQL Server 2012 even if the target platform is set to SQL Server 2008.

I observed it in a such scenario:
sqlpackage.exe was used to generate publish script to create new database. Sqlpackage target is set to a dacpacfile (just empty model without any tables).

The problem is that publish script has few options set which are available only in 2012. The options are:


But when I generate publish script directly from VisualStudio and specify target database as real 2008 database (not dacpac) then resulting publish script is correct. It doesn't contains above options.

This is already registered as a connect bug:
https://connect.microsoft.com/SQLServer/feedback/details/1049478/ssdt-generates-2012-option-for-target-2008-when-target-is-a-dacpac

I have implemented a deployment contributor that removes such invalid options. The code can be downloaded from following link:

https://gallery.technet.microsoft.com/scriptcenter/SSDT-contributor-to-remove-05488cf9

Highlight selected row on report in SQL Server Reporting Services

I have made hundreds of reports using PowerBuilder datawindows. It is a great environment for designing complex reports with lots of controls and events. Nowadays I use SQL Server Reporting Services and I am disappointed how complex it is. Despite lots of properties it is missing some basic features like: expression validating in editor, data preview in design view, scrollbars, etc.
One of missing features is highlighting selected row. Highlighting selected row is very useful in master-detail reports. By clicking on a master row the detail report is being refreshed. User want to see which master row is currently selected. In this post I show how to highlight selected row on report in SSRS.

The final effect will look like on this screen-shot.

 You might also watch a video how it works:

 

Highlighting selected row in Reporting Services report

 I use Report Builder 3.0 for this tutorial. I assume that you have already created a master-detail report and you only need to add highlighting. If you don't have the master-detail report you can follow this tutorial: http://www.codeproject.com/Articles/270924/Master-Details-Report-in-SSRS Lets say you have a master report with dsMaster data set and a detail report with dsDetail data set. Also you have a parameter, lets say @masterRowId. This parameter is used to pass a value from the master report to a child report, so the child knows which records to display.
Now it is time to add highlighting selected row to the master report:

1. Right click on dsMaster data set and choose Dataset Properties
2. Click on Parameters tab
3. Add parameter - @masterRowId and assign it value of -1 (or any dumb value which is not present in your master table)
   
   
   
4. Click Ok to close the Properties
5. Select data row in the master report by right clicking on the row indicator
   
6. In Properties window locate BackgroundColor setting
7. Expand the list of possible values for the BackgroundColor setting and choose the last - Expression
8. Enter following formula:
   
   
9. Click Ok, save reports.

That is all. You have just added highlighting selected row feature to your SSRS report.