T-SQL to Clean Buffers and Cache in SQL Server

T-SQL to Clean Buffers and Cache in SQL Server

DBCC FREEPROCcache
DBCC FREESystemCache('ALL')
DBCC DROPCLEANBUFFERS

MDX Tuple to Convert Seconds to hh:mm:ss

vba!Format(vba!fix([Measures].[Avg Usage by Active Users] / 3600) ,"00") + ":" +  vba!Format((vba!fix([Measures].[Avg Usage by Active Users] / 60) - (vba!fix([Measures].[Avg Usage by Active Users] / 3600) * 60) ) ,"00") + ":" +  vba!Format([Measures].[Avg Usage by Active Users] - (vba!fix([Measures].[Avg Usage by Active Users] / 60) * 60) ,"00")

Convert Seconds to Days Hour Minutes Seconds in SSRS Report

Convert Seconds to Days Hour Minutes Seconds in SSRS Report

Method 1: Go to report property. Open Code section and paste the below code:
Public Function SecondsToText(ByVal intTotalSeconds) As String
    Dim hours As String =INT(intTotalSeconds/3600)
    If Len(hours) < 2 Then
        hours = RIGHT(("0" & hours), 2)
    End If
    Dim mins As String = RIGHT("0" & INT((intTotalSeconds MOD 3600)/60), 2)
    Dim secs AS String = RIGHT("0" & ((intTotalSeconds MOD 3600) MOD 60), 2)

    SecondsToText= hours & ":" & mins & ":" & secs

End Function

Method 2: Go to report property. Open Code section and paste the below code:

Function SecondsToText(Seconds) As String
Dim bAddComma As Boolean
Dim Result As String
Dim sTemp As String
Dim days as String
Dim hours as String
Dim minutes as String
 
If Seconds <= 0 Or Not IsNumeric(Seconds) Then
     SecondsToText = "0 seconds"
     Exit Function
End If
Seconds = Fix(Seconds)
If Seconds >= 86400 Then
  days = Fix(Seconds / 86400)
Else
  days = 0
End If
If Seconds - (days * 86400) >= 3600 Then
  hours = Fix((Seconds - (days * 86400)) / 3600)
Else
  hours = 0
End If
If Seconds - (hours * 3600) - (days * 86400) >= 60 Then
 minutes = Fix((Seconds - (hours * 3600) - (days * 86400)) / 60)
Else
 minutes = 0
End If
Seconds = Seconds - (minutes * 60) - (hours * 3600) - _
   (days * 86400)
If Seconds > 0 Then Result = Seconds & " second" & AutoS(Seconds)
If minutes > 0 Then
    bAddComma = Result <> ""
   
    sTemp = minutes & " minute" & AutoS(minutes)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
If hours > 0 Then
    bAddComma = Result <> ""
   
    sTemp = hours & " hour" & AutoS(hours)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
If days > 0 Then
    bAddComma = Result <> ""
    sTemp = days & " day" & AutoS(days)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
SecondsToText = Result
End Function

Function AutoS(Number)
    If Number = 1 Then AutoS = "" Else AutoS = "s"
End Function

Then in field paste the below code:

=code.SecondsToText(fields!TimeInSeconds.value)

Method 3:

=DATEADD("s", SUM(Fields!TimeinSeconds.Value), CDate("00:00")).ToString("HH:mm:ss")

Attach mdf file to Database in SQL Server

Below is the code to attach *.mdf file to existing database:

USE [master]
GO
Method 1:
EXEC sp_attach_single_file_db @dbname='BISource',
@physname=N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf'
GO
Method 2:
CREATE DATABASE BISource ON
(FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH_REBUILD_LOG
GO
Method 3:
CREATE DATABASE BISource ON
( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH
GO

Determine Size of SQL Table

How to determine size of SQL server table:


Use built-in code: sp_spaceused ‘Tablename’

Example: sp_SpaceUsed 'Employee'

Mandatory Environment Variable for Pentaho

To work efficiently in Pentaho, a user must install JDK and JRE (JDK 1.7.1 and JRE7.0 or any higher version) and create below Environment Variable in Syste:

Variable Name: JRE_HOME
Variable Value: C:\Program Files\Java\jre7


Variable Name: JAVA_HOME
Variable Value: C:\Program Files\Java\jre7

These variable allows user to browse Pentaho Admin and Server web pages.

C-Sharp Script to Derive HashValue for Multiple Columns

The below script help us to create hashvalue which can be used to compare records while inserting/updating rows in a table using SSIS packages.

In script task select the columns you want to consider for deriving hashvalue and copy-paste the below code:


/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline;
using System.Text;
using System.Security.Cryptography;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent

{

private PipelineBuffer inputBuffer;

public override void ProcessInput(int InputID, PipelineBuffer Buffer)

{

inputBuffer = Buffer;

base.ProcessInput(InputID, Buffer);

}



public override void Input0_ProcessInputRow(Input0Buffer Row)

{

var counter = 0;

var values = new StringBuilder();



//loop through input columns

for (counter = 0; counter < inputBuffer.ColumnCount; counter++)

{

object value;

value = inputBuffer[counter];

//add each column value to one big string

values.Append(value);

}

//set output column as results of hash method

Row.HashColumn = CreateHash(values.ToString());



base.Input0_ProcessInputRow(Row);

}



private string CreateHash(string data)

{

//get byte array of long data string

var dataToHash = (new UnicodeEncoding()).GetBytes(data);

//create hash provider and compute hash of byte array

var sha1 = new SHA1CryptoServiceProvider();

var hashedData = sha1.ComputeHash(dataToHash);

RNGCryptoServiceProvider.Create().GetBytes(dataToHash);

//convert results to hexadecimal string (SQL friendly format)

var result = BitConverter.ToString(hashedData).Replace("-", "");

return result;

}

}