17 November 2015

How to fix visual studio 2015 error "The package did not load correctly"

When a visual studio extension is installed, a error can occur that gives the following error:

The 'zebre' package did not load correctly.

The problem may have been caused by a configuration change or by the installation of another extension. You can get more information by examining the file 'C:\Users\[user name]\AppData\Roaming\Microsoft\VisualStudio\14.0\ActivityLog.xml'.

Restarting Visual Studio could help resolve this issue.
Continue to show this error message?

The error can cause intellisense not working or other erratic issues.

A possible fix is to uninstall the extension that is causing the error.

The issued can be fixed executing the following command as an administrator:

devenv.exe /setup

If the previous solution does not work, reset the visual studio preferences by issuing the command (Execute as an administrator):

devenv.exe /resetuserdata

02 October 2015

Dapper: Powerful micro object mapper for .Net


Dapper.Net or Dapper is a simple and powerful micro object mapper for .Net, that eases the development of a Data Access Layer (DAL).
Dapper is in production used by Stack Overflow, helpdesk and other companies.
Performance is the major focus in Dapper. It is very fast and the execution time is near equal to a hand coded DAL using the SqlDataReader.
Dapper uses the POCO (Plain Old CLR Object) approach.


Dapper has several installation options:
1) Download from github
Dapper can be downloaded from the github
After the download the cs class files must be added to your project or class library.

2) Nuget package
Dapper is also available as a Nuget package that can be added to you Visual Studio Project references.


Dapper extend the IDbConnection interface with three Helpers:
1) Execute a query and map the results to any strongly type
public static IEnumerable Query(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

2) Execute a query and map the results to a Dynamic type
public static IEnumerable Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

3) Execute a command that returns no results
public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

A simple example is to retrieve user information from a database.
First define the POCO class that represents the user. The names must be equal to the database table names.
public class User
     public int UserID { get; set; } 
     public string Name { get; set; }
To select a list of all the users:
using (SqlConnection connection = new SqlConnection(myConnectionString))
     List users = connection.Query("SELECT * FROM [User]").ToList();
Not that "myConnectionString" must be a connection string accepted by the SqlConnection.
Dapper also works with LINQ to return a list (ToList) or single objects (Single / SingleOrDefault)

Parameterized queries

The parameters of a query are passed as anonymous classes.
The anonymous classes must have the query parameter name and it's value.

To select a list of all the users that the name ends with "Doe":
using (SqlConnection connection = new SqlConnection(myConnectionString))
     List users = connection.Query("SELECT * FROM [User] WHERE Name LIKE @name", new {name = "%Doe'"}).ToList();
In this example the name will be matched with the parameter @name of the query.

Stored Procedures

Dapper has extensive support for stored procedures.

using (SqlConnection connection = new SqlConnection(myConnectionString))
     List users = connection.Query("UserSearch", 
                                               new {name = "%Doe"}, 
                                               commandType: CommandType.StoredProcedure).ToList();
In this example, the stored procedure has a parameter named "name" that will receive the value "%Doe".
The parameters can also be passed using the DynamicParameters class, but the anonymous class approach works fine and is more simple.

Table Value Parameters

Dapper supports Stored Procedures Table Value Parameters (TVP):
System.Data.DataTable countryDataTable = new System.Data.DataTable();
countryDataTable.Columns.Add("Id", typeof(long));


using (SqlConnection connection = new SqlConnection(myConnectionString))
     bool isValid = connection.Query(@"IsValid", 
                                      new {
                                             UserID = 674,
                                             countries = countryDataTable.AsTableValuedParameter("[dbo].[IdList]")
                                           commandType: CommandType.StoredProcedure

Where IdList is the sql server table value type with the column Id of type INT
 [Id] [int] NULL

Multiple Result Sets

Dapper has support for multiple result sets in a single query.
In this case use the QueryMultiple extension method:
var sql = @" select * from Users where UserID = @id ";
sql += @" select * from Roles where UserID = @id"

using (var multi = connection.QueryMultiple(sql, new {id = myId }))
     var users = multi.Read<User>().Single();
     var roles = multi.Read<Role>().ToList();

CRUD - Create, Read, Update and Delete

Dapper has no native support for Create, Read, Update e Delete (CRUD)
These operations must be manually developed in queries.

There are however third party extensions that easy the implementation of these operations.
One of those is SqlMapperExtensions the can be found on the dapper github on the Dapper.Contrib Folder.
The file must be added to your project or class library.
This extension uses the POCO (Plain Old CLR Object) approach, so you must create a class that has it's properties with the same name and corresponding data type with the database table.
Take some time to read the extension code and verify that it implements the code accordingly to your needs.

The SqlMapperExtension implements the following extension methods:

The entity identifier must have the attribute [Key] to identify it.
public class User
     public int UserID { get; set; } 
     public string Name { get; set; }

1) Get
Get returns a single entity by it's Id.
using (SqlConnection connection = new SqlConnection(myConnectionString))
     User user = connection.Get(200);

2) Insert
Inserts the entity and returns it's Id.

using (SqlConnection connection = new SqlConnection(myConnectionString))
     User user = new User();
     user.Name = "John Doe";

3) Update
Updates the entity.

4) Delete
Delete the entity by it's Id.

5) DeleteAll
Deletes all the entities in the table with the same name as the POCO class.
The best documentation for this extension is at the Test cases

12 May 2015

Time testing: How to prevent time syncing

A common task when performing tests on data sets that vary over time is to advance the windows time.
The server or the workstation can however be part of a domain and the time is synced with it.
In this situation after the date/time is changed for the tests, the sync process will update them to the Domain Controllers value.
This will reset the date/time and the test environment is lost.

A question will then arise:
How to prevent time syncing with Domain Controllers?

The best solution that works in my test environment, is to change the registry key Type (REG_SZ value) located at

The Type can have the following values:
Value Description
NoSync No Sync with Domain Controllers
Nt5DS Sync with Domain Controllers

Note that the Nt5DS value is my configuration. Keep your Type value in the registry to restore to it.

The value can be updated with the registry editor (regedit) or by the command line, executing the following command:

reg add HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Parameters /v Type /d NoSync /f

Note that the value NoSync forces the time not being synced with the Domain Controllers. Change the value to you configuration to restore the time sync.

This solution is great, but be aware that if the server/workstation date/time gets out of sync with the Domain Controllers, authentication will fail and you can loose access to them.
While testing, keep the server/workstation logged-in so that you can keep the date/time in sync with the Domain Controllers.

03 March 2015

Fix: Property PopulationStatus is not available for FullTextCatalog

After restoring a database with full text search active, on Sql Server 2014, on a staging server I got the following error when opening the Full Text Catalog:

Property PopulationStatus is not available for FullTextCatalog '[SearchDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

The error is not self explanatory, but after some investigation I descovered that Full Text Search Service isn't installed on the staging server.

The easy way to validate if  Full Text Search is installed on a Sql Server is to execute the following T-SQL statement on Management studio:


28 November 2014

Management Studio: The type initializer for ‘PropertyMetadataProvider’ threw an exception

Sql Server Management Studio, gave me the following error after I rebooted my computer:
The type initializer for ‘PropertyMetadataProvider’ threw an exception

I installed the windows updates and started relating the problem to the updates.

After some research I found a interesting artice in Sql Blog about the problem that states that the DLL versions of the cumulative update 4 for SQL Server 2014 had bad DLL versions.

The temporary workaround is:
1) Close all instances of Management studio and visual studio
2) Open a administrator command prompt
3) Execute the following commands :

         (i) cd %WINDIR%\assembly\GAC_MSIL\Microsoft.SqlServer.Smo
         (ii) rmdir /Q /S

4. Run the SQL Server 2014 setup
5. Select the Maintenance option
7. Press the Repair link
8. Pass all the per-requirement steps and click Repair button
9. Once the repair is finished launch Management studio and the error should be fixed

Sql Blog

08 July 2014

SQL Server Table Value Constructor

The latest versions of Sql Server (SQL Server 2008 or above) implement the Table Value Constructor.


The Table Value Constructor defines a set of row value expressions to be added to a table.
It allows us to create tables of values and/or expressions.
The Table Value Constructor allows us to simplify the T-SQL syntax.


The Table Value Constructor is defined as:
VALUES (<row value expression>),(<row value expression>), (<row value expression>), ...
The Table Value Constructor must start with the VALUE keyword and that it can have one or more row value expression.
The <row value expression> can have one of the following values:
2) DEFAULT. If there isn't a default defined for the column NULL is inserted.
3) A constant, variable or an expression. The expression cannot have the EXECUTE statement.
The expression only allows single scalar values. Sub-queries that return multiple columns are not supported.
An example of an invalid sub-query is:
Note that the values list can have a maximum of 1000 rows. The sql server error 10738 is thrown if the number of rows is greater than the 1000 rows limit.
To insert more row use the classic approaches like a multiple INSERT statements or a BULK INSERT.


1) Insert multiple rows
Name VARCHAR(1000)
VALUES ('John Doe'), ('Tim Burton'), ('Tom Thomas')

This example can be used to initialize table values.

2) SELECT using a derived table
FROM (VALUES (1, 'row 1'), (2, 'row 2'), (3, 'row 3')) AS myTable(x,y)

This example uses the Table Value Constructor as a derived table, to create a table named myTable with the columns x and y.

3) SELECT the maximum value between two variables
DECLARE @val1 MONEY = 20

FROM (VALUES (@val1), (@val2)) AS MyTable(Val)

The query returns the value 20. This can be very useful to easily select the maximum value between two or more variables, since sql server doesn't have a built-in function to do this operation.

Table Value Constructor can also be used with the MERGE statement.

01 July 2014

SSAS: OLAP PivotTable Extensions

Excel Pivot tables are a reporting tool that makes it easy to extract information from data sets without the use of formulas.
Excel Pivot tables are great for browsing Analysis Services cubes, since they allow to easily move, pivot and analyze data using drag and drop to see the same data in a number of different ways
OLAP PivotTable Extensions add advanced features to the pivot tables.
OLAP PivotTable Extensions are an Excel add-in which extends the functionality of PivotTables on Analysis Services cubes.
It works with Excel 2007, Excel 2010, and Excel 2013.
The Excel API has certain PivotTable functionality which is not exposed in the user interface. OLAP PivotTable Extensions provides an interface for some of this functionality and it also adds some new features like searching cubes, configuring default settings, and filtering to a list in your clipboard.

For more information and to install the OLAP PivotTable Extensions go to the codeplex website: