SQL Server - Call a URL using GET or POST method and fetch Status Code and Response Text
We already talked about the MSXML2.XMLHTTP and MSXML2.ServerXMLHTTP objects in an older post: these objects provides methods and properties for server-safe HTTP access between different Web servers and can be used to exchange data between different servers or services. In a typical SQL Server scenario, the MSXML2.ServerXMLHTTP is used to issue HTTP or HTTPS calls to external URIs to send and receive data. If you've stumbled upon this post, you're most likely looking for that: a way to execute GET or POST requests to remote servers using the HTTP or HTTPS protocol within a T-SQL Stored Procedure. Without further ado, here's the code snippet you're looking for: DECLARE @status int DECLARE @responseText as table(responseText nvarchar(max)) DECLARE @res as Int; DECLARE @url as nvarchar(1000) = 'https://www.google.it' EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @res OUT EXEC sp_OAMethod @res, 'open', NULL, 'GET',@url,'false' EXEC sp_OAMethod @res, 'send' EXEC sp_OAGetProperty @res, 'status', @status OUT INSERT INTO @ResponseText (ResponseText) EXEC sp_OAGetProperty @res, 'responseText' EXEC sp_OADestroy @res SELECT @status, responseText FROM @responseText The above code sample has been specifically designed to overcome a known bug in the MSXML2.XMLHTTP and MSXML2.ServerXMLHTTP objects: they both cannot handle more than 8000 characters for their output handlers. This basically mean that you wouldn't be able to get a Response Text bigger than 8000 characters with a nvarchar(MAX) variale such as the following one: DECLARE @responseText as nvarchar(MAX) If you try to do that, you'll run into an error or get an empty string. To fix that, the above method creates a "temporary table variable" instead, who is then used to store the response text: such workaround does not trigger any internal errors and therefore allows the actual storage of a response text of any length.
XMLHTTP vs ServerXMLHTTP
XMLHTTP is designed for client applications and relies on URLMon, which is built upon Microsoft Win32 Internet (WinInet). ServerXMLHTTP is designed for server applications and relies on a new HTTP client stack, ServerXMLHTTP offers reliability and security and is server-safe. For more information, see the MSXML Software Development Kit (SDK) documentation. As the name suggests, ServerXMLHTTP is recommended for server applications and XMLHTTP is recommended for client applications. XMLHTTP has some advantages such as caching and auto-discovery of proxy settings support. It can be used on Windows 95 and Windows 98 platforms, and it is well-suited for single-user desktop applications. There are other advantages in using ServerXMLHTTP, such as the following: Data can be exchanged between local and remote systems as a stream or as XML documents. Because the underlying protocol is HTTP or HTTPS, data can be exchanged between the systems that are behind firewalls. Can also be used to send HTTP requests from different environments such as Active Server Pages (ASP), Microsoft Visual Basic, and Microsoft Visual C++.
HTTP and HTTPS
Both ServerXMLHTTP and XMLHTTP components have limited HTTPS support in MSXML3. Specifically, they do not fully support Secure Sockets Layer (SSL) certificates, which are used for authentication. The components do support the HTTPS protocol, but the request fails if the server requires a client certificate. However, ServerXMLHTTP includes SSL certificate support since MSXML 3.0 Service Pack 1, which is a built-in package in most modern Windows and Windows Server distributions: that's why you should always use it for server-based tasks.
ServerXMLHTTP limitations
The number of instances of ServerXMLHTTP that can exist simultaneously within a single process primarily depends upon the amount of memory available for applications on the system. However, other factors, such as CPU processing capacity, or available socket connections can further limit the number of instances that can be active simultaneously. With MSXML 3.0, the maximum number of instances that can exist simultaneously within a single process is 5,460.
Conclusions
That's about it: we sincerely hope that this short guide will help other developers and system administrators to better understand the MSXML2.XMLHTTP and MSXML2.ServerXMLHTTP objects. Read the full article
