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

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s