Show Desktop Icon deleted from Quick Lunch

If accidentally your ‘Show Desktop’ icon is deleted from quick lunch toolbar then do the following step 

1. Open new notepad

2. Paste the following code in the notepad
[Shell]
Command=2
IconFile=explorer.exe,3
[Taskbar]
Command=ToggleDesktop

3.Go to file menu and select Save As and in the file name write name as “Show Desktop.scf” and save it on desktop (you can save anywhere you want). Make sure that ‘Save as file’ should have all files selected. 

4.Drag & drop the file to the quick lunch bar .. and you are DONE!!

-Ravi Kumar V.

SQL BCP command to export full database data to text/csv file

There are times when you need to automate the exporting of table data to text file. In that command line BCP command comes handy.

SQL CODE TO EXPORT FULL DATABASE DATA:

SET NOCOUNT ON

DECLARE @cmd NVARCHAR(2048)

DECLARE @tableName NVARCHAR(2048)

DECLARE @schemaID NVARCHAR(2048)

DECLARE@schemaName NVARCHAR(2048)

DECLARE @outPath NVARCHAR(2048)  

SET @outPath = 'd:\test\'

DECLAREtableCursor CURSOR

FOR SELECT[name],[uid] FROMsysobjects WHEREtype='U' FOR READ ONLY

OPENtableCursor

FETCH NEXT FROM tableCursor INTO @tableName,@schemaID;

WHILE @@FETCH_STATUS = 0

BEGIN

      SET @schemaName = SCHEMA_NAME(@schemaID)

      SET @cmd = 'bcp [' + db_name() + '].['+ @schemaName +'].[' + @tableName + '] out ' + @outPath + @tableName + '.txt -t"," -T -c -S <SERVERNAME\INSTANCE>'

      PRINT @cmd

      EXEC xp_cmdshell @cmd

      FETCH NEXTFROM tableCursor INTO@tableName,@schemaID;

END;

CLOSEtableCursor;

DEALLOCATE tableCursor;

Happy Exporting !!

Ravi Kumar V.

Get list of measure in SSAS cube using PowerShell

There are times you are told to compare measure on two servers. Manually doing the task would be tedious. So I have prepared scripts which will generate the list of all measures in all cube on the given server. The script works for both SQL 2005 & SQL 2008.

Dimension can also be fetched in the same way.

The script will generate comma separated file named ‘MeasureList.txt’ in the same folder from where the power shell script running.

CODE:

$OLAPServer = Read-Host "`nEnter the name of the OLAP server"

$OutputFileName = "MeasureList.txt"

[reflection.assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") | out-null

[reflection.assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null

$ObjServer = New-Object "Microsoft.AnalysisServices.Server"

Trap{"[ERROR] No server found !";break} 

$ObjServer.Connect($OLAPServer)

New-Item $OutputFileName -type File -Force | out-null

#Add-Content $OutputFileName "CATALOG_NAME,CUBE_NAME,MEASURE_NAME,MEASURE_UNIQUE_NAME,MEASURE_AGGREGATOR,MEASURE_IS_VISIBLE,MEASURE_NAME_SQL_COLUMN_NAME,EXPRESSION"

Add-Content $OutputFileName "CATALOG_NAME,CUBE_NAME,MEASURE_NAME,MEASURE_UNIQUE_NAME,MEASURE_AGGREGATOR,MEASURE_IS_VISIBLE,MEASURE_NAME_SQL_COLUMN_NAME"

#--NOTE: Open "EXPRESSION" column if you need calculated members formula but it will spread in multiple line in excel.

foreach($OLAP_DB in $ObjServer.Databases)

{

#foreach($Cube in $OLAP_DB.Cubes){

# Write-Host -f Yellow "CUBE:" $Cube.Name

#}

Write-Host "Database: " $OLAP_DB

$AdomdClient = New-Object "Microsoft.AnalysisServices.AdomdClient.AdomdConnection"

$AdomdClient.ConnectionString = "data source=$OLAPServer;initial catalog=$OLAP_DB"

$AdomdClient.Open()

Write-Host "Status:"$AdomdClient.State

$DataSet = $AdomdClient.GetSchemaDataSet("MDSCHEMA_MEASURES", $null, $true)

Write-Host -f yellow "Number of Measures:"$DataSet.Tables["rowsetTable"].Rows.Count

ForEach($Cube in  $OLAP_DB.Cubes)

{

ForEach($row in $DataSet.Tables["rowsetTable"].Rows)

{

#Write-Host $Cube.Name "#" $row["CUBE_NAME"].ToString()

If($Cube.Name -eq $row["CUBE_NAME"].ToString())

{

$op = ""

$op = $op + $row["CATALOG_NAME"]

$op = $op + "," + $row["CUBE_NAME"]

$op = $op + "," + $row["MEASURE_NAME"]

$op = $op + "," + $row["MEASURE_UNIQUE_NAME"]

$op = $op + "," + $row["MEASURE_AGGREGATOR"]

$op = $op + "," + $row["MEASURE_IS_VISIBLE"]

$op = $op + "," + $row["MEASURE_NAME_SQL_COLUMN_NAME"]

#$op = $op + "," + $row["EXPRESSION"]

Add-Content $OutputFileName $op

}

}

}

$AdomdClient.Close();

}

$ObjServer.Disconnect();

Happy Power shelling!! 

Powershell: Check is Analysis Server / SQL Server is running!

If you are planning automation then it becomes necessary to check if the server is up and running before passing query to it.

Below is the Power-shell script which quickly checks if the SQL Server Analysis Server / SQL Server is running or not.

Checking Analysis Server (SSAS)

# Load AMO assembly

[reflection.assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null

$assembly = New-Object "Microsoft.AnalysisServices.Server" 

#Trap is since the connect method will throw error if no server is found.

Trap{$null;continue}

#Try connecting to server 

$assembly.Connect(".\SQL2008R2")

if($assembly.Version -eq $null){

Write-Host -f Red "Not connected"

}else{

Write-Host -f Yellow "Connected!"

}

Checking SQL Server (SQL)

# Load assembly

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$s = New-Object "Microsoft.SqlServer.Management.Smo.Server" "SQL2008R2"

if($s.Version -eq $Null){

Write-Host -f Red "SQL not connected!"

}else{

Write-Host -f Yellow "SQL Server connected!"

}

Happy Powershelling!!

Ravikumar Vishwakarma.