0

I have a database on my on-premise SQL Server (SQL Server 2019 Developer edition) that have many views and stored procedures that reference another database, so from database A I have

SELECT * 
FROM [DatabaseB].[dbo].[TableName]

Both extracting a data-tier application and deploying to Azure fails because of this.

UPDATE

Example Error Log Entry:

Error SQL71561: Error validating element [cp].[GL_Transactions_All]: View: [cp].[GL_Transactions_All] has an unresolved reference to object [DYNAMICS].[dbo].[MC40200].

How can I migrate both databases at the same time to my Azure SQL database?

Randy
  • 1,137
  • 16
  • 49
  • What is the actual error? – misha130 Jul 20 '21 at 04:46
  • 3
    You can't do cross DB in Azure SQL. You need to convert them to fit inside _one_ database in _two_ different schemas. – Nick.Mc Jul 20 '21 at 06:37
  • @misha130 I added an example Example Log Entry. – Randy Jul 20 '21 at 10:04
  • @Nick.McDermaid Merging and Elastic Queries are going to be too much. Do you know a way to strip or ignore cross quering objects from the first Database? – Randy Jul 20 '21 at 10:09
  • plz refer https://stackoverflow.com/questions/12161392/using-ssdt-how-do-i-resolve-sql71561-errors-when-i-have-a-view-that-references and let me know if this resolve your Error SQL71561. – Utkarsh Pal Jul 20 '21 at 12:28
  • @Nick.McDermaid in the same server? Never heard of this – misha130 Jul 20 '21 at 13:51
  • I’m not sure what you mean. – Nick.Mc Jul 20 '21 at 13:52
  • @UtkarshPal-MT I am not doing this in a Visual Studio SSDT Project. How do I add a database reference in SSMS? – Randy Jul 20 '21 at 14:25
  • Forget about database references. You simply can't have cross database references in Azure SQL. You can't add a database reference in SSMS. I'm also guessing you can't add a database reference in a SSDT project if the target is Azure SQL. People post guesses (I'm also guilty of this) and they shouldn't. All you can do is merge the two databases into one, using schemas. – Nick.Mc Jul 20 '21 at 22:58
  • In answer to "Do you know a way to strip or ignore cross querying objects from the first Database?". If you strip or ignore this, I assume your database isn't going to work properly. Elastic queries don't work with private endpoints and besides it's just a bad idea. Theoretically you should be able to do this with find/replace. It might be easiest to import into two database projects and do it that way. – Nick.Mc Jul 20 '21 at 23:02

1 Answers1

1

Unfortunately, you can't do cross DB in Azure SQL. You need to convert them to fit inside one database in two different schemas. I'm also guessing you can't add a database reference in a SSDT project if the target is Azure SQL.

All you can do is merge the two databases into one, using schemas. Refer: Transfer data from one database to another database

Utkarsh Pal
  • 4,079
  • 1
  • 5
  • 14