Options
Invoke-DlmDatabaseSchemaValidation - object does not exist or is invalid for this operation
Redshift
Posts: 31 Bronze 3
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:-
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:-
There follows a few of the above for different views.
We then get:-
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
*/
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.
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:-
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 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]
AFTER INSERT
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.
Tagged:
Answers
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:-
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.
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