3

Can i call REST Web Service of SharePoint 2013 from tsql stored procedure ?

All examples in web are for GET method, But i want create item in SharePoint list and i need to use POST method.

this is my code :

USE [IODB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[stt]
AS 
BEGIN
    declare @UserName nvarchar(100) = '*****';
    declare @Password nvarchar(100)= '****'; 
    declare @RequestText as nvarchar(500)
    set @RequestText='{"__metadata":{"type":"SP.Data.AaaaListItem"},"Title":"extitle"}';
    Declare @Object as Int; 
    Declare @ResponseText as Varchar(8000); 
    Declare @Url as Varchar(MAX);
    select @Url = 'http://<DomainName>/home/_api/lists/getbytitle(''aaaa'')/items'
    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; 
    Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false' ,@UserName, @Password
    Exec sp_OAMethod @Object, 'send', null, @RequestText
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT      
    Exec sp_OADestroy @Object
    select @ResponseText
    select @Object
END
1

You first need to make a POST request to http://<DomainName>/home/_api/contextinfo to get a request digest. Then you'll have to add that as a header X-RequestDigest to your actual POST to add the list item. The token is valid for 30 minutes, I believe, so you may want to cache it if you are expecting this procedure to be accessed multiple times within that period.

If you are getting errors, post them here and we can address them. I've never had to do this, but it's pretty interesting as a problem.

Another possibility might be to write a PowerShell script that does exactly what you need and then call that within your T-SQL. I have a pretty strong background in both and I can say that what you're doing could be accomplished in far fewer lines in PowerShell and would be a lot easier to debug.

1
  • Thx, "write a PowerShell script that does exactly what you need and then call that within your T-SQL", how to do it ?
    – alireza
    May 1 '16 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.