Managed Instance - Any alternative to sp_OACreate ?

MS Prog 471 Reputation points
2021-10-29T05:17:39.377+00:00

Hi,
We are in the process of migrating our on-prem SQL Server to cloud Azure Managed Instance.
One of our current stored-proc uses OLE Automation sp_OACreate to create WinHTTP.WinHTTPRequest.5.1 object.

Basically, We have a portal running and this stored proc uses sp_OACreate to check if the portal url is working or not.
if not, the sp calls DBMail to alert the support team. This sp is invoked via a Sql Agent job.

I see that OLE Automation is not supported in Azure MI which means I can't use sp_OACreate .
Has anyone else faced this issue? What alternatives are available that can be looked at please?

Thanks in advance

Azure SQL Database
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-10-29T09:35:49.837+00:00

    @MS Prog Thank you for reaching out.

    As you have rightly identified, OLE Automation is not available on Azure SQL Managed Instance.

    For you use case, you can use Azure Data Factory with a HTTP Connector to call an external service/website via HTTP
    Refer to the documentation here for step-by-step instructions

    To send emails from an ADF pipeline, refer to the instructions here

    ----------

    If an answer is helpful, please click on 130616-image.png or upvote 130671-image.png which might help other community members reading this thread.

    0 comments