Restore MDF file to New Database

Execute below script to restore any MDF and LDF files to new Database.

USE [master]

GO

CREATE DATABASE DemoDB ON

( FILENAME = N'I:\MSSQL\DATA\DemoDB.mdf' ),

( FILENAME = N'K:\MSSQL\Logs\DemoDB_log.ldf' )

FOR ATTACH ;

GO

SQL to Shrink LogFile after Truncating Records

Below SQL helps you to shrink a DB grown due to truncating of records from tables regularly:

USE DemoDB;

DBCC SHRINKFILE ('DemoDB_log',0,truncateonly)

SSAS Error: No Mapping between Account Names and Security IDs was Done

We may come across the below error mostly while we are deploying SSAS cube in to one server:

It is because of difference in domain between development server and deployment server. In that cases Goto Roles --> Membership tab, and remove non-supportive members from the role.

Convert Unix Timestamp to Date format

SELECT *, DATE_FORMAT(FROM_UNIXTIME(TimeStamp), '%d-%m-%Y %h:%i:%s') AS LogDate
FROM dbo.reports
ORDER BY Timestamp DESC
LIMIT 10

T-SQL to Disable all Jobs in in SQL Agent

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

SSIS Package Runs Fine in Integration Server but Fails From SQL Agent Job

SSIS Package Runs Fine in Integration Server but Fails From SQL Agent Job:

This is the most common issue faced when we deploy packages in 64 bit system. When we create any package with Run64bit set as false, this issue occurs.

In order to overcome the 32\64 bit environment issue, it better we use command line in our job to execute the packages.

Use the below command:

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec /DTS "\MSDB\ETLFolder\ETLExtractMaster" /SERVER DW01P /CHECKPOINTING OFF  /REPORTING V

SQL Server Timed Out due to Low Memory Space While SSIS Package was Running

SQL Server Timed Out due to Low Memory Space While SSIS Package was Running:

While I was running my data warehouse job (containing many ETL Master Packges) in SQL SERVER 2008 R2, environment I was frequently facing Timed Out issue and SQL server got disconnected, but when I ran single package manually, it ran without failure.

So I performed the below tasks to monitor the performance of the Job:

1. Monitor performance in Task manager: The CPU usage was more than 90% when the job was running.

2. Tracked job even in EVENT VIEWER when the job failed, and got the below error message:

3. Executed below query in SQL Server:

SELECT [name] AS [Name]
      ,[configuration_id] AS [Number]
      ,[minimum] AS [Minimum]
      ,[maximum] AS [Maximum]
      ,[is_dynamic] AS [Dynamic]
      ,[is_advanced] AS [Advanced]
      ,[value] AS [ConfigValue]
      ,[value_in_use] AS [RunValue]
      ,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Max server memory (MB)','Min server memory (MB)')

On executing the above query, I found that the server is configured with maximum config value:

4. Executed the below query to trouble shoot the issue:


EXEC sp_configure 'show advanced options', '1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'min server memory', '1024'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'max server memory', '6000'
RECONFIGURE WITH OVERRIDE

I set Minimum Allowed Memory to 1 GB and Maximum Allowed Memory to 6 GB and now my data warehouse job runs without any issue and consumes less CPU usage.

SQL to Track Running Jobs

Execute below query to find the SQL Jobs running in a server:

Exec msdb..sp_help_job @execution_status = 1

Unable to Restart SQL Agent after restarting SQL Server

When we restart SQLServer, sometimes SQL Agents gets stopped and we may unable to restart the SQLAgent, it remains as Stopped.

In that case execute the below query and try restarting SQL Agent:

EXEC sp_addsrvrolemember 'NT SERVICE\<ServiceName>', 'sysadmin';
GO
You must replace <ServiceName> with SQLServerAgent for the default instance or SQLAgent$InstanceName
for a named instance as below:

EXEC sp_addsrvrolemember 'NT SERVICE\SQLAGENT$BIP1', 'sysadmin';
GO

Batch File Script to deploy SSIS packages via DTUTIL command

The below code helps to deploy ssis packages to o MSDB folder in a server:

@Echo Off

Echo.
Echo.
Echo SSIS Package Installation Script
Echo.

if %1a == a goto Error
if %2a == a goto Error
if %3a == a goto Error

Echo.
Echo.
Echo Deployment Server: %1
Echo -----------------------------------------------------
Echo --This will delete any %3 data mart files
Echo --on the server, and reinstall from the local machine
Echo -----------------------------------------------------
Pause
REM Goto Out


REM Remove Existing files and directory on Server
for %%f in (%2"\*.dtsx") do (
Echo Now Removing: %%~nf
dtutil /Q /SourceS %1 /SQL "\%3\\%%~nf" /Del
)

dtutil /Q /SourceS %1 /FDe "SQL;\;%3"

:Create

Echo.
Echo Preparing to create folder
Echo.
pause

REM Create the Directory
dtutil /Q /SourceS %1 /FC "SQL;\;%3"
if errorlevel 1 goto End
Echo.
Echo Preparing to Copy Files to Server
Echo.
pause

:Out
REM copy the SSIS Packages to the server
for %%f in (%2"\*.dtsx") do (
Echo Now Copying: %%~nf
dtutil /Q /DestS %1 /Fi "%%f" /C "SQL;\%3\\%%~nf"
)


Echo.
Echo.
Echo Installation Complete!
Echo.
Echo.
Pause
Goto End

:Error
Echo.
Echo.
Echo Missing Servername!
Echo Syntax: Deploy SSIS Packages [servername] [Source File Path] [MSDB Deploy Folder]
Echo.
Echo.

Pause

:End

1. Copy the above code and crete a bat file (e.g., DeploySSIS).
2. Open Command Prompt and navigate to th ebatch file folder
3. execute the command DeploySsis.bat [SERVERNAME] [FILEPATH] [MSDB Sub-Folder]

SQL Function to Convert Seconds to HH:MM

Create a function as like below:

CREATE FUNCTION [dbo].[ufn_GetHHMM] ( @pInputSecs   BIGINT )
RETURNS VARCHAR(MAX)
BEGIN

DECLARE @HHMM       Varchar(Max)

IF @pInputSecs < 60 and @pInputSecs<>0
BEGIN
SET @HHMM= '0:01'
END
ELSE
BEGIN
SET @HHMM=ISNULL(CAST(@pInputSecs/3600 AS VARCHAR(MAX)) +':'
+RIGHT('0'+CAST(ROUND(CAST((@pInputSecs%3600.0)/60.0 as float),0) as varchar(MAX)),2),'0:00')
END

RETURN @HHMM


END

--To Get Results

SELECT dbo.ufn_GetHHMM(DurationinSeconds) --Assume you have column name 'DurationinSeconds'

MDX Named set to filter Dimension member based on their Text value

Named set to filter Dimension member based on their value or text:

FILTER([License].[Seat Class].ALLMEMBERS,
INSTR([License].[Seat Class].CURRENTMEMBER.MEMBER_CAPTION,'Free') >0 )


The above MDX gives the set of Seat Class containing name like '%Free%'

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;

}

}

C-Sharp Script to get IncrementalDate

The below script helps to get maximum date used for incremental load for the SSIS packages:

In below example, we have "TimeStarted" as a incremental date column. You need to create 2 string variables 'IncrementalDate' and 'NewIncrementalDate' in the package.

In script task set 'TimeStarted' (incrementaldate column) as Readonly variable and copy paste the below code and modify the highlighted text (in Yellow) as per the input column name. On executing the package 'NewIncrementalDate' variable will get updated with MaxIncremantal date.

/* 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 System.Windows.Forms;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
      DateTime NewIncrementalDate;
      String strNewIncrementalDate;

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */

        strNewIncrementalDate = Variables.IncrementalDate.ToString();
        NewIncrementalDate = DateTime.Parse(strNewIncrementalDate);
        //MessageBox.Show("Before: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
              
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */

        /*NewMaxEventDate.AddMonths(-1);*/
        //MessageBox.Show("After: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
        Variables.NewIncrementalDate = NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff");

    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */
        try
        {
            if (!Row.TimeStarted_IsNull  && Row.TimeStarted  > NewIncrementalDate)
            {
                //MessageBox.Show("CreateDate: " + Row.createdate.ToString("yyyy-MM-dd hh:mm:ss.fff") + "; NewDate: "
                 //              + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));
                NewIncrementalDate = Row.TimeStarted;
                //MessageBox.Show("NewDate: " + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));


            }

            Row.etlcreateddate = Variables.ContainerStartTime;
            Row.etllastmodifieddate = Variables.ContainerStartTime;

        }
        catch (Exception ex)
        {
            throw new Exception("Error in ExtractTestSample script.  Message: " + ex.Message);
        }
    }

}

How to determine largest value comparing two or multiple columns using T-SQL

The below function can be used as a alternate for GREATEST() function in MYSQL:

Create a function with below code in your SQL SERVER database:

Below function compares 11 columns in a table.

CREATE FUNCTION dbo.fnGreatest
   (  @Value0  sql_variant,
      @Value1  sql_variant,
      @Value2  sql_variant,
      @Value3  sql_variant,
      @Value4  sql_variant,
      @Value5  sql_variant,
      @Value6  sql_variant,
      @Value7  sql_variant,
      @Value8  sql_variant,
      @Value9  sql_variant,
      @Value10  sql_variant
    )
   RETURNS sql_variant
AS
   BEGIN
     
      DECLARE @ReturnValue sql_variant

      DECLARE @MaxTable table
         (  RowID      int  IDENTITY,
            MaxColumn sql_variant
         )

        INSERT INTO @MaxTable VALUES ( @Value0 )
        INSERT INTO @MaxTable VALUES ( @Value1 )
        INSERT INTO @MaxTable VALUES ( @Value2 )
        INSERT INTO @MaxTable VALUES ( @Value4 )
        INSERT INTO @MaxTable VALUES ( @Value5 )
        INSERT INTO @MaxTable VALUES ( @Value6 )
        INSERT INTO @MaxTable VALUES ( @Value7 )
        INSERT INTO @MaxTable VALUES ( @Value8 )
        INSERT INTO @MaxTable VALUES ( @Value9 )
        INSERT INTO @MaxTable VALUES ( @Value10 )

       SELECT @ReturnValue = MAX(MaxColumn)
      FROM @MaxTable

      RETURN @ReturnValue

   END
GO








SELECT fnGreatest(date1,date2,date3.....date11)  from <tablename>

DB Connection error while connecting SQL Server Via Excel

I tried to connect SQL Server with excel and faced the below issue:

DBNETLIB ConnectionOpen (Connect()).]SQLServer doesnot exists or access denied.

I was able to overcome this issue by registering SQLVDI.dll through command prompt,

On successful registration a message box prompting registration successful appears.