Skip to content
SQL CURL
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
Properties
.gitignore
Curl.cs
README.md
SqlClrCurl.csproj
SqlClrCurl.sln
SqlClrCurl.tt
license.txt

README.md

CURL in SQL Server using CLR

SQL Server Database Engine doesn't have built-in functions that would enable you to send information to some API using http:// protocol. If you need to call some REST endpoint or a web hook from the T-SQL code, you will need to use WebClient or WebRequest classes from .Net framework and expose them as a T-SQL function or procedure.

One of the most popular tools for calling an API on http: endpoints is curl. This code sample demonstrates how to create CLR User-Defined function/procedure that provides CURL-like functionalities in T-SQL.

This code exposes minimal CURL functionalities required for the basic demo purposes. If you need more advanced features, you can modify the code.

Contents

About this sample
Build the CLR/CURL extension
Add RegEx functions to your SQL database
Test the functions
Disclaimers
Appendix - quick install script for your dev environment.

About this sample

  1. Applies to: SQL Server 2005+ Enterprise / Developer / Evaluation Edition, Azure SQL Database (Managed Instance)
  2. Key features:
    • CLR
  3. Programming Language: T-SQL, .NET/C#
  4. Author: Jovan Popovic [jovanpop-msft]

Build the CLR/CURL functions

  1. Download the source code and open the solution using Visual Studio.

  2. Create a .pfx file (go to Project > Properties > Signing) to sign the assembly: Sign assembly

  3. Rebuild the solution in Retail mode.

  4. Open and save SqlClrCurl.tt to generate T-SQL file that will contain the script that inserts CLR assembly with the CURL functionalities, and exposes T-SQL/CLR functions.

Add CURL functions to your SQL database

File SqlClrCurl.sql contains the code that will import the CURL assembly into SQL Database.

If you have not added CLR assemblies in your database, you should use the following script to enable CLR:

sp_configure @configname=clr_enabled, @configvalue=1
GO
RECONFIGURE
GO

Once you enabled CLR, you can use the T-SQL script to add the CURL functions. The script depends on the location where you have built the project, and might look like:


--Create the assembly
CREATE ASSEMBLY SqlClrCurl
FROM 'C:\GitHub\sql-server-samples\samples\features\sql-clr\Curl\bin\Release\SqlClrCurl.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE SCHEMA CURL;
GO

--Create the function/procedure
CREATE FUNCTION CURL.XGET (@H NVARCHAR(MAX), @url NVARCHAR(4000))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrCurl.Curl.Get;
GO

CREATE PROCEDURE CURL.XPOST (@H NVARCHAR(MAX), @d NVARCHAR(MAX), @url NVARCHAR(4000))
AS EXTERNAL NAME SqlClrCurl.Curl.Post;
GO

This code will import a assembly in SQL Database and add one function and one procedure that provide CURL functionalities. Two modules are provided in this sample:

  • CURL.XGET - function that calls API on some http endpoint using get method, and fetches the response. It has two parameters:
    • @H representing the header information that should be sent to remote endpoint (null for none).
    • @url representing the Url endpoint where the Http request should be sent.
  • CURL.XPOST - procedure that sends text to some http endpoint using post method and prints response. It has three parameters:
    • @H representing the header information that should be sent to the remote endpoint (null for none).
    • @d representing the data that should be sent to remote endpoint in the request body.
    • @url representing the Url endpoint where the Http request should be sent.

In the assembly you can find an alternative version of Post procedure that will automatically retry Http request with 50ms delay if some error is returned by destination.

CREATE PROCEDURE CURL.XPOST (@H NVARCHAR(MAX), @d NVARCHAR(MAX), @url NVARCHAR(4000))
AS EXTERNAL NAME SqlClrCurl.Curl.PostWithRetry;
GO

Test the functions

Once you create the assembly, you can use CURL functionalities in T-SQL code. The following simple example gets the Microsoft earning from Investors Exchange ("IEX") API data REST API:

select curl.xget(null, 'https://api.iextrading.com/1.0/stock/msft/earnings')

The following example sends one event to Azure Event Grid.

declare @hkey nvarchar(200) = N'aeg-sas-key: 9CwFFHbPIwTPVEdXS+W7eMnuPk1/+pouIlhzf5=';
declare @body nvarchar(4000) = N'[{"id":"1807","eventType":"recordInserted","subject":"myapp/vehicles/motorcycles","eventTime": "2017-08-10T21:03:07+00:00","data": {"make": "Ducati","model":"Monster"},"dataVersion":"1.0","metadataVersion":"1"}]';
declare @endpoint nvarchar(1000) = N'https://test-event-grid.eventgrid.azure.net/api/events';

exec curl.XPOST @H = @hkey, @d = @body, @url = @endpoint;

Note: The code might return an error if your firewall/networking rules don't allow access to targeted Url. Security and configuring access right in your networks is beyond the scope of this sample.

Disclaimers

The code included in this sample is not intended to be a set of best practices on how to build scalable enterprise grade applications. This is beyond the scope of this sample.

Apendix

In order to quickly test the function in your dev environment, you can create an assembly using the following script that imports the assembly from binary format. Recommendation is to take the source code, compile it, and execute the script shown above.

sp_configure @configname=clr_enabled, @configvalue=1
GO
RECONFIGURE
GO

EXEC sp_add_trusted_assembly 0xF9251BA2BBA78E1462F07ABBF45216B6FB3C8EE702940066607C5BAE76147DBDA9A325A0989B5AA45B5BF26EFEF7A2229C8B8DB17D2518BDFC7E18EFC78806D7
GO

CREATE ASSEMBLY [SqlClrCurl]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004D0BD75A0000000000000000E00022200B013000001000000008000000000000AA2E00000020000000400000000000100020000000020000040000000000000006000000000000000080000000020000719A0000030060850000100000100000000010000010000000000000100000000000000000000000582E00004F000000004000002C04000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B00E0000002000000010000000020000000000000000000000000000200000602E727372630000002C040000004000000006000000120000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000008C2E000000000000480000000200050044220000940B000009000000000000000000000000000000D82D0000800000000000000000000000000000000000000000000000000000000000000000000000133002003100000001000011731200000A0A0206280400000606036F1300000A0B1201281400000A281500000A6F1600000A6F1700000A731800000A2A000000133003006300000002000011731200000A0A02062804000006036F1900000A2C1072010000707271000070731A00000A7A06046F1300000A0C1202281400000A281500000A036F1300000A0C1202281400000A6F1B00000A0B281C00000A727700007007281D00000A6F1E00000A2A001B300500D500000003000011731200000A0A02062804000006036F1900000A2C1072010000707271000070731A00000A7A7E010000040B72A10000700C0006046F1300000A0D1203281400000A281500000A036F1300000A0D1203281400000A6F1B00000A0C150BDE5D1304281C00000A1B8D10000001251672A3000070A2251711046F1F00000AA2251872B3000070A225197E020000048C1D000001A2251A72C9000070A2282000000A6F1E00000A0717590B7E02000004282100000ADE0007163D76FFFFFF07153315281C00000A727700007008281D00000A6F1E00000A2A00000001100000000032002C5E005617000001133002002C00000004000011026F1900000A2D23026F1300000A0B1201281400000A0A06282200000A2D0C036F2300000A066F2400000A2A1E02282500000A2A3A1980010000041F3280020000042A0042534A4201000100000000000C00000076342E302E33303331390000000005006C0000006C030000237E0000D80300001404000023537472696E677300000000EC070000D400000023555300C0080000100000002347554944000000D0080000C402000023426C6F620000000000000002000001571502000900000000FA013300160000010000001F0000000200000002000000060000000B0000002500000012000000040000000100000003000000000095020100000000000600870163030600060263030600900031030F00830300000600B800C10206006A01C10206003601C1020600ED01C1020600A701C1020600D201C1020600E500C1020600A400440306008200440306001901C102060000013F020600C303BA020A00550109030A00C00109030A00A70392030E00D903CE030A00870292030A00CF0009030600EF02BA020E009102BA0206008A02BA020600E702BA020A00EF0309030A007A00090306000100BA02060042002E020E00D302CE03000000000700000000000100010001001000B50200004100010001003100210033013100120033015020000000009600CA03360101009020000000009600EA033F010400002100000000960006043F010700F421000000009100FF0249010A002C22000000008618240306000C0034220000000091182A0351010C0000000000000000000100100000000200B60200000100100000000200500000000300B60200000100100000000200500000000300B60200000100100000000200E303090024030100110024030600190024030A00290024031000310024031000390024031000410024031000490024031000510024031000590024031000610024031500690024031000710024031000790024031000890024030600910024030600B10024030600A10024030600990085022100A90024022600C10075022A00A10059022F00C900FA0334009900240339009900A4024700D10024034B00A10068025100D90071005700C900BC035C00E1004D001000B90065002600C900BC036D00F100F9027300C90052007E00A100B0038300F9004900100081002403060020007B00A90224008300AE022E000B0055012E0013005E012E001B007D012E00230086012E002B0096012E003300D8012E003B00DE012E004300ED012E004B000C022E005300D8012E005B00D8012E00630024022E006B004E022E0073005B0240008B00A90260008B00A9021A003F0062007800048000000100000000000000010000009100AF020000040000000000000000000000880039000000000004000000000000000000000088002D00000000000400000000000000000000008800BA0200000000000000496E743332003C4D6F64756C653E00480044454C41595F4F4E5F4552524F520052455452595F434F554E540053797374656D2E44617461006D73636F726C696200546872656164004164640053656E640049734E756C6C4F7257686974655370616365006765745F4D657373616765006765745F506970650053716C5069706500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C654174747269627574650053716C50726F63656475726541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F647563744174747269627574650053716C466163657441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053797374656D2E546872656164696E670053797374656D2E52756E74696D652E56657273696F6E696E6700446F776E6C6F6164537472696E670055706C6F6164537472696E6700457363617065557269537472696E6700546F53716C537472696E67005572690053716C436C724375726C2E646C6C006765745F49734E756C6C0053716C436C724375726C0053797374656D0053797374656D2E5265666C656374696F6E00576562486561646572436F6C6C656374696F6E00417267756D656E74457863657074696F6E00536C65657000416464486561646572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053716C4368617273006765745F4865616465727300436F6E636174004F626A656374004765740053797374656D2E4E657400576562436C69656E7400636C69656E7400506F73740053716C436F6E7465787400546F43686172417272617900506F737457697468526574727900006F59006F00750020006D007500730074002000730070006500630069006600790020006400610074006100200074006800610074002000770069006C006C002000620065002000730065006E007400200074006F002000740068006500200065006E00640070006F0069006E007400000540006400002952006500710075006500730074002000690073002000650078006500630075007400650064002E000001000F4500720072006F0072003A00090000152E002000570061006900740069006E006700200000076D0073002E0000000000210EB3B0DCE94F419AB644C8C813459F00042001010803200001052001011111042001010E04200101020607021251115504200011550320000E0400010E0E0420010E0E0420001D03052001011D0307070312510E115503200002052002010E0E0520020E0E0E04000012710500020E0E0E0A07051251080E1155125D0500010E1D1C04000101080507020E1155040001020E042000127D08B77A5C561934E08980A00024000004800000940000000602000000240000525341310004000001000100594568514168B07173B4258E3B5DCAC316DAE3F3C0C01DDFDBC628534C3F8D1A4F378F74CDD858CDB90804D5A862842BC650F764CBD8EC01C5C69A9EC1BFA90012AC45AC19B8198569A6E06671977DE65FC6939CFC20366252416CFFEA1DEDBB035134658E4C113C92CBA6CB1D32233F033102809E95F0D16B0C1E25E0352CB6020608080002124D124D124D09000301124D124D124D07000201124D1251030000010801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000000F01000A53716C436C724375726C00004101003C496D706C656D656E746174696F6E206F66204355524C20636F6D6D616E6420696E2053514C2053657276657220446174616261736520456E67696E6500000501000000000E0100094D6963726F736F667400001E01001953716C20536572766572204769744875622053616D706C6573000017010012436F7079726967687420C2A920203230313800002901002432363565306263332D616435662D343466332D626231372D63363166373762393834376600000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E362E310100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E362E31040100000012010001005408074D617853697A65FFFFFFFF0000009B5D68E6D8B6CFDE25C606C2D49B6121BF8910B0A157824E3480F21D4099D140407F550D1F59CEB3A7F9F37EF533CD3B4AE28FF95D91461162B6B93719086F3FE7500E27DC21295E10C95B5B95A68F4EF3A9D61C05D877ACEBD3BA038CFF6A1633DB4B9D64A1ACC2BACBD480901472A5D0DA9859F8BD5E298BD8143B8A29A25F802E000000000000000000009A2E00000020000000000000000000000000000000000000000000008C2E0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00300000000000000000000D00334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430030000010053007400720069006E006700460069006C00650049006E0066006F0000000C030000010030003000300030003000340062003000000092003D00010043006F006D006D0065006E0074007300000049006D0070006C0065006D0065006E0074006100740069006F006E0020006F00660020004300550052004C00200063006F006D006D0061006E006400200069006E002000530051004C002000530065007200760065007200200044006100740061006200610073006500200045006E00670069006E0065000000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003E000B000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0043006C0072004300750072006C0000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003E000F00010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C0072004300750072006C002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100380000002A00010001004C006500670061006C00540072006100640065006D00610072006B007300000000000000000046000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C0072004300750072006C002E0064006C006C000000000054001A000100500072006F0064007500630074004E0061006D00650000000000530071006C00200053006500720076006500720020004700690074004800750062002000530061006D0070006C00650073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000AC3E00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE SCHEMA CURL;
GO

--Create the function/procedure
CREATE FUNCTION CURL.XGET (@H NVARCHAR(MAX), @url NVARCHAR(4000))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrCurl.Curl.Get;
GO
CREATE PROCEDURE CURL.XPOST (@H NVARCHAR(MAX), @d NVARCHAR(MAX), @url NVARCHAR(4000))
AS EXTERNAL NAME SqlClrCurl.Curl.Post;
GO

You can’t perform that action at this time.