(SQL Server) SharePoint -- Upload File

See more SharePoint Examples

Demonstrates how to upload a file to a SharePoint folder.

For more information, see https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-folders-and-files-with-rest

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- -------------------------------------------------------------------------
    -- The following comments apply to SharePoint Windows classic authentication.
    -- -------------------------------------------------------------------------
    -- For example, imagine our SharePoint endpoint is https://xyzoffice.mycompany.com/
    -- The SHAREPOINT_NTLM_DOMAIN would be "mycompany.com"
    -- The SHAREPOINT_HTTPS_DOMAIN would be "xyzoffice.mycompany.com"
    -- Also, the SHAREPOINT_USERNAME would be just the name, not a full email address.
    -- for example, "chilkat" instead of "chilkat@mycompany.com"

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- If SharePoint Windows classic authentication is used, then set the 
    -- Login, Password, LoginDomain, and NtlmAuth properties.
    EXEC sp_OASetProperty @http, 'Login', 'SHAREPOINT_USERNAME'
    EXEC sp_OASetProperty @http, 'Password', 'SHAREPOINT_PASSWORD'
    EXEC sp_OASetProperty @http, 'LoginDomain', 'SHAREPOINT_NTLM_DOMAIN'
    EXEC sp_OASetProperty @http, 'NtlmAuth', 1

    -- -------------------------------------------------------------------------
    -- The more common case is to use SharePoint Online authentication (via the SPOIDCRL cookie).
    -- If so, do not set Login, Password, LoginDomain, and NtlmAuth, and instead
    -- establish the cookie as shown at SharePoint Online Authentication
    -- -------------------------------------------------------------------------

    -- When creating, updating, and deleting SharePoint entities, we'll need
    -- to first get the server's form digest value to send in the X-RequestDigest header.
    -- This can be retrieved by making a POST request with an empty body to
    -- http://<site url>/_api/contextinfo and extracting the value of the
    -- d:FormDigestValue node in the XML that the contextinfo endpoint returns.

    -- Apparently, SharePoint needs an "Accept" request header equal to "application/xml",
    -- otherwise SharePoint will return an utterly incomprehensible and useless error message.
    DECLARE @savedAccept nvarchar(4000)
    EXEC sp_OAGetProperty @http, 'Accept', @savedAccept OUT
    EXEC sp_OASetProperty @http, 'Accept', 'application/xml'

    -- If your Sharepoint site is within a site collection, then use "https://SHAREPOINT_HTTPS_DOMAIN/sites/teamA/_api/contextinfo" where "teamA" is the name of the site.
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, 'https://SHAREPOINT_HTTPS_DOMAIN/_api/contextinfo', '', 'utf-8'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- Restore the default Accept header
    EXEC sp_OASetProperty @http, 'Accept', @savedAccept

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        -- A response status code not equal to 200 indicates failure.

        EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
        PRINT 'Response status code = ' + @iTmp0

        PRINT 'Response body:'
        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @resp

        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    DECLARE @success int
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
    EXEC @hr = sp_OADestroy @resp

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- The response XML looks like this:

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <d:GetContextWebInformation xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml" m:type="SP.ContextWebInformation">
    --     <d:FormDigestTimeoutSeconds m:type="Edm.Int32">1800</d:FormDigestTimeoutSeconds>
    --     <d:FormDigestValue>0x3059FFB920651834540F3E6792EA73F5746B302E953FF4E808E485DB1E6C2836C7CF924644995F092453B02A94DE14A7962674B7B16780AF16EAFB8C246BCDC7,12 Apr 2017 17:08:22 -0000</d:FormDigestValue>
    --     <d:LibraryVersion>15.0.4569.1000</d:LibraryVersion>
    --     <d:SiteFullUrl>https://SHAREPOINT_HTTPS_DOMAIN</d:SiteFullUrl>
    --     <d:SupportedSchemaVersions m:type="Collection(Edm.String)">
    --         <d:element>14.0.0.0</d:element>
    --         <d:element>15.0.0.0</d:element>
    --     </d:SupportedSchemaVersions>
    --     <d:WebFullUrl>https://SHAREPOINT_HTTPS_DOMAIN</d:WebFullUrl>
    -- </d:GetContextWebInformation>
    -- 

    -- We'll use the d:FormDigestValue in the X-RequestDigest request header
    -- for the actual file upload.

    -- --------------------------------------------------------------------
    -- Now upload a file named "penguins2.jpg" to the "/Documents" folder.

    DECLARE @req int
    -- Use "Chilkat_9_5_0.HttpRequest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT

    EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
    -- Change "/Documents" to whatever folder is the destination.
    -- If your Sharepoint site is within a site collection, then use "/sites/teamA/_api/web/GetFolderByServerRelativeUrl('/sites/teamA/Documents')/Files/add(url='penguins2.jpg',overwrite=true)" where "teamA" is the name of the site.
    EXEC sp_OASetProperty @req, 'Path', '/_api/web/GetFolderByServerRelativeUrl(''/Documents'')/Files/add(url=''penguins2.jpg'',overwrite=true)'
    -- Give the location of the file to be uploaded.  
    -- Note: The file is not loaded into memory at this point.  It is never fully loaded
    -- into memory.  Instead, the file data is streamed directly from the file when
    -- when the upload occurs.
    EXEC sp_OAMethod @req, 'StreamBodyFromFile', @success OUT, 'qa_data/jpg/penguins2.jpg'

    -- Add the X-RequestDigest header field.
    EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'd:FormDigestValue'
    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'X-RequestDigest', @sTmp0

    -- Set the Accept header
    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Accept', 'application/xml'

    -- Do the upload using https (TLS). Port 443 is the default port for https.
    DECLARE @bUseTls int
    SELECT @bUseTls = 1
    EXEC sp_OAMethod @http, 'SynchronousRequest', @resp OUT, 'SHAREPOINT_HTTPS_DOMAIN', 443, @bUseTls, @req
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @req
        RETURN
      END

    -- The response body contains XML.  Load it into our xml object..
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
        PRINT 'Response status code = ' + @iTmp0
        EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed'
        EXEC @hr = sp_OADestroy @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @req
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    -- Show the successful XML response.
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    PRINT 'Success.'

    -- A successful XML response looks like this:

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <entry xml:base="https://SHAREPOINT_HTTPS_DOMAIN/_api/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml">
    --     <id>Web/GetFileByServerRelativeUrl('/Documents/penguins2.jpg')</id>
    --     <category term="SP.File" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    --     <link rel="edit" href="Web/GetFileByServerRelativeUrl(&apos;/Documents/penguins2.jpg&apos;)" />
    --     <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Author" type="application/atom+xml;type=entry" title="Author" href="Web/GetFileByServerRelativeUrl(&apos;/Documents/penguins2.jpg&apos;)/Author" />
    --     <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CheckedOutByUser" type="application/atom+xml;type=entry" title="CheckedOutByUser" href="Web/GetFileByServerRelativeUrl(&apos;/Documents/penguins2.jpg&apos;)/CheckedOutByUser" />
    --     <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ListItemAllFields" type="application/atom+xml;type=entry" title="ListItemAllFields" href="Web/GetFileByServerRelativeUrl(&apos;/Documents/penguins2.jpg&apos;)/ListItemAllFields" />
    --     <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/LockedByUser" type="application/atom+xml;type=entry" title="LockedByUser" href="Web/GetFileByServerRelativeUrl(&apos;/Documents/penguins2.jpg&apos;)/LockedByUser" />
    --     <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ModifiedBy" type="application/atom+xml;type=entry" title="ModifiedBy" href="Web/GetFileByServerRelativeUrl(&apos;/Documents/penguins2.jpg&apos;)/ModifiedBy" />
    --     <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Versions" type="application/atom+xml;type=feed" title="Versions" href="Web/GetFileByServerRelativeUrl(&apos;/Documents/penguins2.jpg&apos;)/Versions" />
    --     <title />
    --     <updated>2017-04-12T17:16:44Z</updated>
    --     <author>
    --         <name />
    --     </author>
    --     <content type="application/xml">
    --         <m:properties>
    --             <d:CheckInComment />
    --             <d:CheckOutType m:type="Edm.Int32">0</d:CheckOutType>
    --             <d:ContentTag>{FC8D1A58-9544-496C-8D9C-19B501A8B322},2,3</d:ContentTag>
    --             <d:CustomizedPageStatus m:type="Edm.Int32">0</d:CustomizedPageStatus>
    --             <d:ETag>"{FC8D1A58-9544-496C-8D9C-19B501A8B322},2"</d:ETag>
    --             <d:Exists m:type="Edm.Boolean">true</d:Exists>
    --             <d:Length m:type="Edm.Int64">99832</d:Length>
    --             <d:Level m:type="Edm.Byte">255</d:Level>
    --             <d:MajorVersion m:type="Edm.Int32">0</d:MajorVersion>
    --             <d:MinorVersion m:type="Edm.Int32">1</d:MinorVersion>
    --             <d:Name>penguins2.jpg</d:Name>
    --             <d:ServerRelativeUrl>/Documents/penguins2.jpg</d:ServerRelativeUrl>
    --             <d:TimeCreated m:type="Edm.DateTime">2017-04-12T17:14:19Z</d:TimeCreated>
    --             <d:TimeLastModified m:type="Edm.DateTime">2017-04-12T17:16:44Z</d:TimeLastModified>
    --             <d:Title m:null="true" />
    --             <d:UIVersion m:type="Edm.Int32">1</d:UIVersion>
    --             <d:UIVersionLabel>0.1</d:UIVersionLabel>
    --         </m:properties>
    --     </content>
    -- </entry>
    -- 
    -- 

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @req


END
GO