Comments
Sort by recent activity
Unfortunately, we are also seriously considering abandoning SQL Source Control migration scripts. We were very excited when this feature was announced, but it has given us lots of pain and cost us a lot of time.
We really wanted this to work but alas... [image] / comments
Unfortunately, we are also seriously considering abandoning SQL Source Control migration scripts. We were very excited when this feature was announced, but it has given us lots of pain and cost us ...
Thanks for the feedback, Peter! / comments
Thanks for the feedback, Peter!
Saulcruz,
Seeing your post I tried to rack my brain for how we ended up solving the issue, but I'm sorry I drew a blank. :-( I would suspect we reverted to manual adjustments of the target database to work around the issue.
Sorry I can't give you a more exact answer than that - it's been a while. I'll post again if I remember more details.
/Mattias / comments
Saulcruz,
Seeing your post I tried to rack my brain for how we ended up solving the issue, but I'm sorry I drew a blank. :-( I would suspect we reverted to manual adjustments of the target database...
Thanks for the suggestion, Rob! I'll run a test with this (temporary!) configuration change. / comments
Thanks for the suggestion, Rob! I'll run a test with this (temporary!) configuration change.
Hi Robert,
Yes, I'm a longtime SQL Compare user. :-)
Following your instructions, I could extract the db directory from the NuGet package and, with SQL Compare, compare it with the target database. The target database didn't contain any of the changes from source control. This was expected since DLM Automation rolled back all changes in the failed deployment that prompted this discussion.
I then attempted to deploy the changes via SQL Compare. Interestingly, the deployment failed because the database user I provided in the connection string didn't have sufficient permissions to add new roles to the target database. When SQL Compare ran a new comparison after the deployment, it consequently found that there were roles in the source database that didn't exist in the target database. A sproc was missing as well. (This sproc is linked to one of the new database role. This explains why the sproc was omitted.)
At this point I thought we had found the root cause of the problem: insufficient user rights on the target database.
However, I then realized that I hadn't used the same user account in the SQL Compare project as in the failed deployment on Octopus Deploy / DLM Automation. Switching to the same (system administrator) account, SQL Compare was able to deploy all database objects.
Furthermore, shouldn't DLM Automation have reported an error if it was unable to deploy some database objects because of insufficient user rights? The log in Octopus Deploy didn't contain any messages indicating any errors.
To continue the troubleshooting, I think it would be of great help if I could temporarily tell DLM Automation not to roll back the changes in case of a failed deployment. Is this possible?
/Mattias / comments
Hi Robert,
Yes, I'm a longtime SQL Compare user. :-)
Following your instructions, I could extract the db directory from the NuGet package and, with SQL Compare, compare it with the target database....
Hi,
Thanks for the help!
I just double-checked my password variable values and scopes, and everything seems fine - but still no joy I'm afraid.
- Password variable value: I could successfully log in to the target db server from the Tentacle when I copy/pasted the correct password. I then pasted the same password to the variable definition in Octopus and created a new release. Result: Same error as in my original post. Hardcoding the password in the step configuration works fine, but of course that makes it rather cumbersome when you deal with multiple target db servers.
- Password variable scope: I use the same scoping rule for other variables, such as the SQL Server user, and I can confirm that Octopus picks up the correct value for the other variables. It's just the password variable that acts up.
What else could be wrong?
/Mattias / comments
Hi,
Thanks for the help!
I just double-checked my password variable values and scopes, and everything seems fine - but still no joy I'm afraid.
- Password variable value: I could successfully log ...
I forgot to mention that the same error occurs on the "Create Database Release" step. / comments
I forgot to mention that the same error occurs on the "Create Database Release" step.
Hello Robert,
I switched to a SQL Server instance for the temporary database. That resolved the issue for me.
Thank you for the help!
/Mattias / comments
Hello Robert,
I switched to a SQL Server instance for the temporary database. That resolved the issue for me.
Thank you for the help!
/Mattias
Hi Chris,
Thanks for the detailed and prompt response - much appreciated!
/Mattias / comments
Hi Chris,
Thanks for the detailed and prompt response - much appreciated!
/Mattias
Hi Richard,
Thanks for the info - much appreciated!
Yes, the reports that SQL Release generates seems really good to have! I will familiarize myself with them plus catch up on your blog post!
I have installed DLM Dashboard but truth be told I haven't played around much with it yet. Will certainly do so!
/Mattias / comments
Hi Richard,
Thanks for the info - much appreciated!
Yes, the reports that SQL Release generates seems really good to have! I will familiarize myself with them plus catch up on your blog post!
I hav...