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

Here is a simple T-SQL script which does the job.

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

If you find this script useful please donate generously.

Get Every 3rd Monday Date T-SQL Query

Here is a simple T-SQL script which does the job.

    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

If you find this script useful please donate generously.

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.

If you find this script useful please donate generously.

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 

If you find this script useful please donate generously.

MS SSRS Reporting Management vs TFS Branches Reconcilation

The MS Access database attached allows to reconcile DEV, UAT and PROD TFS Branches with SSRS reports deployed to the Reporting Management Service.  It requires to create a ODBC connection to ReportServer SQL Server PROD database and uses the Catalog table records to compare against.
The ReconcilePROD_ReportingServerManagementWithTFSBranches.accdb database availlable for download.

If you find this script useful please donate generously.

Extracting MS Active Directory Users

The following user defined function returns query against MS Active Directory:

USE active_directory
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 = 'implementation.ADusers')
        DROP FUNCTION implementation.ADusers
        PRINT 'DROP FUNCTION implementation.ADusers'
GO

PRINT 'CREATE FUNCTION implementation.ADusers'
GO

CREATE FUNCTION implementation.ADusers()
/************************************************************************************************************************************
AD users

Usage: EXEC sp_executesql @strSQL

Change Log
 #    Date       Who  Change
*************************************************************************************************************************************/ 
 RETURNS nvarchar(max)
 BEGIN
    DECLARE @strSQL1 AS varchar(600)
    ,       @strSQL2 AS varchar(max) 
    ,       @strSQL3 AS varchar(100)
    ,       @strSQL  AS nvarchar(max) 

    SET @strSQL1 = 'SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager
    ,CAST(CASE WHEN accountexpires = 0 or accountexpires = 0x7FFFFFFFFFFFFFFF then null
                    ELSE dateadd(day, (accountexpires / (1e7 * 60 * 60 * 24)), cast(''16000101'' as datetime2))
                END AS datetime
            ) FROM (
    '

    SELECT @strSQL2 = COALESCE(@strSQL2, ' ') + alpha_union.strSQL
    FROM (
    SELECT char(decimal#) alpha
    ,'SELECT  samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,NAME,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires = CAST(accountExpires AS bigint) 
        FROM OPENQUERY(active_directory,''SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires
        FROM ''''LDAP://OU=Organisation,DC=corporate,DC=com,DC=au''''
        WHERE    objectCategory = ''''Person'''' and    objectClass = ''''USER'''' and samaccountname = '
     + '''''' + CHAR(decimal#) + '*' + ''''''''
     + IIF(CHAR(decimal#) <> 'z',' 
    ) UNION ALL 
     ','')    strSQL
    FROM (select row_number() over (order by (select 1)) as decimal#
    FROM sys.all_columns) cte_tally
    WHERE decimal# > 96 and decimal# < 123) alpha_union

    SET @strSQL3 = (SELECT ')) t    
    WHERE t.distinguishedName LIKE ''%OU=Users%''')

    SET @strSQL = @strSQL1 + @strSQL2 + @strSQL3
    RETURN @strSQL
END

If you find this script useful please donate generously.

Extract SSRS reports objects

 The script below is:

·       Retrieving object names from Data Sources of SSRS reports stored as XML on Report Server database

·       Selecting these objects definitions from information schema of the desirable database

·       Searching a keyword in the report definition

;WITH
     XMLNAMESPACES
         (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
                 ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
          AS rd)
    ,DEF AS
        (SELECT RPT.ItemID
                ,RPT.Name
                ,RPT.ReportPath
                ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
                ,CommandText = REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)'))),'&gt;', '>'),'&lt;', '<')
         FROM (SELECT RPT.ItemID
                        ,RPT.Name
                        ,RPT.Path AS ReportPath
                     ,RPT.name AS ReportName
                     ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
               FROM ReportServer.dbo.[Catalog] AS RPT
               WHERE RPT.Type in (2, 5, 7, 8)
             ) AS RPT
         CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode))
    SELECT   DEF.Name ReportName
            ,DEF.ReportPath
            ,DEF.DataSetName
            ,DEF.CommandText
            ,o.OBJECT_NAME
            ,o.OBJECT_DEFINITION
    FROM DEF
        ,(select o.name as OBJECT_NAME
                , m.definition as OBJECT_DEFINITION
            from sys.sql_modules m
            inner join sys.objects o on o.object_id = m.object_id
            WHERE (o.type in ('P','V','TF','IF','FN')
            AND (m.definition Like '%fld_current%'
            OR m.definition Like '%originalcost%')
            )
    ) 

    WHERE DEF.CommandText like '%' + o.object_name + '%'

If you find this script useful please donate generously.