application/json SQL Server

Question

Hi Developers

so upon research i have managed to find a code that execute the JSON data and tries to upload it to the web API

however i am receiving this message

"{"Message":"An error has occurred."}"

Here is the below Code

CREATE PROCEDURE API @URL varchar(8000),
@Method varchar(5) = 'POST',--POST
@BodyData varchar(8000) = NULL,--normally json object string : '{"key":"value"}',
@Authorization varchar(8000) = NULL,--Basic auth token, Api key,...
@ContentType varchar(255) = NULL--'application/json; charset=utf-8' --'application/xml'
AS
BEGIN
SET NOCOUNT ON;

DECLARE @vWin int --token of WinHttp object  
DECLARE @vReturnCode int   
DECLARE @tResponse TABLE (ResponseText nvarchar(max))  

--Creates an instance of WinHttp.WinHttpRequest  
--Doc: https://learn.microsoft.com/en-us/windows/desktop/winhttp/winhttp-versions  
--Version of 5.0 is no longer supported  
EXEC @vReturnCode = sp_OACreate 'WinHttp.WinHttpRequest.5.1',@vWin OUT  
IF @vReturnCode <> 0 GOTO EXCEPTION  

--Opens an HTTP connection to an HTTP resource.  
--Doc: https://learn.microsoft.com/en-us/windows/desktop/winhttp/iwinhttprequest-open  
EXEC @vReturnCode = sp_OAMethod @vWin, 'Open', NULL, @Method/*Method*/, @Url /*Url*/, 'false' /*IsAsync*/  
IF @vReturnCode <> 0 GOTO EXCEPTION  
IF @Authorization IS NOT NULL  
BEGIN  
	EXEC @vReturnCode = sp_OAMethod @vWin, 'SetRequestHeader', NULL, 'Authorization', @Authorization  
	IF @vReturnCode <> 0 GOTO EXCEPTION  
END  

IF @ContentType IS NOT NULL  
BEGIN  
	EXEC @vReturnCode = sp_OAMethod @vWin, 'SetRequestHeader', NULL, 'Content-type', @ContentType  
	IF @vReturnCode <> 0 GOTO EXCEPTION  
END  

--Sends an HTTP request to an HTTP server.  
--Doc: https://learn.microsoft.com/en-us/windows/desktop/winhttp/iwinhttprequest-send  
IF @BodyData IS NOT NULL  
BEGIN  
	EXEC @vReturnCode = sp_OAMethod @vWin,'Send', NULL, @BodyData  
	IF @vReturnCode <> 0 GOTO EXCEPTION  
END  
ELSE  
BEGIN  
	EXEC @vReturnCode = sp_OAMethod @vWin,'Send'  
	IF @vReturnCode <> 0 GOTO EXCEPTION  
END  

IF @vReturnCode <> 0 GOTO EXCEPTION  

--Get Response text  
--Doc: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-oagetproperty-transact-sql  
INSERT INTO @tResponse (ResponseText)   
EXEC @vReturnCode = sp_OAGetProperty @vWin,'ResponseText'  
IF @vReturnCode <> 0 GOTO EXCEPTION  

IF @vReturnCode = 0   
	GOTO RESULT  

EXCEPTION:  
	BEGIN  
		DECLARE @tException TABLE  
		(  
			Error binary(4),  
			Source varchar(8000),  
			Description varchar(8000),  
			HelpFile varchar(8000),  
			HelpID varchar(8000)  
		)  

		INSERT INTO @tException EXEC sp_OAGetErrorInfo @vWin  
		INSERT  
		INTO	@tResponse  
		(  
				ResponseText  
		)  
		SELECT	(   
					SELECT	*  
					FROM	@tException  
					FOR		JSON AUTO  
				) AS ResponseText  
	END  

--FINALLY  
RESULT:  
--Dispose objects   
IF @vWin IS NOT NULL  
	EXEC sp_OADestroy @vWin  

--Result  
SELECT	*   
FROM	@tResponse  

RETURN  

END

Answer 1

If you really want to mess with this, first write an application in C++, VB or what ever that uses the COM interface so that you have that part working.

But sp_OAxxxxx is very old stuff, and I definitely recommend against it. Whether you should do web requests from SQL Server can be disputed. It is certainly not the core business of SQL Server. But in the system I work with, we actually run all REST requests through SQL Server, but we use the CLR for the purpose. That is definitely cleaner than sp_OAxxxxx.

Answer 2

Hi @Ronald Van Der Westhuizen ,

Thank you so much for posting here.

It is recommened that stored procedures should handle data and the processing thereof on the SQL Server. Retrieving remote data from a website in a store procedure is calling for all kinds of trouble: Performance, Transaction contention, Non-Determinism etc..

If you insist on using this, you could refer Calling REST API From SQL Server Stored Procedure and check whether this is helpful to you.

In addition, you could consider to use CLR. By using CLR, we could run and manage the code inside the SQL Server. Code that runs within CLR is referred to as a managed code.

You also could refer below links for more details:
Call url from sql server stored procedure
Using SQL CLR to Call a Web Service

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table