CODEWITHSUNDEEP
azureapiazure-sql-databasesqlclrsql-server-agent
Francesco Mantovani
Francesco Mantovani

Reputation: 12207

Make REST call to API and save the result to Azure SQL every hour

I'm using this very useful SQLCLR script to make a REST call to an API and save the data on SQL Server on the fly. I have created a stored procedure that withdraws new data every hour so my data are always updated. I would like to have all this on Azure so I can then create a Power BI data visualization.

THE PROBLEM:

As soon as I try to transfer the database on Azure I receive this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Could not import package.
Warning SQL0: A project which specifies SQL Server 2019 or Azure SQL Database Managed Instance as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.
Error SQL72014: .Net SqlClient Data Provider: Msg 40517, Level 16, State 1, Line 4 Keyword or statement option 'unsafe' is not supported in this version of SQL Server.
Error SQL72045: Script execution error.  The executed script:
CREATE ASSEMBLY [ClrHttpRequest]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103006D85475F0000000000000000E00022200B0130000026000000060000000000007E45000000200000006000000000001000200000000200000400000000000000060000000000000000A00000000200004C1E01000300608500001000001000000000100000100000000000001000000000000000000000002C4500004F00000000600000FC03000000000000000000000000000000000000008000000C000000F44300001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000084250000002000000026000000020000000000000000000000000000200000602E72737263000000FC030000006000000004000000280000000000000000000000000000400000402E72656C6F6300000C000000008000000002000000
 (Microsoft.SqlServer.Dac)

------------------------------
BUTTONS:

OK
------------------------------

enter image description here

This happens because Azure SQL has some feature stripped off like SQLCLR or SQL Server Agent (for some obvious security reason).

Upvotes: 1

Views: 1408

Answers (2)

Felix Schwenk
Felix Schwenk

Reputation: 11

Even though Azure Functions is great, you could even solve this without much code using Azure Logic Apps, a scheduled trigger, the http request and the mssql connector.

https://azure.microsoft.com/de-de/services/logic-apps/

Upvotes: 0

Peter Bons
Peter Bons

Reputation: 29711

I do not think there is a straight forward replacement for SQL CLR. However, there are some Azure offerings that might be interesting.

I suppose an alternative is using a scheduled azure function that calls the API and store the result in the Azure SQL Database.

Do mind that if the process takes longer than 10 minutes you cannot use a consumption plan for the Azure Function, which is the most cost effective probably.

Depending on the scenario, Azure Data Factory can also provide a solution. You can create a pipeline that calls the API and copies the data to Sql Server as outlined here, based on a schedule trigger.

Upvotes: 3

Related Questions