Thursday, June 15, 2017

Dump a procedure result set into a temporary table by leveraging on sys.dm_exec_describe_first_result_set_for_object function introduced in MS SQL Server 2012

The following script creates a temporary table with attributes defined dynamically based on metadata using sys.dm_exec_describe_first_result_set_for_object function out of a stored procedure.
It populates this table by inserting the recordset returning by this stored procedure.

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP Table ##tmp
GO

DECLARE @strSQL VARCHAR(MAX)

WITH cte AS (
SELECT 'CREATE TABLE ##tmp(' strSQL
UNION ALL
SELECT strSQL = ',' + name + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object
(OBJECT_ID('myschema.dbo.MyProc'), NULL)
UNION ALL
SELECT strSQL = ')')

SELECT @strSQL = COALESCE(@strSQL, '') +  strSQL
FROM cte
WHERE strSQL IS NOT NULL
OPTION (MAXDOP 1);

SET @strSQL = REPLACE(@strSQL,'(,','(');

EXEC (@strSQL);

INSERT INTO ##tmp
EXEC myschema.dbo.MyProc
    5512712        --@Field1    INT
    ....
    ,NULL;        --@FieldN    VARCHAR(15)

SELECT * FROM ##tmp;

Wednesday, May 17, 2017

Split SSRS XML into chunks and store it as temporary table records




Use case is known T-SQL limitation: The pattern to be searched and replaced can't exceed the maximum possible size: 8000 bytes, or 4000 Unicode characters. This applies to intrinsic functions REPLACE, CHARINDEX, PATINDEX and LIKE.
One way around is search XML using Xpath.
However, the stored procedure below performs the following:
1.       Starts at the beginning of the XML and reads 4000 characters. At this point it reads backwards to the last closed tag and stores that position e.g. may be at 3982 characters. It then stores this as the first “chunk” of XML.
2.       Then it continues to read the next 4000 characters from the last end position (e.g. was 3982 so would read from 3983), and at this point reads backwards again to get the next “chunk”.
3.       This process repeats until you reach the end of the XML then this allows us to read through each chunk using intrinsic T-SQL functions.
Note that this process works correctly providing all data sources within XML are no longer than the possible maximum size.  Best practice is to reside all data sources as database objects (Views, User Defined Functions or Stored Procedures).


 USE ReportServer;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_WARNINGS  OFF
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
      (SELECT *
          FROM sys.objects
          WHERE name = 'SplitXMLToTableRecords')
    drop procedure
YourSchema.SplitXMLToTableRecords
    PRINT 'drop procedure
YourSchema.SplitXMLToTableRecords'
GO

PRINT 'CREATE PROCEDURE YourSchema.SplitXMLToTableRecords'
GO

CREATE PROC
YourSchema.SplitXMLToTableRecords @Path NVARCHAR(425)
AS
    BEGIN
        BEGIN TRY
            DECLARE @reportXML NVARCHAR(MAX)
            ,       @strXML    NVARCHAR(4000)
            ,       @lenXML    INT
            ,       @index     INT
            ,       @posXML    INT
            ,       @loopsXML  NUMERIC(10,2);

            IF OBJECT_ID('tempdb..#tblXML') IS NOT NULL
                DROP TABLE #tblXML;
            CREATE TABLE #tblXML ( indXML INT
            ,                       [Path] NVARCHAR(425)
            ,                      strXML NVARCHAR(4000)
            ,                      lenXML INT );

            SELECT @reportXML = CAST(CONVERT(XML,CONVERT(VARBINARY(MAX),Content)) AS NVARCHAR(MAX))
            FROM ReportServer.dbo.Catalog
            WHERE [Path] LIKE @path;

            SET @loopsXML = CEILING((SELECT LEN(CAST(@reportXML AS NVARCHAR(MAX))))/CAST(4000 AS NUMERIC));
            SET @index = 0;
            SET @posXML = 1;

            WHILE (1 = 1) BEGIN
                SELECT @lenXML = LEN(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4001 - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000))) - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000 - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000))))))));
                IF @lenXML <> 0    BEGIN
                    SET @index = @index + 1;
                    SET @strXML = (SELECT SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4001 - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000))) - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000 - charindex('>',reverse(SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,4000))))))));
                    SET @lenXML = LEN(@strXML);
                    INSERT INTO #tblXML (    indXML
                    ,                        [Path]
                    ,                        strXML
                    ,                        lenXML)
                    VALUES (                 @index
                    ,                        @Path
                    ,                        @strXML
                    ,                        @lenXML
                    );
                    SET @reportXML = SUBSTRING(CAST(@reportXML AS NVARCHAR(MAX)),@posXML,LEN(CAST(@reportXML AS NVARCHAR(MAX))));
                    SET @posXML = @lenXML + 1;
                    IF @loopsXML = @index BREAK;
                END;
            END;

            SELECT *
            FROM #tblXML;
        END TRY
            BEGIN CATCH
                SELECT   ERROR_NUMBER() AS ErrorNumber 
                        ,ERROR_SEVERITY() AS ErrorSeverity 
                        ,ERROR_STATE() AS ErrorState 
                        ,ERROR_PROCEDURE() AS ErrorProcedure 
                        ,ERROR_LINE() AS ErrorLine 
                        ,ERROR_MESSAGE() AS ErrorMessage; 
            END CATCH;
    END;
/************************************************************************************************************************************
Test:

    IF OBJECT_ID('tempdb..#myXML') IS NOT NULL    DROP TABLE #myXML;
        CREATE TABLE #myXML ( indXML INT
        ,                      strXML NVARCHAR(4000)
        ,                      lenXML INT );

    INSERT INTO #myXML
            ( indXML, strXML, lenXML )
    EXEC ReportServer.
YourSchema.SplitXMLToTableRecords 'YourPath';

    SELECT *
    FROM #myXML;
*************************************************************************************************************************************/
GO

Tuesday, May 16, 2017

Get Middle And End Of Month Dates T-SQL User Defined Function

USE YourDatabase
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
    (SELECT *
    FROM sys.objects
    WHERE name = 'Middle_And_End_Of_Month_Dates')
    DROP FUNCTION YourSchema.Middle_And_End_Of_Month_Dates
PRINT 'DROP FUNCTION YourSchema.Middle_And_End_Of_Month_Dates'
GO

PRINT 'CREATE FUNCTION YourSchema.Middle_And_End_Of_Month_Dates'
GO

CREATE FUNCTION YourSchema.Middle_And_End_Of_Month_Dates(
/************************************************************************************************************************************
Generates Middle And End Of Month Dates

Change Log
#    Date       Who  Change
*************************************************************************************************************************************/
@Start datetime)
RETURNS TABLE
AS
    RETURN(
            SELECT DISTINCT GeneratedDate = EOMONTH ( @Start + decimal# )
            FROM (
            SELECT row_number() OVER (ORDER BY (select 1)) as decimal#
            FROM sys.all_columns) tbl1
            UNION
            SELECT GeneratedDate = CONVERT(datetime, EOMONTH ( DateValue ), 121) - IIF(day(DateValue) < 30, 14, 15)
            FROM (
            SELECT DISTINCT DateValue = EOMONTH ( @Start + decimal# )
            FROM (
            SELECT row_number() OVER (ORDER BY (SELECT 1)) as decimal#
            FROM sys.all_columns) tbl2
            ) tbl3
)
/*******************************************************************
Test Sample:

SELECT * FROM EDW_atomic.YourSchema.Middle_And_End_Of_Month_Dates(
 '2015-12-31'    --@Start
)

********************************************************************/
GO

Get Every 3rd Monday Date T-SQL Query

SELECT Every3rdMondayDate = Date
    FROM (    SELECT date                                                
            ,      ROW_NUMBER() OVER(PARTITION BY ym ORDER BY date DESC) AS RowNumber
            FROM (
            SELECT *                                
            ,      LEFT(CONVERT(varchar, Date,112),6) ym
            FROM (SELECT DATE
                        ,      (SELECT (DATENAME(dw,
                                                    CAST(DATEPART(m, DATE) AS VARCHAR)
                                                    + '/'
                                                    + CAST(DATEPART(d, DATE) AS VARCHAR)
                                                    + '/'
                                                    + CAST(DATEPART(yy, DATE) AS VARCHAR))
                                                    ))          DayOfWeek
                                FROM (SELECT Row#
                                            ,CAST('20000101' AS DATETIME) + Row# [DATE]
                                        FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Row#
                                                FROM sys.all_columns) t1) t2) tbl1
                                WHERE DayOfWeek = 'Monday') tbl2
        ) tbl3
    WHERE RowNumber = 3

Tuesday, May 9, 2017

Passing Pamertrised Query from Excel to SQL Server using Password Protected VBA

Passing Dynamic Query Values from Excel to SQL Server describes step by step solution of this use case.  The disadvantage of this approach is that the connection string is accessible by the end user.  The way around of this issue is to create password protected VBA as follows:

Sub btnRefresh_Click()
On Error GoTo Error_Handler
    Dim SellStartDate As String _
    , SellEndDate As String _
    , strSQL As Variant _
    , sSQLQry As String _
    , Conn As New ADODB.Connection _
    , mrs As New ADODB.Recordset _
    , DBPath As String _
    , sconnect As String

        Call ClearWKSData(ThisWorkbook.Worksheets("Your Sheet Long Name"), 2, 1)
        sconnect = "WSID=*;DRIVER=SQL Server;APP=Microsoft Office 2013;SERVER=Your Server Name;UID=Your SQL Account Name;PWD=Your Password;DATABASE=Your Database Name;"
        SellStartDate = Sheets("Change Parameters").Range("C3").Value
        SellEndDate = Sheets("Change Parameters").Range("C4").Value
        strSQL = strSQL + " SELECT * FROM dbo.YourUserDefinedFunction('" & SellStartDate & "','" & SellEndDate & "')"
        Conn.Open sconnect
        mrs.Open strSQL, Conn
        Sheets("Purchase Order Spend").Range("A2").CopyFromRecordset mrs
        mrs.Close
        Conn.Close
        ThisWorkbook.Worksheets("Your Sheet Long Name").Activate
      
Exit_Procedure:
        Exit Sub
Error_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, Err.Source
        Resume Exit_Procedure
End Sub

Sub ClearWKSData(wksCur As Worksheet, iFirstRow As Integer, iFirstCol As Integer)
On Error GoTo Error_Handler
    Dim iUsedCols As Variant, iUsedRows As Variant
  
        iUsedRows = wksCur.UsedRange.Row + wksCur.UsedRange.Rows.Count - 1
        iUsedCols = wksCur.UsedRange.Column + wksCur.UsedRange.Columns.Count - 1
      
        If iUsedRows > iFirstRow And iUsedCols > iFirstCol Then
            wksCur.Range(wksCur.Cells(iFirstRow, iFirstCol), wksCur.Cells(iUsedRows, iUsedCols)).Clear
        End If

Exit_Procedure:
        Exit Sub
Error_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, Err.Source
        Resume Exit_Procedure
End Sub

Private Sub Workbook_Open()
    ThisWorkbook.Worksheets("Change Parameters").Activate
End Sub

The .xlsm sample file is available for download. To lock the VBA please press Alt+F11 -> VBA Project Properties -> Protection Tab -> Lock project for viewing -> Enter your password.

Sunday, April 30, 2017

Replace String In File



The following PowerShell script replaces all occurrences of the string in file.

#Usage powershell -executionpolicy bypass C:\SharedToAnyone\ReportsAudit\ReplaceStringInFile.ps1 "c:\Temp\DirlistDEV.txt" '.' '`,'
    #Calling from VBA: Call Shell("powershell -executionpolicy bypass C:\SharedToAnyone\ReportsAudit\ReplaceStringInFile.ps1 'c:\Temp\DirlistDEV.txt' '.' ','")

    Clear-Host;
    $inputFile = $($args[0]);
    if ($inputFile) {
        Write-Host "Passed: $inputFile`n";
            $index = $inputFile.IndexOf(".");
            if ($index -eq 0) {
                Write-Host "Input file does not have any extensions";
                break;
            }
    } else {
        Write-Host "No file passed";
        break;
    }
    $findString = $($args[1]);
    if ($findString) {
        Write-Host "Passed string to find: $findString`n";
    } else {
        Write-Host "No string to find passed";
        break;
    }
    $findString = $findString.Replace(".","\.");
    $findString;
    $Replacement = $($args[2]);
    if ($Replacement) {
        Write-Host "Passed replacement string : $Replacement`n";
    } else {
        Write-Host "No replacement string passed";
        break;
    }
    $outputFile = $inputFile.SubString(0,$index) + ".tmp"
    $outputFile;
    Get-Content $inputfile | ForEach-Object { $_ -replace $findString, $Replacement } | Set-Content ($outputFile)
    Remove-Item $inputfile
    Rename-Item $outputFile $inputfile