Thursday, July 27, 2017

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.

Thursday, July 13, 2017

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.

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;
 
If you find this script useful please donate generously.

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

Tuesday, May 16, 2017

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.

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.
If you find this script useful please donate generously.