SQL Server 2012 : introduction to tabular model

Here, you can find a good article for you in order to introduce a sql server analysis services 2012 new functionality : tabular model : http://msdn.microsoft.com/en-us/library/hh231691(SQL.110).aspx   enjoy


Retreive Analysis Services warning with powershell

One great feature of Analysis Services is to give warning about your cube design during the design of your cube. These warning can be retreive after you have deployed your cube on the Analysis Services Server.   here is a sample script to retreive these warning : [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)$serverAS = New-Object Microsoft.AnalysisServices.Server$serverAS.connect(“ServerName”)$db = $serverAS.databases[“DatabaseName”] $val =…


Update SQL Server Statistics with Powershell

It can be usefull to have a script to update statistics with powershell. this script is very simple, you just have to connect to you database and call the updatestatistics() method for all your tables : [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null  $s = new-object (‘Microsoft.sqlserver.Management.smo.server’) myServer\myInstance  $db = $s.databases[“myDB”]  foreach ($t in $db.tables) {       write-host “Update stat”  $t.name      …


Rebuild SQL Server index with Powershell

let continue to explore what we can achieve with powershell for SQL Server management. The goal of this post is to rebuild fragmented index with power shell. To achieve that we need to use the invoke-sqlcmd cmdlet. try to use this command, if it doesn’t work, be sure to have all the system module loaded,…


SQL Server backup and powershell

In my previous post, I give some basic sample on how to use powershell for SQL Server. Here we are going to build a sample script to backup database. 1)  Perfom a full backup for a specified database : [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “myServer\myInstance” #Create a Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup namespace…


SQL Server and powershell

Here is some sample of what you can achieve to manager SQL Server with powershell, let start with system script : 1) is the SQL Service running ? Get-WmiObject win32_service | Where-Object {$_.name -match “^*SQL*”} | select SystemName, Name, StartName, State 2) how the process run ? get-wmiobject win32_process | where {$_.name -like “*sql*”} |…


Analysis Services and powershell

It is usefull to be able to see analysis services configuration and browsing cube structure with powershell. to achieve that, you first need to load the AMO assembly in Powershell : [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) And then create a server object : $serverAS = New-Object Microsoft.AnalysisServices.Server And connect to your Analysis Services server : $serverAS.connect(“OPIERI02\R2”)  Now, you can…


Usefull DMV Query

What are DMV : (Dynamic Management View) View of internals informations. DMV are basically SQL Views on some pretty important internal memory structures. DMV informations include metrics about : indexes query execution operating system Transaction Databases and much more What are DMF : (Dynamic Management Function) As an example sys.dm_exec_query_stats records details of the SQL…


Run SSIS Package programatically

here is a small sample that show how to run an existing package from a .Net program. This sample show how to replace target and source connection string and give a value to a package variable. It show too how to register package event. using Runtime = Microsoft.SqlServer.Dts.Runtime; private PackageEvents packageEvents; public event PackageEventEventHandler packageEvent…


XMLA from the Command line

I was very suprise to don’t find a tool to execute XMLA Script from the command line. Then I have decided to write it and it have been simple. Let do it ! 1) Use the useful namespace using Microsoft.AnalysisServices.AdomdClient; using System.Data; using System.IO; using System.Xml;   2) Start by connecting to Analysis Services 2005…