question

JakubWitoski-6977 avatar image
0 Votes"
JakubWitoski-6977 asked TomPhillips-1744 commented

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

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


sql-server-transact-sqloffice-sharepoint-server-development
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@JakubWitoski-6977,

Is there anything update? If there is any answer helps you, you could accept it:)

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jerryzy avatar image
0 Votes"
Jerryzy answered Jerryzy edited

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cakriwut avatar image
0 Votes"
cakriwut answered

I could not agree more with @OlafHelper-2800 . But I am curious , does @JakubWitoski-6977 have any specific reason why he needs to call REST API from SQL?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cakriwut avatar image
0 Votes"
cakriwut answered

I could not agree more with @OlafHelper-2800 . But I am curious , does @JakubWitoski-6977 have any specific reason why he needs to call REST API from SQL?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JakubWitoski-6977 avatar image
0 Votes"
JakubWitoski-6977 answered TomPhillips-1744 commented

@cakriwut
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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQL Agent can run Powershell scripts.

0 Votes 0 ·
cakriwut avatar image
0 Votes"
cakriwut answered cakriwut edited

Thanks @JakubWitoski-6977 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 @Jerryzy ) or even using Pnp.PowerShell (https://docs.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://docs.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://docs.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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.