Add new item to sharepoint list by using Rest API in SQL

Question

Hi,
I am trying to add new item to sharepoint list by using rest api in sql, but I am getting error that I can't deal with. I need to do this this way, because there is need to share information in SP when some information occur in our database. We are using SharePoint 2019 on Premise.

I am getting this error:
{"error":{"code":"-2130575251, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The security validation for this page is invalid and might be corrupted. Please use your web browser's Back button to try your operation again."}}}

From what i read about this error, i should add X-RequestDigest, but after adding code with it, nothing changed and I still get this error.
This is code I am testing:

DECLARE @URL NVARCHAR(MAX) = 'http://<server>/<site>/_api/contextinfo';  
DECLARE @HeaderValue NVARCHAR(MAX) = 'application/json; odata=verbose';  
Declare @Object as Int;  
Declare @ResponseText as Varchar(8000);  
DECLARE @RequestDigest NVARCHAR(MAX);  
  
DECLARE @ret INT;  
EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;  
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);  
  
Exec sp_OAMethod @Object, 'open', NULL, 'post', @URL, 'False';  
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Accept', @HeaderValue;  
Exec sp_OAMethod @Object, 'send';  
  
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT;  
  
IF((Select @ResponseText) <> '')  
BEGIN  
     DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)  
     SELECT @RequestDigest = req  
     FROM OPENJSON(@json)  
          WITH (  
                 req NVARCHAR(30) '$.d.GetContextWebInformation.FormDigestValue'  
               );  
END  
ELSE  
BEGIN  
     DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';  
     Print @ErroMsg;  
END  
Exec sp_OADestroy @Object  
  
print @RequestDigest;  
  
set @URL = '<server>/<site>/_api/lists/getbytitle(''test'')/items';  
  
DECLARE @Body AS VARCHAR(8000) =  
'{  
  "__metadata": {  
    "type": "SP.Data.TestListItem"  
  },  
  "Title": "Test"  
}'  
  
DECLARE @len int  
SET @len = len(@body)  
  
EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;  
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);  
  
Exec sp_OAMethod @Object, 'Open', null, 'Post', @URL, 'False';  
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Accept', @HeaderValue;  
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', @HeaderValue;  
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Content-Length', @len;  
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'X-RequestDigest', @RequestDigest;  
--EXEC sp_OAMethod @Object, 'SETRequestBody', null, 'Body', @body;  
Exec sp_OAMethod @Object, 'send', null, @Body;  
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT;  
  
IF CHARINDEX('false',(SELECT @ResponseText)) > 0  
BEGIN  
 SELECT @ResponseText As 'Message'  
END  
ELSE  
BEGIN  
 SELECT @ResponseText As 'Details'  
END  
EXEC sp_OADestroy @Object  


  
  

Answer 1

Thanks @Jakub Witoński for the highlight.

So, if I understand correctly there are 2 requirements here:

1. Scheduling the task, preferably using SQL Server Agent and Job
2. The job will add new item to SharePoint list

Using that understanding, you can try following path:

1. Use powershell script (example by @ZhengyuGuo ) or even using Pnp.PowerShell (https://learn.microsoft.com/en-us/powershell/sharepoint/sharepoint-pnp/sharepoint-pnp-cmdlets?view=sharepoint-ps&WT.mc_id=M365-MVP-4024516)
2. Then you will use Sql Server Agent to execute Powershell script created #1 (see here: https://learn.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver15?WT.mc_id=M365-MVP-4024516)

Luckily the Pnp.PowerShell usage is straight forward - https://learn.microsoft.com/en-us/powershell/module/sharepoint-pnp/add-pnplistitem?view=sharepoint-ps

After you install-module Pnp.Powershell , you can

Import-Module Pnp.Powershell  
  
Add-PnPListItem -List "Demo List" -Values @{"Title" = "Test Title"; "Category"="Test Category"}  

I hope it helps.