SqlRS: There is already an object named 'ExecutionLog' in the database.

[PavelPikat]

Details of the scenario you tried and the problem that is occurring

Tried running the following example on freshly installed SSRS 2019 on Azure SQL VM (Windows Server 2019):

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlRS 'DefaultConfiguration'
        {
            InstanceName                 = 'SSRS'
            DatabaseServerName           = 'localhost'
            DatabaseInstanceName         = 'MSSQLSERVER'
            ReportServerVirtualDirectory = 'ReportServer'
            ReportsVirtualDirectory      = 'Reports'
            ReportServerReservedUrl      = @('https://+:443')
            ReportsReservedUrl           = @('https://+:443')
            UseSsl = $true
        }
    }
}

The operation is not idempotent meaning that it will fail the second time with:

There is already an object named 'ExecutionLog' in the database.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : localhost
 
Cannot create index on view 'ExecutionLog' because the view is not schema bound.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : localhost

I can see that the ReportServer DB created by the resource contains 2 Views:

Verbose logs showing the problem

ssrs_dsc_log.txt

Suggested solution to the issue

Check whether ExecutionLog view already exists

The DSC configuration that is used to reproduce the issue (as detailed as possible)

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlRS 'DefaultConfiguration'
        {
            InstanceName                 = 'SSRS'
            DatabaseServerName           = 'localhost'
            DatabaseInstanceName         = 'MSSQLSERVER'
            ReportServerVirtualDirectory = 'ReportServer'
            ReportsVirtualDirectory      = 'Reports'
            ReportServerReservedUrl      = @('https://+:443')
            ReportsReservedUrl           = @('https://+:443')
            UseSsl = $true
        }
    }
}

SQL Server edition and version the target node is running

Microsoft SQL Server 2019 (RTM-CU5) (KB4552255) - 15.0.4043.16 (X64) Jun 10 2020 18:25:25 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

SQLPS 15.0

The operating system the target node is running

OsName : Microsoft Windows Server 2019 Datacenter OsOperatingSystemSKU : DatacenterServerEdition OsArchitecture : 64-bit WindowsVersion : 1809 WindowsBuildLabEx : 17763.1.amd64fre.rs5_release.180914-1434 OsLanguage : en-US OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

PSVersion 5.1.17763.1007
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.1007
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Version of the DSC module that was used

SqlServerDsc 14.2.0

[johlju]

@PavelPikat did it correctly initialize the Reporting Services on the first run?

The resource seem to think it didn't. 🤔

RBOSE: [%computer_name%]:                            [[SqlRS]DefaultConfiguration] Reporting services localhost\MSSQLSERVER are not initialized.
VERBOSE: [%computer_name%]:                            [[SqlRS]DefaultConfiguration] The value for using SSL are not in desired state. Should be 'True', but was ''.

[PavelPikat]

Im not sure if it did it correctly, but the first run did create SSRS databases. I switched to 2017 version later on and it worked like a charm from the first go. Was this resource tested against 2019?

[stale[bot]]

This issue has been automatically marked as stale because it has not had activity from the community in the last 30 days. It will be closed if no further activity occurs within 10 days. If the issue is labelled with any of the work labels (e.g bug, enhancement, documentation, or tests) then the issue will not auto-close.

[johlju]

This resource is not integrated tested against SQL Server 2019 yet so there might be that there are changes in SQL Server 2019 that prevent it from working.

[johlju]

We can keep this open until the community has added an integration test for SQL Server 2019.

[James-Randal]

Can confirm it doesn't work on PowerBI Reporting Services, sigh

[brunogr84]

Is there any update on this issue? Have someone found a work around it?

Thanks

[blakedrumm]

Anyone find a fix?

[johlju]

A similar error might happen when SqlRS is run on an instance that has was not previously correctly configured. This error was seen in integration tests for SQL 2022 when trying to reconfigure an previously failed installation to SSL.

Expected no exception to be thrown, but an exception "PowerShell DSC resource DSC_SqlRS  failed to execute Set-TargetResource functionality with error message: System.Management.Automation.MethodInvocationException: Exception calling "ExecuteNonQuery" with "1" argument(s): "ExecuteNonQuery failed for Database 'master'. "
---> Microsoft.SqlServer.Management.Smo.FailedOperationException: ExecuteNonQuery failed for Database 'master'. 
---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch.
---> Microsoft.Data.SqlClient.SqlException: There is already an object named 'ExecutionLog' in the database.

Since the previous integraiton test failed with unknown random error `PowerShell DSC resource DSC_SqlRS failed to execute Set-TargetResource functionality with error message: The parameter is incorrect." it left tables that was created by the failed instance (and there is no functionality for rollback). Trying to reconfigure the instance (that failed to install completely) results in it trying to re-configure it from scratch.