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

The security validation for this page is invalid

You are using the very old MSXML2.XMLHTTP, which doesn't support any security feature, no https, no encrytion and so on; I don't think you get that working.
And SQL Server is not the right place for such operations.
Better use other tools like PowerShell: SharePoint PowerShell cmdlet

Answer 2

Agree with OlafHelper's suggestion.

In summary, SQL Query isn't the right way to call SharePoint Rest API.

You can use the PowerShell below to call SharePoint 2013 On-Premise Rest API for adding item:

$url="http://server/sites/SiteName/_api/lists/GetByTitle('YourListTitle')/items"
#the url to get request digest
$url1="http://server/sites/SiteName/_api/contextinfo"                    
$url2="http://server/sites/SiteName/_api/lists/GetByTitle('YourListTitle')"
$credentials=Get-Credential



#getting request digest
$RequestDigest=Invoke-RestMethod -Uri $url1 -Method POST -Credential $credentials
$RequestDigest=$RequestDigest.GetContextWebInformation.FormDigestValue


#getting ListItemEntityTypeFullName
$List = Invoke-RestMethod -uri $url2 -Credential $credentials
$ListItemEntityTypeFullName = $list.entry.content.properties.ListItemEntityTypeFullName
$ListItemEntityTypeFullName



$body = "{ '__metadata': { 'type': '$ListItemEntityTypeFullName' }, 'Title': 'Hello'}"
$header = @{
"accept" = "application/json;odata=verbose"
"X-RequestDigest" = $RequestDigest

}

Invoke-RestMethod -Method Post -Uri $url -Body $body -ContentType "application/json;odata=verbose" -Headers $header -Credential $credentials

Answer 3

I could not agree more with @Olaf Helper . But I am curious , does @Jakub Witoński have any specific reason why he needs to call REST API from SQL?

Answer 4

I could not agree more with @Olaf Helper . But I am curious , does @Jakub Witoński have any specific reason why he needs to call REST API from SQL?

Answer 5

@Riwut Libinuko
As I said in first post, we need this functionality to be restarting daily, automatically and also be connected to SQL to retrieve information when to create new element in list. I know how to do this side functionality in SQL, so it was my first pick.
I suppose that powershell scripts can also be initiated in this way and can connect to sql server. If I am not wrong?
But also we are using SQL Server Agent and Jobs daily. I wanted to be consistent in it, because I am afraid if I will be putting some scripts here and there, on this or that server, in time, it can be forgotten and after a while we wouldn't know why something could not work properly. Of course if there is no other way I will stick with this.