Journal Table not recognizing

[raochandansingh]

Hi,

Recently I install DB up Migration task in Azure-DevOps for SQL scripts deployment. But I am getting an issue while executing script due to invalid object name 'dbo._schemaVersions' for Journal table.

Steps were taken:-

1. Cross verified the user permission already in the database which is using to execute the scripts.
2. If I run manually the same script it does execute.

I am adding Task screenshots and error one. Please help as this issue already taken my 2 days.

[johanclasson]

Thanks for reaching out!

My guess is that the user which runs the scripts (It looks like you are running on-premice, and then it is the user account which runs the agent windows service.) fails to check if the the _SchemaVersions table exists, or fails to create it.

If that is true, then your issue would be resolved by granting some additional permissions. I just made a quick google and found this example.

[raochandansingh]

Thanks for replying, really appreciated. I hope the _schemaVersion Journal table should be created by DB Up migration task automatically to maintain the logs script which already runs. I checked on the database this table is not created yet ( guess failing to create it)

More context:- we are migrating tool TFS to Azure-DevOps. The same user is working in TFS for this task but not with Azure-DevOps (targeting same databases).

[johanclasson]

Your hopes are right. DbUp will attempt to create the _SchemaVersion Journal table. :)

I do not think there would be a difference agent-wise between Azure DevOps Server (a.k.a TFS) and Azure DevOps. Are you perhaps running the new agents with a different service account?

[raochandansingh]

Agents running with the same account. I am attaching one more SS if which helps to debug.

Logs say table created but while checked on the database it's not there?

[johanclasson]

I see you have single transaction selected. That might explain why you do not nee the _SchemaVersion table after the SLAByServiceModel.sql script fails with dbo._SchemaVersion having an invalid object name.

You do not happen to drop the _SchemaVersion table in the SLAByServiceModel.sql script?

[raochandansingh]

This is a simple Test script create a table only, there no drop for _SchemaVersion. Also, I tried with other transaction types (No transaction, Transaction per script) but have the same issue :(

[johanclasson]

Try and remove the USE [Blank] and all rows related to transactions, and try again. DbUp will manage the transaction stuff for you!

[raochandansingh]

Thanks, John for your help, extremely you are very supportive.

My issue is resolved, I don't know what's changed in DB Up 2.0 version this time I have to give admin permission for agent user on the particular database where the script is executing but earlier 1.0 version it was working fine.

One last another thing I want to check, if I have multiple DB scripts using different-2 DBs which need to be run in one time on target DBs for one DB server so how multiple DBs will provide in the single connection string ( or it will switch DB based on a script?), please see below

[johanclasson]

Sorry no, you will have to use one task per DB even though they are on the same server. See for example this issue in the DbUp-repo for details.

[raochandansingh]

Ok, Thanks!!

[raochandansingh]

This is possible using DB Up Task, we can create ( pre-create) one physical table in one database which DB giving connecting string into DBUp task and another database will have synonyms of this table.

I tried it and it's working fine for me for multiple DBs

[johanclasson]

So you are using a connection string stored in the first database to run commands on a second database? For example with sp_addlinkedserver or similar? Why would you want to do something like that?