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 12.0.0.0__89845dcd8080cc91

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

References:
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.

Definition

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.

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:
1) NULL
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:
SELECT ID, Name FROM [User]
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.

Examples

1) Insert multiple rows
CREATE TABLE Product
(
Id INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(1000)
)
INSERT INTO Product
VALUES ('John Doe'), ('Tim Burton'), ('Tom Thomas')

This example can be used to initialize table values.

2) SELECT using a derived table
SELECT x,y
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
DECLARE @val2 MONEY = 5

SELECT MAX(Val)
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:

http://olappivottableextend.codeplex.com

30 June 2014

Turn off Visual Studio 2013 Preview of files

Visual studio 2013 previews the file content when it is selected.
The feature can be very annoying and luckily it can be disabled.

To disable the feature, in the visual studio menu, select Tools -> Options -> Environment -> Tabs
Uncheck all the Preview tab checkboxes.

06 June 2014

Fix SSDT error" Could not load type Microsoft.SqlServer.TransactSql.ScriptDom.OnOffStatisticsOption"

The SQL Server Data Tools for SQL Server 2014 (SSDT) are now available for Visual Studio 2012 and 2013.
The SQL Server Data Tools - Business Intelligence for Visual Studio 2013 (SSDT-BI) are also available.
I installed both and after a while the schema compare started failing with the error:

Error 6 Could not load type 'Microsoft.SqlServer.TransactSql.ScriptDom.OnOffStatisticsOption' from assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

The solution that worked for me was to uninstall some components of SSDT and then repair the installation.
The detailed steps are:
1) Open Control Panel and then Programs and Features
2) Uninstall the following items:
    Microsoft SQL Server 2014 Management Objects version 12.0.2299.1
    Microsoft SQL Server 2014 Management Objects (x64) version 12.0.2299.1
    Microsoft SQL Server 2014 Transact-SQL ScriptDom version 12.0.2299.1
3) Select Microsoft SQL Server Data Tools 2013, right click to select Change

press the repair button


Note that this solution fixes the SSDT but may have side effect on the SSDT-BI.

02 June 2014

Undocumented sp_MSforeachdb: Iterate through each database

The stored procedure sp_MSforeachdb is used to iterate through each database that exists in SQL Server, including the system databases.

The stored procedure is undocumented and can be removed at any time, so use it carefully.

It is used to run a command over a set of databases in a server.
The stored procedure receives a parameter with the command to execute. The ? is used as a placeholder to identify the current database name listed by the stored procedure.

Some common scenarios where it can be used are:

1. Print all the database names, excluding the master,tempdb, model and msdb:
EXEC sp_msforeachdb 
"IF '?' NOT IN ('master','tempdb', 'model', 'msdb')   
BEGIN
        PRINT '?'
END"


2. Show the size of all the database
EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'


3. Check the integrity of all objects in the database
sp_MSforeachdb 'DBCC CHECKDB(?)' 


4. Retrieve database physical files information excluding the master,tempdb, model and msdb:
EXEC sp_msforeachdb 
'IF ''?'' NOT IN (''master'',''tempdb'', ''model'', ''msdb'')   
BEGIN
        SELECT name,physical_name,state,size FROM [?].sys.database_files
END'


5. Retrieve database physical files information excluding the master,tempdb, model and msdb to a table:

DECLARE @DbSize TABLE
    (
      mame NVARCHAR(50),
      physical_name NVARCHAR(500),
      size INT,
      growth INT,
   is_percent_growth BIT,
   type_desc NVARCHAR(10)
    )

INSERT  INTO @DbSize
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''master'', ''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,size, growth, is_percent_growth, type_desc
       FROM ?.sys.database_files
END'

SELECT * FROM @DbSize

27 May 2014

Entity Frameword 6 Error: Unable to update the EntitySet 'X' because it has a DefiningQuery and no element exists in the element to support the current operation.

I was inserting an object in entity framework 6 using the usual code:
context.X.Add(x); context.SaveChanges();
The entity framework returned the error:
Unable to update the EntitySet 'X'because it has a DefiningQuery and no element exists in the element to support the current operation.

The solution to fix this error was to create a primary key in the table and update the entity model.

19 May 2014

Poor Mans T-SQL formatter

A database developer must have a set of SQL coding Standards to avoid each statement being written differently.

Steven Bates has witten the SQL Server 2005 coding Standards in a series of blog posts at the MSDN blogs.
The rules are implemented by a SQL Server Management Studio add-in that is named Poor Mans T-SQL formatter.
The Poor Mans T-SQL formatter is a open-source T-SQL formatter that is available in several distibutions: Stand alone application, Notepad++ add-in, online,...
For more information visit the Poor Mans T-SQL formatter site, by pressing here

As for now there is no support to Management Studio 2014.
To make it work with Management Studio 2014:

1) Run the setup for the SQL Server Management Studio 2012 add-in available at the site.

2) Create the folder
%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\12.0\Addins\
if it doesn't already exists.

3) Copy the file from:
%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\11.0\Addins\PoorMansTSqlFormatterSSMSAddIn.AddIn
To
%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\12.0\Addins\PoorMansTSqlFormatterSSMSAddIn.AddIn

And now the add-in should be available at the Tools menu:
Note: You may need to enable show Hidden Items, in windows explorer View Menu, for the folder ProgramData to be shown.

06 February 2014

Visual Studio 2013 connection to TFS very slow

Visual Studio 2013 connects to Team Foundation Server (TFS) with the TFS client.
In some situations, out of our control, the connection to TFS is verty slow. The source control operations take ages to complete.

The problem appears to be network related, since at the same time one computer is very slow executing a source control operation and the others are working at a normal pace.

The TFS client usually connects to TFS using HTTP. The .Net Framework is used to make the connection.
Visual studio has a configuration of the Hypertext Transfer Protocol (HTTP) proxy server to perform the connection.
To avoid a slow speed the proxy must be disabled.

The configuration file for visual studio 2013 is located in
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe.config

Open the file and add / Update the following entry:


    
    
        
        
    


03 February 2014

Faster Android emulator on Intel Architecture

The Eclipse is the main stable development environment for Android.
The performance of the Android emulator is really bad, but Intel has a solution for computers on a Intel Architecture.

The recent computers with an Intel processor have the Intel Virtualization Technology enabled, that allows the acceleration of the the Android Emulator.
In win Windows and Apple Mac OS there is the Intel Hardware Accelerated Execution Manager (Intel HAXM) or KVM for Linux.

In this document I am focusing on the configuration of the Intel HAXM in a windows environment.

The prerequisites are:
1) Android SDK must be installed
2) An Intel processor with support for Intel VT-x, EM64T and Execute Disable(XD) Bit functionality enabled from the BIOS


To install the Intel HAXM:
1) Open the Android SDK manager where you installed it
2) Go to Extras in the tree-view and select the Intel x86 Emulator Accelerator(HAXM) and press the Install button

3) After the installation the SDK manager displays that the extra is installed, but it only downloaded a executable named IntelHaxm.exe that must be executed manually.
The file is tipically where you insalled the Android SDK in sdk\extras\intel\Hardware_Accelerated_Execution_Manager. If you can't find it search in windows explorer for the file.
4) Execute the IntelHaxm.exe and set the amount of memory that you want to reserve for the emulator and install it.
If your computer doesn't meet the requirements and error will occur, so validate that you BIOS settings are enabled for the Virtualization Technology.


The next step is to create an Android Virtual Device (AVD) with hardware accelerated emulation:
1) Launch Eclipse
2) Go to the AVD Manager and create a new device
3) Select Intel Atom (x86) as the CPU/ABI. This option is only available for Intel x86 system images, so you must install it.
You can install the Intel x86 Atom system images using the Android SDK manager.

4) Switch on the GPU emulation



The memory options of the AVD are also important. RAM values greater than 768M usually may fail on windows depending on the system load. Progressively lower the RAM values until the AVD is stable.

On windows 8.1 there is a side effect: The error dump CRITICAL_STRUCTURE_CORRUPTION
Intel has released a hotfix for windows 8.1 that is not included on the Android SDK Manager download.

Download it here