Archive

Archive for the ‘SQL Script’ Category

Overcome varchar(8000) or nvarchar(4000) limit in OpenQuery to SSAS

March 13, 2013 5 comments

OpenQuery has query string length limit to varchar(8000) or nvarchar(4000)

To overcome this limit you can use the below query:

DECLARE @mdx AS VARCHAR(MAX)
SET @mdx=' <Your MDX longer than varchar(8000)> '
SELECT len(@mdx) AS LENGTH_OF_MDX
Exec (@mdx) At <Your Linked Server>;

to use dynamic SQL, you can use the below tempalte:

BEGIN TRY
	DECLARE @SQL nvarchar(max)
	SET @SQL = 'Exec (''' + @MDX + ''') At ' + @CubeLocation
	EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
	SET @ErrorMessage = 'Query execution failed!'
	RAISERROR(@ErrorMessage , 16, 1) WITH NOWAIT
END CATCH

However, although the above can bypass the varchar(8000) limit, it brings another side issue. If you MDX returns a empty set, the SQL Server connection will be dropped. And even worse, this error cannot be captured by Try Catch block. Hence, if you need to implement this logic in your SQL stored procedure, you need to implemented MDX Count function to avoid returning no result set. For instance, count the result-set first and if count = 0, there will be no need to go ahead with the query. Here is the code sample for a function to return the row count of your MDX result set:

</pre>
WITH
 SET myset AS
 <Your SET Definition>
 Member [Measures].[cnt] AS
 myset.count
SELECT
 [Measures].[cnt] ON columns
FROM <Your Cube>
WHERE
 (
 <Your Slicers>
 )
<pre>

You can then wrap the above query in dynamic SQL which uses OpenQuery and linked server.

Advertisements

FW: Days Since Last Event: TSQL vs. MDX vs. DAX

January 19, 2011 Leave a comment

There is a nice blog discussing “Days Since Last Event”, you can find it here.

It compares the same logic implemented by TSQL, MDX and DAX. In my perspective, the TSQL is the most easy to understand. The MDX (2nd solution) is elegant. The DAX one is the hardest to understand. (Tips: don’t read the DAX code in the text, read from the screenshot and the code is formatted better).

CompareStrings_DataAccuracy – loop through characters to compare two strings

February 19, 2009 Leave a comment
USE [YourDatabaseName]
GO
/****** Object:  UserDefinedFunction [dbo].[CompareStrings_DataAccuracyTest]    Script Date: 10/25/2007 16:16:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CompareStrings_DataAccuracyTest] (@FirstString varchar(max), @SecondString varchar(max)) 
RETURNS @strCompare TABLE
(
    — columns returned by the function
    FirstStringlength int NOT NULL,
    num_of_errors int NOT NULL,
    accuracy decimal(3,2) NOT NULL,
    errorType nvarchar(255) null
)
AS 

BEGIN
    –First of all, remove all spaces within the string   
    set @FirstString = replace(@FirstString,’  ‘,”)
    set @SecondString = replace(@SecondString,’  ‘,”)
    set @FirstString = replace(@FirstString,’ ‘,”)
    set @SecondString = replace(@SecondString,’ ‘,”)

    DECLARE @Difference AS INT,
            @MaxLength AS INT,
            @Position AS INT
    declare @accuracy as decimal(3,2)
    Declare @errorType as nvarchar(255)   

    If isnumeric(@FirstString)=1 and isnumeric(@SecondString)=1
    –Start of Check if the answer is nermeric
    –if it is nemeric, check whether 2 parameters are equal. if equal, write to tem db and return
    –if not equal, continue next block of code to compare them with String Mode.
    Begin
        declare @tempFirstString as money
        declare @tempSecondString as money
        set @tempFirstString=convert(money,@FirstString)
        set @tempSecondString=convert(money,@SecondString)

        if @tempFirstString = @tempSecondString
        Begin
            set @Difference=0
            set    @accuracy=1
            set    @errorType=’Unknown’           
       
            — copy the required columns to the result of the function
            INSERT @strCompare
            SELECT 0, @Difference, @accuracy,@errorType
            RETURN   
        End   
    End
    –End of Check if the answer is nermeric

    Begin –Start of not nermeric

    /*
    Returns a count of the number of characters different in the 2 strings passed.

    If each string contains the same number of each letters and there are 2 differences,
    this will be counted as 1 difference as it is likely a typo: EG ‘String’ and ‘Strnig’
    */
   

    –Create Temporary table to hold the count of letters in words
    DECLARE @LettersInString TABLE
        (
        Letter CHAR(1) NOT NULL,
        FirstStringCount INT NOT NULL DEFAULT 0,
        SecondStringCount INT NOT NULL DEFAULT 0
        )

    –Difference is the counter of characters that are different in the 2 strings
    SET @Difference = 0
    –Position is the position of the character in the string that is being compared
    SET @Position = 1

    –determine the length of the longest string
    IF len(@FirstString) < len(@SecondString)
        SET @MaxLength = len(@SecondString)
    ELSE
        SET @MaxLength = len(@FirstString)

    –loop through each string until the maximum length is reached
    WHILE @Position <= @MaxLength
        BEGIN
        –check to see if the character in the first string is in the temp table. If not add
        IF NOT EXISTS (SELECT Letter FROM @LettersInString WHERE Letter = SUBSTRING(@FirstString, @Position, 1))
            INSERT INTO @LettersInString SELECT SUBSTRING(@FirstString, @Position, 1), 0, 0
       
        –add 1 to the count of the character in the temp table
        UPDATE @LettersInString
        SET FirstStringCount = FirstStringCount + 1
        WHERE Letter = SUBSTRING(@FirstString, @Position, 1)

        –check to see if the character in the second string is in the temp table. If not add
        IF NOT EXISTS (SELECT Letter FROM @LettersInString WHERE Letter = SUBSTRING(@SecondString, @Position, 1))
            INSERT INTO @LettersInString SELECT SUBSTRING(@SecondString, @Position, 1), 0, 0

        –add 1 to the count of the character in the temp table
        UPDATE @LettersInString
        SET SecondStringCount = SecondStringCount + 1
        WHERE Letter = SUBSTRING(@SecondString, @Position, 1)

        –check to see if the same position in each string holds the same character   
        IF SUBSTRING(@FirstString, @Position, 1) <> SUBSTRING(@SecondString, @Position, 1)
            SET @Difference = @Difference + 1

        –move to the next position
        SET @Position = @Position + 1
        END

        –If each string contains the same number of each letters and there are 2 differences, this will be counted as 1 difference as it is likely a typo: EG ‘String’ and ‘Strnig’
        –IF (SELECT COUNT(Letter) FROM @LettersInString WHERE FirstStringCount <> SecondStringCount) = 0 AND @Difference = 2
        —    SET @Difference = 1
       
       
        set @accuracy = 1 – cast(@Difference as decimal) / cast(len(@FirstString) as decimal)

       
        set @errorType=’Unknown’
        /*
        set @errorType=
            case
                when len(@FirstString) < len(@SecondString) Then ‘Type Extra Characters’
                when len(@FirstString) > len(@SecondString) Then ‘Missing Characters’
            else
                ‘Others’
            end
        */

        — copy the required columns to the result of the function
        INSERT @strCompare
        SELECT len(@FirstString), @Difference, @accuracy,@errorType
        RETURN             
    End  –end of not nermeric

END

Categories: SQL Script

Understand Nested Transaction

February 19, 2009 Leave a comment

Nested Begin Transaction and Commit/Rollback Transaction is
very confused. From my memory, rollback transaction within the nested begin transaction
will roll back the nested transaction as well as all parent begin transaction.
However the commit transaction only commit its corresponding transaction and if
there is a rollback outside, the transaction will still rollback unless you use
Save Tran cmd. You can play with the below script and exam the result.

USE [YourDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[test]’) AND type in (N’U’))
DROP TABLE [dbo].[test]
CREATE TABLE [dbo].[test](
    [value] [int] NOT NULL
) ON [PRIMARY]

insert into test values(1)
insert into test values(2)
insert into test values(3)
insert into test values(4)

DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
begin tran
    update test
    set value=111111
    where value=1   
    select @@TRANCOUNT

    IF @@TRANCOUNT >
0       
       SAVE TRANSACTION t1
    else
        BEGIN TRANSACTION

    update test
    set value=222222
    where value=1
rollback tran t1
commit

Categories: SQL Script

Calculating Age

February 19, 2009 Leave a comment
CASE
        WHEN Month(GetDate()) < Month(c.[BirthDate])
            THEN DateDiff(yy,c.[BirthDate],GetDate()) – 1
        WHEN Month(GetDate()) = Month(c.[BirthDate])
        AND Day(GetDate()) < Day(c.[BirthDate])
            THEN DateDiff(yy,c.[BirthDate],GetDate()) – 1
        ELSE DateDiff(yy,c.[BirthDate],GetDate())
    END AS [Age]
Categories: SQL Script

Detect Overlapping DateTime

February 19, 2009 Leave a comment
declare @starttime as datetime
declare @endtime as datetime
declare @inputstarttime as datetime
declare @inputendtime as datetime

set @starttime=’2007-04-01 00:00:00′
set @endtime=’2007-04-10 00:00:00′
set @inputstarttime=’2007-03-10 00:00:00′
set @inputendtime=’2007-04-01 00:00:00′

select    
    case
        when @inputstarttime>@starttime and @inputstarttime<@endtime
            Then ‘Yes’
        when @inputendtime>@starttime and @inputendtime<@endtime
            Then ‘Yes’
        when @inputstarttime <= @starttime AND @inputendtime >= @endtime
            Then ‘Yes’
        Else
            ‘No’
    end as [Overlapping?]

Categories: SQL Script