T-SQL to Filter Records Containing Special Characters and Latin Characters

The below SQL code helps to filter record containing Non-English characters:

SELECT * FROM [Table_Name]
WHERE [Column_Name] LIKE N'%[^ -~]%' collate Latin1_General_BIN

To Check SQL Server Status & Blocking Process

-- 1. To check any blocking in SQL server

SELECT CMD, * FROM SYS.SYSPROCESSES WHERE BLOCKED > 0

-- 2. Check the log space in the server

DBCC SQLPERF (LOGSPACE)

GO

  

-- 3. Check any process is running for long time.

SP_WHO2

Example MDX Calculation for SSAS Cube

Example MDX Calculations

Sales Growth

([Time].CurrentMember, [Measures].[Sales]) - ([Time].CurrentMember.PrevMember, [Measures].[Sales])

PP Growth

([Time].CurrentMember, [Measures].[Sales]) - (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])

YTD Sales

sum(ytd([Time].CurrentMember), [Measures].[Sales])

YTD Sales Using PeriodsToDate

sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])

Product Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100

Product Total Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].[All Products], [Measures].[Unit Sales]) * 100

Average Stock - Brute Force

Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity]) / Count(Descendants([Time].CurrentMember, [Month]))

Average Stock - Elegant

Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Closing Period - Brute Force

(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])

Closing Period - Elegant

(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])

Max for Period

Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Min for Period

Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Moving Average

Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])

Products Down

Count(Filter(Descendants([Product].CurrentMember, [Product Name])             , ([Time].CurrentMember, [Measures].[Unit Sales])               < ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales]))) / Count(Descendants([Product].CurrentMember, [Product Name]))

Dynamic Custom Default Member

Tail(Filter([Time].[Month].Members      , Not IsEmpty([Time].CurrentMember)), 1).Item(0)

Properties Query

Filter([Store].[Store Name].Members        , Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)

Products Down Query

select       {{{[Measures].[Products Down]}         * {[Customers].DefaultMember}         * {[Product].DefaultMember}}} on columns      , {[Time].&[1998].&[Q1].&[1]         , [Time].&[1998].&[Q1].&[2]         , [Time].&[1998].&[Q1].&[3]         , [Time].&[1998].&[Q2].&[4]         , [Time].&[1998].&[Q2].&[5]         , [Time].&[1998].&[Q2].&[6]         , [Time].&[1998].&[Q3].&[7]         , [Time].&[1998].&[Q3].&[8]         , [Time].&[1998].&[Q3].&[9]         , [Time].&[1998].&[Q4].&[10]         , [Time].&[1998].&[Q4].&[11]} on rows  from      [Sales_MDX2]

MDX Query

select     {[Measures].[Unit Sales]      , [Measures].[MA]} on columns     , {Descendants([Time].[Year].&[1997], [Month])} on rows from     [Sales_MDX2]

System DSN ODBC Connection Missing While Creating SSIS Connection Manager

We often come across this issue when our working server and source server have different bits 32 or 64.

You might have created System DSN in your system, but when try to create connection manager, the DSN will be missing in ODBC list. To overcome this you need to create DSN connection in appropriate ODBC (32/64).

Perform the followings:

Open command window:

1. Navigate to C:\Windows\Sysos64\Odbacd32.exe

2. ODBC Connection wizard will appear.

3. Create a new system DSN there.

4. Now try creating Connection Manager in SSIS package

Attaching MDF file to a Database

Below is the query to attach a .mdf file to a database

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO

Query to get list of packages in a Integration Server

Below is the SQL query to get the list of packages deployed to an Integration Server:

WITH ChildFolders

AS

(

    SELECT PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,

        CAST('' AS SYSNAME) AS RootFolder,

        CAST(PARENT.foldername AS VARCHAR(MAX)) AS FullPath,

        0 AS Lvl

    from msdb.dbo.sysssispackagefolders PARENT

    WHERE PARENT.parentfolderid IS NULL

    UNION ALL

    SELECT CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,

        CASE ChildFolders.Lvl

            WHEN 0 THEN CHILD.foldername

            ELSE ChildFolders.RootFolder

        END AS RootFolder,

        CAST(ChildFolders.FullPath + '/' + CHILD.foldername AS VARCHAR(MAX))

            AS FullPath,

        ChildFolders.Lvl + 1 AS Lvl

    FROM msdb.dbo.sysssispackagefolders CHILD

        inner join ChildFolders ON ChildFolders.folderid = CHILD.parentfolderid

)

SELECT F.RootFolder, F.FullPath, P.name AS PackageName,

    P.[description] AS PackageDescription, P.packageformat, P.packagetype,

    P.vermajor, P.verminor, P.verbuild, P.vercomments,

    CAST(CAST(P.packagedata AS VARBINARY(MAX)) AS XML) AS PackageData

FROM ChildFolders F

    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid

ORDER BY F.FullPath ASC, P.name ASC;

Convert Seconds to hours, minute, seconds (hh:mm:ss) in MDX

WITH MEMBER [Measures].[TimSpent (HH:MM:SS)] AS '[Measures].[Timespent]/ 86400', FORMAT_STRING = "hh:mm:ss"

SELECT [Assignment].[Element].[Element] ON ROWS, {[Measures].[Timespent], [Measures].[TimSpent (HH:MM:SS)]} ON COLUMNS

FROM [Activity]