0

I am new to SQL Azure, we have taken the subscription of Azure SQL elastic pool. I have two databases in my local SQL Server and it has a stored procedure which internally is calling msdb.dbo.sp_send_dbmail and I have scheduled these stored procedures to run in a job (SQL Server Agent -> Scheduler).

I want to move these two databases along with DB-mail accounts and SQL Server jobs to Azure. I am finding difficulties as in my subscription I am not able to see msdb, SQL Server Agent and database email. We want to go with only SQL Azure as service, no need your support on these.

I tried to deploy the database and getting the error message:

Error
SQL71562: Error validating element [dbo].[AMPLoanAgingEMail]:
Procedure: [dbo].[AMPLoanAgingEMail] has an unresolved reference to object [msdb].[dbo].[sp_send_dbmail].[@profile_name]. External references are not supported when creating a package from this platform.

The expected result is I want to move those scheduled jobs and stored procedures to my Azure.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
junior
  • 15
  • 1
  • 5
  • @LeonYue it's an unfortunate fact that posters often ask questions then never respond to your detailed answers. – Nick.Mc May 08 '19 at 22:51
  • As already stated in the answer, this is not supported by Azure SQL. It _is_ supported by Azure SQL _Managed Instance_ so you may want to look into that. Not sure about the email procs though – Nick.Mc May 08 '19 at 22:52

1 Answers1

0

Azure SQL database doesn't support msdb database. We can not migrate msdb to Azure or do anything with it in Azure SQL database.

enter image description here

For more details, please see: msdb database.

SQL server agent is only supported Azure SQL Managed Instance only. enter image description here

Database email also is not supported in Azure SQL Database. enter image description here

This why you can not be able to see msdb , SQL server agent and database email.

Your stored procedure [dbo].[AMPLoanAgingEMail] could not call the [msdb].[dbo].[sp_send_dbmail] in msdb. The error message also mentioned that.

For now, there isn't a solution for msdb in Azrue SQL Database.

Before migrate your database to Azure SQL database, you can use Data Migration Assistant(DMA) to help you assess your database.

Hope this helps.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Leon Yue
  • 15,693
  • 1
  • 11
  • 23