dsccommunity / dsccommunity/SqlServerDsc

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

Open
#1,605 9 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

help wanted tests
Dominant language
PowerShell
Stars
385
Forks
224
PR merge metrics
No merged PRs in 30d

Description

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:
image

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

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the SqlRS DSC resource and reproduce the configuration in the issue, using the attached ssrs_dsc_log.txt for failure details. Verify the first and second applications against the ReportServer database; done means repeated application is idempotent and does not report a duplicate ExecutionLog object or an invalid index on the view.

Written by the indexing model from the issue text.

Assessment

Tech stack
powershell, sql
Domain
databases, devops
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.