What are the challenges you face when working across database platforms? Take the survey

Invoke-DlmDatabaseSchemaValidation - object does not exist or is invalid for this operation

We are trying to do an upgrade of our CI process with a new server.
Our existing CI process that uses Jenkins with SQLCI works fine but is using an old version of DLM Automation.

At present we are trying to test a powershell script (as per documentation on this site) on the new server.
We are using a trial version of DLM Automation 2 until we have everything running and then we will transfer the licence over

Our existing SQLCI command in Jenkins is this (with asterisks replacing some characters for security):-
"C:\Program Files (x86)\Red Gate\DLM Automation Suite 1\SQLCI\SQLCI.exe" Build /scriptsFolder=. /packageId=NewCIPackage /temporaryDatabaseServer=******\TTS******TEST /packageVersion=2.%BUILD_NUMBER% /dlmDashboardHost=localhost /dlmDashboardPort=19528

The above build step works perfectly every time

We have translated the above SQLCI Build command (without the DLMDashboard element) to this:-
   $scriptsFolder = "D:\ManzenDatabaseTrunk"
   $packageID = "ManzenDatabase"
   $packageVersion = 0.1
   $targetServerInstance = "*****\TTS******TEST"
   $targetDatabaseValidate = "Blankdb"
   $username = "myuser"
   $password = "mypassword"   
   $errorActionPreference = "stop"    
   $temporaryDatabase = New-DlmDatabaseConnection -ServerInstance $targetServerInstance -Database $targetDatabaseValidate -Username $username -Password $password
   Test-DlmDatabaseConnection $temporaryDatabase
   $validatedSchema = Invoke-DlmDatabaseSchemaValidation $scriptsFolder -TemporaryDatabase $temporaryDatabase 

The scripts folder was populated with a fresh checkout from SVN

Upon executing the above powershell command we see the following error:-
Invoke-DlmDatabaseSchemaValidation : Schema validation failed: 416 batches failed. The first error is 'Could not find object '[dbo].[vw_GetHLAAntibodyTestResultDetails]' or you do not have permission.'.
Local parameters [connectionString = Data Source=*****\TTS******TEST;Initial Catalog=Blankdb;User ID=myuser;Password=********;Application Name="Redgate DLM Automation"]
At line:11 char:23
+ ... tedSchema = Invoke-DlmDatabaseSchemaValidation $scriptsFolder -Tempor ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (database 'Blank...\TTS******TEST':DatabaseConnection) [Invoke-DlmDatabaseSchemaValidation], TerminatingException
    + FullyQualifiedErrorId : SchemaValidationError,RedGate.DLMAutomation.PowerShell.Commands.InvokeDlmDatabaseSchemaValidationCommand

The script for [dbo].[vw_GetHLAAntibodyTestResultDetails] is in the scripts folder, it is not as though it isn't present.

Looking at the output from the powershell execution the first thing mentioned is this:-
WARNING: The error 'Could not find object '[dbo].[vw_GetHLAAntibodyTestResultDetails]' or you do not have permission.' occurred when executing the following SQL:
EXEC sp_refreshview N'[dbo].[vw_GetHLAAntibodyTestResultDetails]'

There follows a few of the above for different views.
We then get:-
WARNING: The error 'Cannot drop the index 'dbo.reftblLexiconCodes.IX_reftblLexiconCodes_LexiconTypeId', because it does not exist or you do not have permission.' occurred
 when executing the following SQL:
DROP INDEX [IX_reftblLexiconCodes_LexiconTypeId] ON [dbo].[reftblLexiconCodes]

We don't understand why a DROP INDEX is being executed on a database build?

We are also seeing messages output during the execution of the powershell script like this:-
WARNING: The error 'The object 'dbo.tblHLAAntibodyTestResultsDetail' does not exist or is invalid for this operation.' occurred when executing the following SQL:-
Code Comments eliminated for brevity
ALTER TRIGGER [dbo].[TRG_tblHLAAntibodyTestResultsDetail_Insert] ON [dbo].[tblHLAAntibodyTestResultsDetail]

We can't understand why an ALTER TRIGGER statement is being executed when the script for the table definition has CREATE TRIGGER and we're building a database from scratch.

Unless we can resolve these issues we'll be stuck on an old version of DLM Automation with no path forward.

We've spent two man days up till now trying to get this to work but to no avail and desperately need to get this working.
Getting DLM Automation1 to work with SQLCI was a doddle compared to this.


  • Options
    RedshiftRedshift Posts: 31 Bronze 3
    Having thought more about this overnight it appears that the powershell cmdlet is working on a migrations basis rather than a state basis.
    We've always gone down the state approach but seeing failures due to EXEC sp_refreshview, DROP INDEX and ALTER TRIGGER implies a migrations approach does it not.
    The thing is, none of the individual scripts in the scripts folder use those T-SQL commands so it must be the cmdlet that is doing something.
    When powershell commences execution the following appears in the execution window:-
    Invoke-DlmDatabaseSchemaValidation, DLM Automation, Copyright © Red Gate Software Ltd 2014-2018
    In trial: expires 2019/11/13 09:06:48 +00:00
    Validating project 'scripts folder 'D:\ManzenDatabaseTrunk''.
    Cleaning database 'Blankdb' on server 'TTS******TEST'

    My assumption is that 'Cleaning database ' is a process that ensures the db is blank before execution of any CREATE scripts for the individual objects.
    We know that following completion of the cmdlet that 'Blankdb' is still empty of any objects contained in the scripts folder but expect that as the whole idea is to create and then remove a scratch copy of the database from the scripts.

    We're still no closer to resolving this however.
  • Options
    DLM Automation 2 works similarly to SQL CI in the sense that it works with SQL Source Control (SOC)\SQL Compare (SC) Scripts Folder and creates a dynamic deployment script.
    SQL Change Automation (SCA) Powershell (the successor to DLM Automation 2) works the same way as well (meaning that it is fully compatible with SOC\SC scripts folders) but is also capable of working with SCA Authoring projects (SCA VS or SSMS) in which case it doesn't create dynamic deployment scripts but it deploys migrations.
    So this issue is not caused by a fundamental change in how DLM Automation 2 works.
    If you are upgrading from SQL CI I would advise you to bypass DLM Automation 2 and upgrade straight away to SCA, since that's the software that will support the most recent SQL Server features and benefit from the most recent bug fixes. I stress again that there are no fundamental differences in the way that SCA Powershell handles SOC projects when compared to DLM Automation 2. You can even use the same script you've created for DLM Automation 2 ( the cmdlets have changed names but kept the old names as aliases).
    I will troubleshoot this issue further on the support ticket you have opened.
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Options
    RedshiftRedshift Posts: 31 Bronze 3
    OK, I'll download SCA and try that
Sign In or Register to comment.