Describe attributes of user defined function which resides on remote server using Link Service

The following procedure describes attributes of user defined function which resides on remote server using Link Service and stores it to ##temp table.

USE YourDB
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 = 'Describe_Remote_UDF_Attributes')
    drop procedure reporting.Describe_Remote_UDF_Attributes;
PRINT 'drop procedure reporting.Describe_Remote_UDF_Attributes';
GO

PRINT 'CREATE PROCEDURE reporting.Describe_Remote_UDF_Attributes';
GO

CREATE PROC reporting.Describe_Remote_UDF_Attributes
/*****************************************************************************
Description:  This procedure describes attributes of user defined function

 which resides on remote server using Link Service and stores it to ##temp table.

Change Log
 #    Date       Who  Change
*****************************************************************************/ 
       @runUDF            NVARCHAR(MAX)
      ,@LinkServiceName NVARCHAR(256)
AS
    BEGIN

            SET NOCOUNT ON;
            DECLARE  @strExe NVARCHAR(MAX) = 'sp_describe_first_result_set @tsql = N'''
                    ,@strExecAt  NVARCHAR(MAX);
                IF OBJECT_ID('tempdb..##temp') IS NOT NULL  DROP TABLE ##temp;

                CREATE TABLE ##temp (
                    is_hidden                            sql_variant
                    ,column_ordinal                        sql_variant
                    ,name                                sql_variant
                    ,is_nullable                        sql_variant
                    ,system_type_id                     sql_variant
                    ,system_type_name                   sql_variant
                    ,max_length                         sql_variant
                    ,[precision]                        sql_variant
                    ,scale                              sql_variant
                    ,collation_name                     sql_variant
                    ,user_type_id                       sql_variant
                    ,user_type_database                 sql_variant
                    ,user_type_schema                   sql_variant
                    ,user_type_name                     sql_variant
                    ,assembly_qualified_type_name       sql_variant
                    ,xml_collection_id                  sql_variant
                    ,xml_collection_database            sql_variant
                    ,xml_collection_schema              sql_variant
                    ,xml_collection_name                sql_variant
                    ,is_xml_document                    sql_variant
                    ,is_case_sensitive                  sql_variant
                    ,is_fixed_length_clr_type           sql_variant
                    ,source_server                      sql_variant
                    ,source_database                    sql_variant
                    ,source_schema                      sql_variant
                    ,source_table                       sql_variant
                    ,source_column                      sql_variant
                    ,is_identity_column                 sql_variant
                    ,is_part_of_unique_key              sql_variant
                    ,is_updateable                      sql_variant
                    ,is_computed_column                 sql_variant
                    ,is_sparse_column_set               sql_variant
                    ,ordinal_in_order_by_list           sql_variant
                    ,order_by_is_descending             sql_variant
                    ,order_by_list_length               sql_variant
                    ,tds_type_id                        sql_variant
                    ,tds_length                         sql_variant
                    ,tds_collation_id                   sql_variant
                    ,tds_collation_sort_id              sql_variant
                );

                SET @strExe = @strExe + '''SELECT * FROM ' + @runUDF + '''';
                SET @strExecAt = 'INSERT INTO ##temp EXEC (''' + @strExe + ''''') AT ' 

                    + @LinkServiceName;

                EXEC(@strExecAt);
    END;
/***********************************************
Test Sample:

EXEC reporting.Describe_Remote_UDF_Attributes 
    'YourSchema.dbo.YourUDF( 
                default            
                ,default            
                ,default        
                ,default        
                ,default            
                )'

    ,'LinkServiceName';

 SELECT * FROM     ##temp;
*************************************************/
GO

If you find this script useful please donate generously.

Dynamically transpose an object (Table or View) by swapping rows and columns around

The following PROC will dynamically transpose an object (Table or View) by swapping rows and columns around.

USE YourDB
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 = 'TransposeObject')
 drop procedure YourSchemaTransposeObject

PRINT 'drop procedure YourSchema.TransposeObject'
GO

PRINT 'CREATE PROCEDURE YourSchema.TransposeObject'
GO

CREATE PROCEDURE YourSchema.TransposeObject
  @SchemaName NVARCHAR(256)
 ,@ObjectName NVARCHAR(256)
/*********************************************************************************
 Description : Dynamically transpose an object (Table or View) by swapping rows and columns

 Requirements: The object (Table or View which name passed as a parameter to this PROC) must have
     the following structure:
 
 ObjectName  (RowNo INT
     ,Title NVARCHAR(256)
     ,YourFieldName ATTRIBUTE
     ...
     ,YourFieldName ATTRIBUTE)
 
 Limitations: This PROC will execute a serious INSERT SELECT and UPDATE SELECT statements.

     The size of these DML commands are limited by NVARCHAR(MAX).  The PROC perfomance will decrease with

     the number of the Object fields increase.  View aliases must not have single quotes.

 Modification History:

 Date    Description
 *********************************************************************************/

AS
 BEGIN
  BEGIN TRY
   SET NOCOUNT ON;

   DECLARE  @Value    NVARCHAR(256)
     ,@Counter   INT = 1
     ,@UnpivotRecs  INT
     ,@Rec    INT = 1
     ,@strSQLivotRecs INT
     ,@strSQL   NVARCHAR(MAX);

   IF OBJECT_ID('tempdb..##tblNoRecs') IS NOT NULL DROP TABLE ##tblNoRecs;
   CREATE TABLE ##tblNoRecs(NoRecs INT);

   SET @strSQL = 'INSERT INTO ##tblNoRecs SELECT COUNT(*) FROM ' + @SchemaName + '.' + @ObjectName + ';';

   EXEC (@strSQL);
   SELECT @UnpivotRecs = NoRecs  FROM ##tblNoRecs;
   IF OBJECT_ID('tempdb..##tblPivot') IS NOT NULL DROP TABLE ##tblPivot
   SET @strSQL = 'CREATE TABLE ##tblPivot(RowNo INT, Title sql_variant'
   WHILE @Counter <= @UnpivotRecs BEGIN
    SET @strSQL = @strSQL + ',Value'  + CAST(@Counter AS VARCHAR(8)) + ' sql_variant'
    SET @Counter = @Counter + 1;
   END;
   SET @strSQL = @strSQL + ');'

   EXEC (@strSQL);
   SET @Counter = 1;
   SET @strSQL = 'INSERT INTO ##tblPivot SELECT RowNo = ORDINAL_POSITION, Title = COLUMN_NAME'
   WHILE @Counter <= @UnpivotRecs BEGIN
    SET @strSQL = @strSQL + ',Value'  + CAST(@Counter AS VARCHAR(8)) + ' =  NULL'
    SET @Counter = @Counter + 1;
   END;
   SET @strSQL = @strSQL + ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @ObjectName + ''';'
   EXEC (@strSQL);
   SET @Counter = 2;
   SET @strSQLivotRecs = (SELECT COUNT(*) FROM ##tblPivot)

   WHILE @Counter <= @strSQLivotRecs BEGIN
    SET @strSQL = 'UPDATE ##tblPivot ';
    WHILE @Rec <= @UnpivotRecs BEGIN
     IF @Rec <> 1  SET @strSQL = @strSQL + ', '
     ELSE SET @strSQL = @strSQL + ' SET ';
     
     SELECT @Value = CAST(Title AS VARCHAR(255)) FROM ##tblPivot WHERE RowNo = CAST(@Counter AS VARCHAR(8));

     SET @strSQL = @strSQL + 'Value' + CAST(@Rec AS VARCHAR(8)) + ' = (SELECT ' + @Value + ' FROM ' +  @SchemaName + '.' + @ObjectName + ' WHERE RowNo = ' + CAST(@Rec AS VARCHAR(8)) + ')';
     SET @Rec = @Rec + 1;
    END;
    SET @Rec = 1;
    SET @strSQL = @strSQL + ' WHERE RowNo = ' + CAST(@Counter AS VARCHAR(8));
    SET @Counter = @Counter + 1;

    EXEC (@strSQL);
   END; 

   SET @strSQL = '';
   SELECT @strSQL = @strSQL + NAME + ','
   FROM  tempdb.sys.columns Where object_id=OBJECT_ID('tempdb.dbo.##tblPivot');
   SET @strSQL = SUBSTRING(@strSQL, CHARINDEX(',', @strSQL) + 1, LEN(@strSQL) - CHARINDEX(',', @strSQL) - 1 );
   SET @strSQL = 'SELECT FriendlyTitle = (SELECT K2_Ext.Training.SpaceBeforeCap(CONVERT(NVARCHAR(MAX), Title))), ' + @strSQL + ' FROM ##tblPivot WHERE Title <> ''RowNo''';
  

   EXEC (@strSQL);
ExitLabel:
  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 Sample:

SELECT * FROM YourSchema.YearlyFiguresUnpivot

EXEC YourDB.YourSchema.TransposeObject
 'Training'     -- @SchemaName
 ,'YearlyFiguresUnpivot'  -- @ObjectName
 

SELECT * FROMYourSchema.MonthlyFiguresUnpivot
EXEC YourDB.YourSchema.TransposeObject
 'Training'     -- @SchemaName
 ,'MonthlyFiguresUnpivot' -- @ObjectName
 
*********************************************************************************/
GO

The PROC calls the following UDF:

USE YourDB
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 = 'SpaceBeforeCap')
  DROP FUNCTION YourSchema.SpaceBeforeCap
  PRINT 'DROP FUNCTION YourSchema.SpaceBeforeCap'

GO

PRINT 'CREATE FUNCTION YourSchema.SpaceBeforeCap'
GO

CREATE FUNCTION YourSchema.SpaceBeforeCap
/*********************************************************************************
Inserts Space Before Cap

Change Log

 #    Date       Who  Change
*********************************************************************************/  (@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
 BEGIN
   DECLARE  @result NVARCHAR(MAX) = LEFT(@str, 1)
     ,@i  INT = 2;
   WHILE @i <= len(@str) BEGIN
  IF ASCII(SUBSTRING(@str, @i, 1)) BETWEEN 65 AND 90
   SET @result += ' ';
   SET @result += SUBSTRING(@str, @i, 1);
   SET @i += 1;
   END;
   RETURN @result;
 END;

/*********************************************************************************
Test Sample:

SELECT FriendlyTitle = YourDB.YourSchema.SpaceBeforeCap('ThisIsATestString')

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

If you find this script useful please donate generously.

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;

If you find this script useful please donate generously.

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 PROCYourSchema.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

If you find this script useful please donate generously.

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.