SQL SERVER – Could Not Use View or Function ‘msdb.dbo.sysdac_instances’ Because of Binding Errors

This error is one of an interesting which appeared as soon as we right click on the database. It says Could Not Use View or Function ‘msdb.dbo.sysdac_instances’ Because of Binding Errors.

Due to this error, we were not able to see the properties of the database. Interesting this was happening only when we use the latest version of SQL Server Management Studio 2012. Which made me think that SSMS 2012 automatically checks this view before opening the ‘right click’ menu of a database.

SQL SERVER - Could Not Use View or Function 'msdb.dbo.sysdac_instances' Because of Binding Errors colla-err-01

We captured profile and found that the error was raised while using this view.

Solarwinds
CREATE VIEW [dbo].[sysdac_instances]
AS
    SELECT
        -- this must be locked down because we use instance_id visability as a security gate
        case 
            when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.instance_id
            when sd.owner_sid = SUSER_SID() then dac_instances.instance_id
            else NULL
        end as instance_id,
        dac_instances.instance_name,
        dac_instances.type_name,
        dac_instances.type_version,
        dac_instances.description,
        case 
            when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.type_stream
            when sd.owner_sid = SUSER_SID() then dac_instances.type_stream
            else NULL
        end as type_stream,
        dac_instances.date_created,
        dac_instances.created_by,
        dac_instances.instance_name as database_name
    FROM sysdac_instances_internal dac_instances
    LEFT JOIN sys.databases sd
              ON dac_instances.instance_name = sd.name

As per the above View we see join on “ON dac_instances.instance_name = sd.name”. Here sys.databases are coming from MASTER database and sysdac_instances is from MSDB database. We have queried sys.database and found the collation was different as seen below.

master:  Latin1_General_CI_AI

msdb: SQL_Latin1_General_CP1_CI_AS

Now we know the cause of the error. Later we identified that MSDB was restored from a different server, which caused this issue.

WORKAORUND/SOLUTION

There is no easy way to change collation of system databases. To rebuild the system databases for an instance of SQL Server we need to run setup.exe with below parameters.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Once completed, we restored the backups which we have taken before rebuilding system databases. We should take care of objects which are there in system databases before running rebuild command.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Jump in Identity Column After Restart
Next Post
SQL SERVER – The Lease between Availability Group ‘PRODAG’ and the Windows Server Failover Cluster has Expired

Related Posts

2 Comments. Leave new

  • Msg 207, Level 16, State 1, Procedure dm_exec_sessions, Line 12 [Batch Start Line 0]
    Invalid column name ‘identity_insert_object_name’.
    Msg 4413, Level 16, State 1, Line 53
    Could not use view or function ‘RpDatabase.sys.dm_exec_sessions’ because of binding errors.
    Hi Pinal,
    I am facing the above issue, tried to resolve by installing expression edition 2017 but still no luck on these. This issue persist even when creating new data base through Management studio. The above ‘RpDatabase’ created through c# code.

    Reply

Leave a Reply

Menu