Analysis Services connectivity issue from Remote Server

Problem: I was recently working with one of my colleagues to setup a new server in Azure. He installed all the SQL server components. When we tried to connect OLAP cubes from another local or remote server, we were facing connectivity issue with the following error message:

“A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connection host has failed to respond xxx.xxx.xxx.xxx:xxxx(System)."

Solution:

First we checked if SQL Server Browser service was running (Log On As) under “Local Service”. We made the changes and tried to connect SSAS through SQL Server Management Studio (SSMS) but no luck.

Then we guessed that may be our Windows Firewall was denying the access. To solve this issue, we added a new inbound rule in Windows Firewall for TCP port 2383. Please note that SSAS port is 2383 (instead of SQL port 1433). After adding new port, we were able to connect SSAS successfully from remote server.

I hope this will help you in case you encounter similar problem.

T-SQL code to parse Numbers from a String

Someone asked me recently about T-SQL function to parse numbers from a string. Since there is no built-in function as such, I explained him the logic to calculate same thing using T-SQL code. I will explain the logic here, in case you need to implement same thing (or somewhat similar thing) in your project.

Let’s take example to understand the problem.

Input	Output
Hari12 Sharma34	1234
My 1st code is D$34A and 2nd code is E#078!!!	1342078
Hari# Sharma
$1#0?0 !?@1	1001

Here is the code to get the desired output:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'

DECLARE @Position SMALLINT = 0

SET @Position = PATINDEX('%[^0-9]%', @Str) --Find first character

 

WHILE (@Position > 0)

BEGIN

    -- Replace alphabet with empty string.

    SET @Str = STUFF(@Str, @Position, 1, '')

   

    -- Find next alphabet

    SET @Position = PATINDEX('%[^0-9]%', @Str)

END

SELECT @Str Result

 

Result

-------------------------------------

1342078

 

We can also write function to encapsulate this code and use wherever required.

This above code can be optimized by using Tally table mentioned below:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'

 

DECLARE @Result VARCHAR(100)

SELECT  @Result = COALESCE(@Result, '') + Digit

FROM    (

        SELECT  SUBSTRING(@Str, t.ID, 1) AS Digit

        FROM    dbo.Tally t

        WHERE   t.ID <= LEN(@Str)

        AND     PATINDEX('%[0-9]%', SUBString(@Str, t.ID, 1)) = 1

        ) stg

 

SELECT @Result Result

Result

-------------------------------------

1342078

Date-Range multiple tables using T-SQL

Did you ever come across a situation where you need to join multiple tables (consisting FromDate and ToDate) and combine the data into single target table with proper date range?

In this blog post, I will try to explain how to date-range two source tables into single table based on FromDate and ToDate.

Problem: There are two tables Address and Email. Address of any customer can change at any point of time. Likewise, Email ID of customer can be changed at time. We need to track this information using FromDate (start time) and ToDate (end time).

Below are the sample data of these tables:

Address
AddressID	CustomerID	FullAddress	FromDate	ToDate
1	1	148th Ave NE, A10	2005-07-01	2006-08-07
2	1	156th Ave NE, D5	2006-08-08	2009-11-20
3	1	1100 NE 40th ST, A10	2009-11-21	2011-03-31
4	1	148th Ave NE, J101	2011-04-01	9999-12-31
5	2	14528 NE 40th ST	2007-05-01	2008-04-15
6	2	140th Ave NE, K11	2008-04-16	2012-06-25
7	2	145th Ave NE 31st ST	2012-06-26	9999-12-31
Email
EmailID	CustomerID	EmailAddress	FromDate	ToDate
1	1	hari11@gmail.com	2005-07-01	2010-09-30
2	1	hari21@yahoo.com	2010-10-01	2012-12-31
3	1	hari31@hotmail.com	2013-01-01	9999-12-31
4	2	john42@gmail.com	2007-05-01	2013-03-31
5	2	john52@hotmail.com	2013-04-01	9999-12-31

Now consider the above scenario and data, we need following output from these two tables:

Output
CustomerID	FullAddress	EmailAddress	FromDate	ToDate
1	148th Ave NE, A10	hari11@gmail.com	2005-07-01	2006-08-07
1	156th Ave NE, D5	hari11@gmail.com	2006-08-08	2009-11-20
1	1100 NE 40th ST, A10	hari11@gmail.com	2009-11-21	2010-09-30
1	1100 NE 40th ST, A10	hari21@yahoo.com	2010-10-01	2011-03-31
1	148th Ave NE, J101	hari21@yahoo.com	2011-04-01	2012-12-31
1	148th Ave NE, J101	hari31@hotmail.com	2013-01-01	9999-12-31
2	14528 NE 40th ST	john42@gmail.com	2007-05-01	2008-04-15
2	140th Ave NE, K11	john42@gmail.com	2008-04-16	2012-06-25
2	145th Ave NE 31st ST	john42@gmail.com	2012-06-26	2013-03-31
2	145th Ave NE 31st ST	john52@hotmail.com	2013-04-01	9999-12-31

Solution: There could be several ways to achieve the above output. Here is one the T-SQL scripts to get the desired output:
----------------------------------------------------------
--Description: Script to combine different date-ranged
--             columns into single date-ranged table.
----------------------------------------------------------
  
----------------------------------------------------------
--Create test tables
----------------------------------------------------------
IF OBJECT_ID('tempdb..#Address', 'U') IS NOT NULL DROP TABLE #Address
CREATE TABLE #Address
(
     AddressID INT IDENTITY(1, 1)
      ,CustomerID INT NOT NULL
      ,FullAddress VARCHAR(100) NOT NULL
      ,FromDate DATE NOT NULL
      ,ToDate DATE NOT NULL
      ,CONSTRAINT PKC_Address PRIMARY KEY CLUSTERED
   (CustomerID, FromDate)
)
  
IF OBJECT_ID('tempdb..#Email', 'U') IS NOT NULL DROP TABLE #Email
CREATE TABLE #Email
(
     EmailID INT IDENTITY(1, 1)
      ,CustomerID INT NOT NULL
      ,EmailAddress VARCHAR(50) NOT NULL
      ,FromDate DATE NOT NULL
      ,ToDate DATE NOT NULL
      ,CONSTRAINT PKC_Email PRIMARY KEY CLUSTERED
    (CustomerID, FromDate)
)
  
----------------------------------------------------------
--Insert test data
----------------------------------------------------------
INSERT #Address (CustomerID, FullAddress, FromDate, ToDate) VALUES
 (1, '148th Ave NE, A10', '2005-07-01', '2006-08-07')
,(1, '156th Ave NE, D5', '2006-08-08', '2009-11-20')
,(1, '1100 NE 40th ST, A10', '2009-11-21', '2011-03-31')
,(1, '148th Ave NE, J101', '2011-04-01', '9999-12-31')
,(2, '14528 NE 40th ST', '2007-05-01', '2008-04-15')
,(2, '140th Ave NE, K11', '2008-04-16', '2012-06-25')
,(2, '145th Ave NE 31st ST', '2012-06-26', '9999-12-31')

　
INSERT #Email (CustomerID, EmailAddress, FromDate, ToDate) VALUES
 (1, 'hari11@gmail.com', '2005-07-01', '2010-09-30')
,(1, 'hari21@yahoo.com', '2010-10-01', '2012-12-31')
,(1, 'hari31@hotmail.com', '2013-01-01', '9999-12-31')
,(2, 'john42@gmail.com', '2007-05-01', '2013-03-31')
,(2, 'john52@hotmail.com', '2013-04-01', '9999-12-31')

----------------------------------------------------------
--Verify test data
----------------------------------------------------------
SELECT * FROM #Address
SELECT * FROM #Email


----------------------------------------------------------
--T-SQL code to get the required output
----------------------------------------------------------
SELECT    t1.CustomerID
              ,t1.FullAddress
              ,t1.EmailAddress
              ,t1.FromDate
              ,t1.ToDate
FROM
(
SELECT    a.CustomerID
              ,a.FullAddress
              ,e.EmailAddress
              ,(SELECT MAX(U.FromDate)
           FROM (VALUES (a.FromDate), (e.FromDate)) AS U (FromDate)
                ) AS FromDate
               ,(SELECT MIN(U.ToDate)
           FROM (VALUES (a.ToDate), (e.ToDate)) AS U (ToDate)
                ) AS ToDate
FROM      #Address AS a
LEFT JOIN    #Email AS e
ON        a.CustomerID = e.CustomerID
) AS t1
WHERE ISNULL(t1.FromDate, '9999-12-31') <= ISNULL(t1.ToDate, '9999-12-31')

GO