Visible Thinking

Background

Early this year, we had a day set aside entirely for team building.

There are so many things to share when a group locks itself away from his normal working environment into hopefully a nice settling off campus.

 

What was shared?

Making thinking visible

How do we make our thinking visible.  And, how to encourage others, likewise  ….

Here is what the facilitator shared:

  • Make your own thinking visible

    • Share opinions
    • Provide reasoning
    • State disagreement
  • Help others make their thinking visible
    • Seek information from others
    • Ask others for their reasoning
    • Examine Others
    • Ask others to state disagreement
  • Help move group to agreement 
    • Summarize agreement and disagreement
    • Recommend compromise
    • Check/Poll for agreement
    • Establish next steps

 

Apology

Again, these are not my words, but someone else.

I am openly sharing it in hope that it help others.

 

World Mission Society Church of God

Background

Yesterday coming home on Bart, I ran into a couple of young men speaking about the word.

One of the young men was not a congregant, and was a fellow commuter as I.

The other young man was.

Later other young man from the WMSCG came around.

It seems to me that “World Mission Society Church of God” has blanketed the Bay Area with these young men.

 

Believe System

Here is some of what this Church believes and preaches:

  • This church believes in God the Father and God the Mother
  • 2nd Coming – Ahnsahnghong (  http://usa.watv.org/truth/truth_life/content_secondcoming.asp )
    • In the age of the Holy Spirit of what name should we become witness,what name should we pray, and what name has the authority to give us Salvation – ahnsahnghong ( John 5:39 )
  • All other Churches are false
  • Need for new baptism
  • God’s Intent in creation ( http://usa.watv.org/truth/truth_life/content_mother.asp )
    • God’s intent in creating Adam was to reveal the second coming Jesus, our heavenly father.
      And, eve was created to reveal the second coming Jesus’s Wife, our heavenly mother.

Bible Verse Reference

Here are some of the verses used by the World Worship Church.

  • Genesis 1:26 ( Let us create man in our image )
  • Galatians 4:26 ( But the Jerusalem that is above is free, and she is our mother )
  • John 5:39

Rebuttal

Queen of Heaven

There are specific verses that forbid worshiping the “Queen of Heaven”.

Jeremiah, the weeping Prophet

Evil in the Land
http://www.esvbible.org/Jeremiah%207:17-19/

Do you not see what they are doing in the cities of Judah and in the streets of Jerusalem?
“The children gather wood, and the fathers kindle the fire, and the women knead dough to make cakes for the queen of heaven; and they pour out drink offerings to other gods in order to spite Me.
“Do they spite Me?” declares the LORD. “Is it not themselves they spite, to their own shame?”…

Judgement for Idolatory
http://www.esvbible.org/Jeremiah+44%3A1-19/

Then all the men who knew that their wives had made offerings to other gods, and all the women who stood by, a great assembly, all the people who lived in Pathros in the land of Egypt, answered Jeremiah:

“As for the word that you have spoken to us in the name of the Lord, we will not listen to you.

But we will do everything that we have vowed, make offerings to the queen of heaven and pour out drink offerings to her, as we did, both we and our fathers, our kings and our officials, in the cities of Judah and in the streets of Jerusalem. For then we had plenty of food, and prospered, and saw no disaster.

But since we left off making offerings to the queen of heaven and pouring out drink offerings to her, we have lacked everything and have been consumed by the sword and by famine.”

And the women said, “When we made offerings to the queen of heaven and poured out drink offerings to her, was it without our husbands’ approval that we made cakes for her bearing her image and poured out drink offerings to her?”

Silent Second Coming

Jesus Words

The Abomination of Desolation
http://biblehub.com/matthew/24-23.htm

Unless those days had been cut short, no life would have been saved; but for the sake of the elect those days will be cut short. 
Then if anyone says to you, ‘Behold, here is the Christ,’ or ‘There He is,’ do not believe him. 
For false Christs and false prophets will arise and will show great signs and wonders, so as to mislead, if possible, even the elect.…

Paul’s Word

The Great Apostacy
https://biblia.com/books/nkjv/2Th2
Now, brethren, concerning the coming of our Lord Jesus Christ band our gathering together to Him, we ask you, 
not to be soon shaken in mind or troubled, either by spirit or by word or by letter, as if from us, as though the day of Christ had come. 
Let no one deceive you by any means; for that Day will not come unless the falling away comes first, and the man of sin is revealed, the son of perdition, 
who opposes and exalts himself above all that is called God or that is worshiped, so that he sits as God in the temple of God, showing himself that he is God.

Additional Viewing and Reading

Video

Second Coming

Active Directory Query using ADSI/VBScript

Background

Microsoft’s Active Directory is likely one of the most widely deployed LDAP Server.

Every Application needs User authentication, but unfortunately the code can be written so deep in the Application that the error code and messages gets lost from suppression.

Thankfully, ADSI makes quick work of it.

Here is Microsoft’s definition of ADSI:

ADSI
https://msdn.microsoft.com/en-us/library/aa772170%28v=vs.85%29.aspx

Active Directory Service Interfaces (ADSI) is a set of COM interfaces used to access the features of directory services from different network providers. ADSI is used in a distributed computing environment to present a single set of directory service interfaces for managing network resources. Administrators and developers can use ADSI services to enumerate and manage the resources in a directory service, no matter which network environment contains the resource.

 

Code

Here is a code that I am trying out to quickly get a subset of the logged-on user’s data:


REM ******************************************************************************************************************************
REM *
REM
REM REM AD Query Script
REM http://www.rlmueller.net/ADOSearchTips.htm

REM Rahul Soni's blog
REM Never assume the obvious is true!
REM http://blogs.msdn.com/b/rahulso/archive/2006/12/29/how-to-read-user-and-system-variables-using-vbscript.aspx

REM ******************************************************************************************************************************

Option Explicit

Const USERDOMAIN = "USERDOMAIN"
Const USERNAME = "USERNAME"

Const USERDOMAIN_EXPAND = "%USERDOMAIN%"
Const USERNAME_EXPAND = "%USERNAME%"

Const USER_SYSTEM = "SYSTEM"

Const bDebug = false

Dim adoCommand, adoConnection, strBase, strAttributes
Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strCN
Dim strLog

Dim objWSH
Dim objUserVariables
Dim objSystemVariables

Dim objWshNetwork 

Dim strFilterSyntax
Dim strFilter

Dim strGivenName
Dim strSurname
Dim strUsername
Dim strUserDomain
Dim strEmailAddress
Dim strTelephoneNumber
Dim strDepartment
Dim strEmployeeID
Dim strDistinguishName

Dim bErrorCheckFailed
Dim strWhenCreated

Dim strFullDN
Dim ou

on error goto 0

strUserName = ""
bErrorCheckFailed = false

if (Wscript.Arguments.Count < 1)  Then

	rem Wscript.Echo "No arguments"

elseif (Wscript.Arguments.Count = 1) Then

	strUserName = Wscript.Arguments.Item(0) 

end if

Set objWSH =  CreateObject("WScript.Shell")

Set objUserVariables = objWSH.Environment("USER")
Set objSystemVariables = objWSH.Environment("SYSTEM")

	Rem http://stackoverflow.com/questions/904739/can-i-pick-up-environment-variables-in-vbscript-wsh-script
	strUserDomain = objSystemVariables(USERDOMAIN)

	if (strUserDomain = "") Then

		Rem http://stackoverflow.com/questions/904739/can-i-pick-up-environment-variables-in-vbscript-wsh-script
		strUserDomain = objWSH.ExpandEnvironmentStrings(UserDomain_EXPAND)

	end	if	

	if (strUserName = "") Then

		strUserName = objSystemVariables(USERNAME)

	end if

	if (strUserName = "") Then

		strUserName = objWSH.ExpandEnvironmentStrings(UserName_EXPAND)

	end	if	

set objWSH =  Nothing

if  ( _
		        (strUserDomain = "") _
		   or ( (strUserName = "") or (strUserName = USER_SYSTEM)) _
	) Then

	' Create a new WshNetwork object to access network properties.
	'http://wsh2.uw.hu/ch11b.html
	Set objWshNetwork = WScript.CreateObject("WScript.Network")

		if (strUserDomain = "") Then

			strUserDomain = objWshNetwork.UserDomain

		end if

		if ( (strUserName = "") or (strUserName = USER_SYSTEM) ) Then

			strUserName = objWshNetwork.UserName

		end if		

	Set objWshNetwork = Nothing

end if

if (strUserDomain = "") Then

	Wscript.Echo "User Domain is empty"
	bErrorCheckFailed = true

end if

if (bErrorCheckFailed) Then

	Wscript.Echo "Validity Check failed!"

	rem http://ss64.com/vb/quit.html
	WScript.Quit -100

end if

strLog = "User Domain: " & strUserDomain
Wscript.Echo strLog

strDNSDomain = strUserDomain

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")

strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on user objects.
strFilterSyntax = "(&(objectCategory=person)(objectClass=user)(sAMAccountName=#USERSAMNAME#))"
strFilter = Replace(strFilterSyntax, "#USERSAMNAME#", strUsername)

rem Wscript.Echo "FILTER SYNTAX: " & strFilterSyntax
rem Wscript.Echo "FILTER:        " & strFilter

' Comma delimited list of attribute values to retrieve.
strAttributes = "distinguishedName,sAMAccountName,cn,givenname,sn,mail,telephoneNumber,department,employeeID,whenCreated,lastLogon,lastLogoff,mailNickname"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

Dim strLastLogon
Dim strLastLogoff
Dim strMailNickname

' Enumerate the resulting recordset.
on error resume next

strDistinguishName = ""

Do Until adoRecordset.EOF

    ' Retrieve values and display.
    strName = adoRecordset.Fields("sAMAccountName").Value
    strCN = adoRecordset.Fields("cn").value
	strDistinguishName =  adoRecordset.Fields("distinguishedName").value

	strGivenName = adoRecordset.Fields("givenname").value
	strSurname = adoRecordset.Fields("sn").value
	strEmailAddress = adoRecordset.Fields("mail").value
	strTelephoneNumber = adoRecordset.Fields("telephoneNumber").value
	strDepartment = adoRecordset.Fields("department").value
	strEmployeeID = adoRecordset.Fields("employeeID").value
	rem whenCreated
	'strWhenCreated = adoRecordset.Fields("whenCreated").value
	'strLastLogon = adoRecordset.Fields("lastLogon").value
	'strLastLogoff = adoRecordset.Fields("lastLogoff").value
	strMailNickname = adoRecordset.Fields("mailNickname").value

    Wscript.Echo "NT Name: " & strName
	Wscript.Echo "Common Name: " & strCN
	Wscript.Echo "Distinguish Name (dn): " & strDistinguishName
	Wscript.Echo "Given Name: " & strGivenName
	Wscript.Echo "Last Name: " & strSurname
	Wscript.Echo "Email Address: " & strEmailAddress
	Wscript.Echo "Telephone Number: " & strTelephoneNumber
	Wscript.Echo "Department: " & strDepartment
	Wscript.Echo "Employee ID: " & strEmployeeID
	Wscript.Echo "When Created: " & strWhenCreated
	'Wscript.Echo "Last Logoff: " & strLastLogoff
	'Wscript.Echo "Last Logon: " & strLastLogon
	Wscript.Echo "Mail Nickname: " & strMailNickname

    ' Move to the next record in the recordset.
    adoRecordset.MoveNext
Loop

if (Err.Number <> 0) Then

	Wscript.Echo "Err Number " & CSTR(Err.Number)
	Wscript.Echo "Err Description " & CSTR(Err.Description) 

end if

Dim objUser
Dim objLastLogon
Dim objLastLogoff

Dim intLastLogonTime
Dim intLastLogoffTime

if (strDistinguishName <> "") then

	'Set ou = GetObject("LDAP://ou=Accounts,dc=cerrotorre,dc=de")

	'For Each obj In ou
		'WScript.Echo obj.name
	'Next

	strFullDN = "LDAP://" & strDistinguishName

	if (bDebug) Then

		WScript.Echo "Connecting to " & strFullDN & " ..."

	end if

	Set objUser = GetObject(strFullDN)

	if (Err.Number <> 0) Then

		Wscript.Echo "Err connecting to LDAP - Targeted " & strFullDN
		Wscript.Echo "Err Number " & CSTR(Err.Number)
		Wscript.Echo "Err Description " & CSTR(Err.Description) 

	end if

	if ( (objUser is Nothing) = false) Then	

		Set objLastLogon = objUser.Get("lastLogon")
		intLastLogonTime = objLastLogon.HighPart * (2^32) + objLastLogon.LowPart
		intLastLogonTime = intLastLogonTime / (60 * 10000000)
		intLastLogonTime = intLastLogonTime / 1440

		Set objLastLogoff = objUser.Get("lastLogoff")

		intLastLogoffTime = 0
		if ((objLastLogoff is Nothing) = False) Then

			intLastLogoffTime = objLastLogoff.HighPart * (2^32) + objLastLogoff.LowPart
			intLastLogoffTime = intLastLogoffTime / (60 * 10000000)
			intLastLogoffTime = intLastLogoffTime / 1440

		end if

		WScript.Echo "Last Logon is " & intLastLogonTime + #1/1/1601# 

		if (intLastLogoffTime <> 0) Then

			WScript.Echo "Last Logoff is " & intLastLogoffTime + #1/1/1601# 

		end if		

	else

			WScript.Echo "ou is empty for " & strFullDN 	

	end if

	if ( (objUser is Nothing) = false) Then	

		if (bDebug) Then

			WScript.Echo "Releasing User object ..."

		end if

		Set objUser = Nothing

		if (bDebug) Then

			WScript.Echo "Released User object"

		end if

	end if

end if

on error goto 0

' Clean up.
adoRecordset.Close
adoConnection.Close

Output:

ADQueryUser.vbs

InfoPath – This form cannot be opened in a Web Browser

Background

User clicked on a linked to an Infopath Document, but no go.

Here is the error message:

Textual:

This form cannot be opened in a web browser.  To open this form, use Microsoft Info Path.
Correlation ID: c018c730-866f-4740-8abd-c4524577db85

Image:

ThisFormCannotBeOpenedInAWebBrowserDetailed

 

Solution

IM Thread

Crediting Mr. Akshay.  He is with our Corporate Tech Support.

We did not even talk, started an IM thread with our support group, and he was the one that picked up my request.

Once I told him what I wanted and sent him an email of the error message, he came back right at me.

Here is a snippet of the IM Thread:

Textual:

Please add [domain-name] to your compatibility View settings in IE.

Image:

IM_CompatibilityViewSettingsInIE

 

Steps

  • Launch Internet Explorer
  • Access System Menu
  • From the menu, choose “Compatibility View settings”
  • In the “Compatibility View Settings” window, add the root web sites

 

Internet Explorer – System Menu

SystemMenuCleanedup

 

Compatibility View Settings

ChangeCompatibilityViewSettings

 

 

 

 

 

TFS – Client – Remap Workspace

Background

Messed up my computer while trying to free up space and now my favored TFS hooks are no longer showing up in Windows Explorer.

Stumbled

So here I am, so mad at myself for removing files and folders, I had no business removing.

Wish I could blame it on Maniac Monday, but it happened very early Tuesday morning.

Error Message

Made a fool of myself, looking for a quick fix.

Here are some of the errors, I was getting.

Already Exists

Text:


The workspace app; DBLAB\dadeniji already exists on computer compdan.

Image:

WorkspaceAlreadyExistsCleanedup

Already in use

Textual:


The workspace [workspace]; [username] already exists on computer [computer-name]

Image:

WorkspaceAlreadyExistsCleanedup

Solution

So collected myself and addressed it the better way:

  • Using MS Windows Programs and Features, repaired TFS Client related applications
    • Team Explorer for Visual Studio 2013
    • Microsoft Visual Studio Team Foundation Server 2013 Power Tools – Update 2
  • Issued TFS Client tf “tf workspaces /format:detailed” to concisely identify my workspaces and corresponding folders
  • Issued TFS Client tf “tf workspace -new [workspace] -collection [tfs-web]” to remap workspace

Repaired Applications

repairedApps

Identify Workspaces and Folders

   cd "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE"
   tf workspaces /format:detailed /server:http://tfs.tfs.com:8080/tfs/collection 

Output:


===============================================================================

Workspace : DB
Owner : DBLAB\dadeniji
Computer : compdaniel
Comment :
Collection : tfs.tfslab.com\Collection
Permissions: Public
Location : Server
File Time : Current

Working folders:
$/: C:\Personal\DanielAdeniji\Work\tfslab\Projects\myapp\Code\Database

===============================================================================

Remap Workspace


tf.exe workspace -new DB -collection:http://tfs.tfslab.com:8080/tfs/collection

Listening

Listening to 2 of my homies.  Wale is DC. And, J cole is Fayetteville, NC.

But, everytime I call home, my fam reminds me.

Wale – The Pessimist (feat. J. Cole)
https://www.youtube.com/watch?v=upliEvVc-GM

Somehow, no matter what, Kanye’s lyrics on Homecoming is some of the most introspective ever.

HomeComing Lyrics
http://genius.com/Kanye-west-homecoming-lyrics

 Like the nice, sweet Diabetics, Heart clogging pretzels, they sell at the mall these days, no matter how you twist it, you can still relate.

Transact SQL – Finding Programmable Objects Binding Errors through Object Refresh

Background

Occasionally, after a bout of heavy Transact SQL changes, programmable object loses their bearing.

It might be renamed columns, deleted columns, dropped views and tables.

It can also be argument to Table Value Functions, Stored Procedures, etc.

 

Hope

We are not all together without hope.

In the old days, we can run sp_refreshview against Views.

Since v2005, we can run sp_refreshsqlmodule against a wider gamut of programmable objects.

 

What is sp_refreshsqlmodule?

Microsoft has a goo definition for sp_refreshsqlmodule …

https://msdn.microsoft.com/en-us/library/bb326754.aspx
Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.

 

Sample Code

I have pasted a sample Stored Procedure below.

It takes in a schema name and iterates the list of objects in that schema and refreshes each of them.

 


use [DBLAB]
go

set noexec off
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

if schema_id('dbutil') is null
begin

	print 'Creating schema [dbutil] ... '

	exec('create schema [dbutil];')

	print 'Created schema [dbutil]'

end
go

if object_id('[dbutil].[usp_recompileProgrammableObjects]') is null
begin

	exec('create procedure [dbutil].[usp_recompileProgrammableObjects] as Select 1/0 as [shell] ')

end
go

ALTER PROCEDURE [dbutil].[usp_recompileProgrammableObjects]
(
      @schemaName sysname = null
    , @scriptOnly bit = 1
    , @debug bit =1
)
AS

BEGIN 

    set nocount on

    DECLARE @strModuleName  varchar(300)

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    declare @ErrorNumber int
    declare @ErrorLine	 int
    declare @ErrorProcedure sysname
    declare @strLog         varchar(300)

    declare @CHAR_STAR	    char(1)
    declare @CHAR_STARS	    varchar(255)
    DECLARE @CHAR_CRLF varchar(30)
    DECLARE @CHAR_TAB  varchar(30)
    DECLARE @CHAR_TAB_2 varchar(30)
    DECLARE @CHAR_TAB_3 varchar(30)
    DECLARE @CHAR_TAB_4 varchar(30)

    declare @tblObject table
    (
          [id] int not null identity(1,1)
        , [objectID] int not null
        , [objectType] sysname not null
        , [objectTypeLiteral] sysname not null
        , [schema] sysname not null
        , [object] sysname not null

    )

    declare @id int
    declare @idMax int

    declare @objectID           int
    declare @objectType         sysname
    declare @objectTypeLiteral  sysname
    declare @schemaNameInternal sysname
    declare @objectName         sysname

    declare @objectIDTooling    int
    declare @objectNameFull     sysname

    declare @sqlStatement       varchar(600)

    declare @OBJECT_TYPE_VIEW      varchar(60) = 'V'
    declare @OBJECT_TYPE_SP        varchar(60) = 'P'
    declare @OBJECT_TYPE_TRIGGER   varchar(60) = 'TR'

    begin try

        /*
            Get the Object ID of the running object
        */
         set @objectIDTooling = @@PROCID

         set @CHAR_TAB = char(9)
         set @CHAR_TAB_2 = char(9) + char(9)
         set @CHAR_TAB_3 = char(9) + char(9) + char(9)
         set @CHAR_TAB_4 = char(9) + char(9) + char(9) + char(9)

         set @CHAR_CRLF = char(13) + char(10)

         set @CHAR_STAR = '*'
         set @CHAR_STARS = replicate(@CHAR_STAR, 80)

         insert into @tblObject
         (
              [objectID]
            , [objectType]
            , [objectTypeLiteral]
            , [schema]
            , [object]
         )
        select
                  tblO.object_id
                , tblO.[type]
                , tblO.type_desc
                , schema_name(tblO.schema_id)
                , tblO.[name]

        from   sys.objects tblO

        where  tblO.schema_id != schema_id('sys')

        and    tblO.schema_id = case
                                    when @schemaname is not null then schema_id(@schemaName)
                                    else tblO.schema_id
                                end

        and    tblO.object_id != @objectIDTooling
        /*
            S --> System Object
            U --> User Table
            PK --> Primary Key
            D --> Default
            F --> Foreign Key
            SQ --> Service Queue
            UQ --> User Queue
            IT --> Internal Table
        */
        and    tblO.type not in ('S', 'U', 'PK', 'D', 'SQ', 'UQ', 'IT', 'F')

        /*
            Nicely exclude schema bound objects
        */
        and  OBJECTPROPERTY ( tblO.object_id,'IsSchemaBound') = 0

        set @id = 1
        set @idMax = ( select max([id]) from @tblObject )

	set @strLog = ''
			+ @CHAR_TAB
			+ 'Cycling through '
                        + cast(@idMax as varchar) + ' objects ...'

	print @strLog

        while (@id <= @idMax)
        begin

                select
                           @objectID = [objectID]
                         , @objectType =  [objectType]
                         , @objectTypeLiteral = [objectTypeLiteral]
                         , @schemaNameInternal = [schema]
                         , @objectName = [object]

                from  @tblObject

                where  [id] = @id

                set @objectNameFull = QUOTENAME(@schemaNameInternal) + '.' + QUOTENAME(@objectName)

                set @sqlStatement = null

                if (@objectType = @OBJECT_TYPE_VIEW)
                begin

                    --sp_refreshview [ @viewname = ] 'viewname'
                    set @sqlStatement = 'exec sp_refreshview  @viewname = ' + ' ''' + @objectNameFull +''' '            

                end
                /*
                     select distinct [TYPE], [TYPE_DESC]
                     from   sys.objects
                     where [Type_desc] like '%Function%'
                */
                else if
                        (
                               (@objectType = @OBJECT_TYPE_SP)
                            or (@objectType = @OBJECT_TYPE_TRIGGER )
                            or (@objectType in ('FN', 'TF', 'IF'))
                        )
                begin

                    --sys.sp_refreshsqlmodule
                    set @sqlStatement = 'exec sys.sp_refreshsqlmodule  @name = ' + ' ''' + @objectNameFull +''' '            

                end
                else
                begin

		     set @strLog = ''
			              + @CHAR_TAB_2
				      + 'Skipping Object - ' + @objectNameFull
                                      + ' Type ' + @objectType

		     print @strLog

                end

                if (@sqlStatement is not null)
                begin

                    if (@debug = 1)
                    begin

			  set @strLog = ''
			                + @CHAR_TAB_2
					+ @sqlStatement

			  print @strLog

                    end

                    if (@scriptOnly = 0)
                    begin

                        begin try

                            exec(@sqlStatement)

                        end try
                        begin catch

			   SELECT @ErrorNumber = ERROR_NUMBER();
			   SELECT @ErrorSeverity = ERROR_SEVERITY();
			   SELECT @ErrorState = ERROR_STATE();
			   SELECT @ErrorProcedure = ERROR_PROCEDURE();
			   SELECT @ErrorLine = ERROR_LINE();
			   SELECT @ErrorMessage = ERROR_MESSAGE();

			   set @strLog = ''
			                + @CHAR_TAB_3
			                + @CHAR_STARS

			   print @strLog

			   set @strLog = ''
			               + @CHAR_TAB_3
				       + ' Error Number ' + cast(@ErrorNumber as varchar(80))
				       + ' Error Severity ' + cast(@ErrorSeverity as varchar(80))
				       + ' Error State ' + cast(@ErrorState as varchar(80))
                                       + @CHAR_CRLF
                                       + @CHAR_TAB_3
				       + ' Error Procedure ' + @ErrorProcedure
				       + ' Error Line ' + cast(@ErrorLine as varchar(80))
				       + @CHAR_CRLF
                                       + @CHAR_TAB_3
				       + ' Error Message ' + @ErrorMessage

			     print @strLog

			     set @strLog = ''
				               + @CHAR_TAB_3
				               + @CHAR_STARS

			      print @strLog

                        end catch

                    end

                end

                set @id = @id + 1

        end

    end try

    begin catch

	SELECT @ErrorNumber = ERROR_NUMBER();
	SELECT @ErrorSeverity = ERROR_SEVERITY();
	SELECT @ErrorState = ERROR_STATE();
	SELECT @ErrorProcedure = ERROR_PROCEDURE();
	SELECT @ErrorLine = ERROR_LINE();
	SELECT @ErrorMessage = ERROR_MESSAGE();

	set @strLog = ''
			+ @CHAR_TAB_3
			+ @CHAR_STARS

	print @strLog

	set @strLog = ''
			+ @CHAR_TAB_3
			+ ' Error Number ' + cast(@ErrorNumber as varchar)
			+ ' Error Severity ' + cast(@ErrorSeverity as varchar)
			+ ' Error State ' + cast(@ErrorState as varchar)
			+ ' Error Procedure ' + cast(@ErrorProcedure as varchar)
			+ ' Error Line ' + cast(@ErrorLine as varchar)
			+ ' Error Procedure ' + cast(@ErrorProcedure as varchar)
			+ ' Error Message ' + cast(@ErrorMessage as varchar)

	print @strLog

	set @strLog = ''
			+ @CHAR_TAB_3
			+ @CHAR_STARS

	print @strLog

	RAISERROR (
			@ErrorMessage, -- Message text.
		      , @ErrorSeverity, -- Severity.
		      , @ErrorState -- State.
		  );

    END CATCH

END -- MODULE END

GO

 

Test Code

Here is a bit of unit test code:


    declare @schemaName sysname = null
    declare @scriptOnly bit = 0
    declare @debug bit =1

    -- set @schemaName = null
    set @schemaName = 'dateutil'

    exec dbutil.[usp_recompileProgrammableObjects]
         @schemaName = @schemaName
       , @scriptOnly = @scriptOnly
       , @debug = @debug

Errors

Even though, I personally think the code above is nice, there are things that are not “kosher“.

The ones that I will list here are:

  • We are unable to recompile a function bounded to by a computed column
  • Schema Bound Functions are off limit, as well

 

Object Referenced by a computed column


   Error Number 3729 Error Severity 16 Error State 3
   Error Procedure sp_refreshsqlmodule_internal Error Line 71
   Error Message Cannot ALTER 'dblab.ufn_getPersonFullName' because it is being referenced by object 'person'.

Sample Code


   CREATE TABLE [dblab].[person]
   (
	[Person_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[firstname] [nvarchar](60) NOT NULL,
	[lastname] [nvarchar](60) NOT NULL,
	[dateofBirth] [datetime] NULL,
	[fullname]  AS ([dblab].[ufn_getPersonFullName]([firstname],[lastname])),
	[age]  AS ([dblab].[ufn_getPersonAge]([dateofBirth])),
	[fullname_persisted]  AS ([dblab].[ufn_getPersonFullName_persisted]([firstname],[lastname])) PERSISTED
  ) ON [PRIMARY]

 

Schema Bound Scaler Functions

Error you will get if you try to refresh Schema Bound Scaler function.


    Error Number 15165 Error Severity 16 Error State 1
    Error Procedure sp_refreshsqlmodule_internal Error Line 55
    Error Message Could not find object '[dblab].[ufn_getPersonFullName_persisted]' or you do not have permission.

Please keep in mind that this is not such a big problem:

  • Schema bounding an object places constraints on what you can do with the specific object and the objects that relies on it; and so very little need to run to refresh it in the first place

 

Code Sharing

As always, Blogs are not so good for sharing code.

An easier to use copy of the code above and the breaking code is available @ https://github.com/DanielAdeniji/TransactProgrammableObjectsReCompile

Microsoft Connect Items

As mentioned above, there are about half a baker’s dozen of bugs filed against sp_refreshSQLModule.

I will suggest you review the list, if you run into a country ditch trying to use the SP.

I finally got around to filling one this morning:

Here are a couple of relevant items:

 

I expect the ticket that I opened, Ticket # 1341891, to be closed as duplicate.

sp_refreshsqlmodule vs sp_recompile

What is the difference between sp_refreshsqlmodule and sp_recompile?

  • While sp_refreshsqlmodule checks to ensure that the underlying objects that our object references are in still in place, sp_compile is more geared towards the object’s query plan
  • sp_refreshsqlmodule is immediate. On the other hand, sp_compile drops current query plan and thus forces the engine to prepare a new plan, the next time the object is referenced

 

Listening to

Listening to …

Striking Matches – “When The Right One Comes Along”
https://www.youtube.com/watch?v=JsrHWC5lhqQ