0

So I have an Azure SQL Database instance that I need to run a nightly data import on, and I was going to schedule a stored procedure to make a basic GET request against an API endpoint, but it seems like the OLE object isn't present in the Azure version of SQL Server. Is there any other way to make an API call available in Azure SQL Database, or do I need to put in place something outside of the database to accomplish this?

Improve this question
5
  • 1
    You should probably be avoiding the OLE procedures anyway, they are legacy and have many issues. Perhaps a Powershell Job is a better idea, see learn.microsoft.com/en-us/azure/azure-sql/database/… Commented Aug 19, 2021 at 19:01
  • Thank you! I'm looking over the link you provided and it doesn't seem like Elastic Jobs really provide a means to make an HTTP call which is what I was intending to use the OLE object for; they seem to be more oriented around automating / scheduling basic T-SQL stuff which is one element here, but the HTTP request ability is what's my main concern. For context, this is for a client that is glacial at getting services setup for the project we're working on so my strong preference would be if I can make the call in SQL as opposed to requesting them set up another Azure service to do it. Commented Aug 19, 2021 at 19:24
  • That said, if the only option here is to add another Azure service to the mix, what would be the best solution for the following scenario: 1. Nightly hitting 3 endpoints for a bulk import of records 2. On demand updating individual records on one of those 3 endpoints as needed. A record is a JSON object 100-200 characters in length and in bulk there's a few thousand of them. Would that be best done with something like an Azure Function or is there a better way to do that if I cannot achieve it in the Azure SQL Database instance they already have set up? Commented Aug 19, 2021 at 19:29
  • Elastic Jobs only support TSQL job steps. Commented Aug 19, 2021 at 19:50
  • Ah, ok. Thank you. I wanted to make sure I wasn't missing something there. Commented Aug 19, 2021 at 20:05

1 Answer 1

Reset to default
2

There are several options. I do not know whether a powershell job as stated in the first comment to your question can execute http requests but I do know at least a couple of options:

Azure Data Factory allows you to create scheduled pipelines to copy/transform data from a variety of sources (like http endpoints) to a variety of destinations (like azure sql databases). This involves no or a little bit of scripting.

Azure Logic Apps allows you to do the same:

With Azure Logic Apps, you can integrate (cloud) data into (on-premises) data storage. For instance, a logic app can store HTTP request data in a SQL Server database.

Logic apps can be triggered by a schedule as well and involves none or little scripting

You could also write an Azure Function that is executed on a schedule and calls the http endpoint and write the result to the database. Multiple languages are supported for writing functions, like c# and powershell for example.

All those options include the possibility to force an execution outside the schedule.

In my opinion Azure Data Factory (no coding) or an Azure Function (code only) are the best options given the need to parse a lot of json data. But do mind that Azure Functions on a Consumption Plan have a maximum execution time allowed of 10 minutes per invocation.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

learn.microsoft.com/en-us/powershell/module/…
Thank you for your help! I've mostly work in the Azure App Service side of things so my head was spinning a bit trying to figure out what the various services could offer in solving this problem.
@JamesCamp you're welcome. I updated my answer to tell about the possibility to execute them outside of the schedule.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.