Microsoft SharePoint Server Connection Details¶
Introduction¶
Connector Version
This documentation is based on version 23.0.8690 of the connector.
Get Started¶
Microsoft SharePoint Version Support
The connector supports all versions of Microsoft SharePoint that support the SOAP API. This includes: Windows SharePoint Services 3.0, SharePoint Server 2007+ (2010, 2013, etc.), and SharePoint Online. The connector models the custom lists of your SharePoint site as bidirectional tables; when you connect, the connector retrieves the metadata for these tables by calling SharePoint Web services. Supported authentication schemes are NTLM, Basic, Digest, Forms, Kerberos, SSO, STS (security token services), and SharePoint authentication cookies.
Establish a Connection¶
Connect to Microsoft SharePoint¶
Regardless of whether you will connect online or on-premesis, what architecture will be used, and which Lists and Documents will be accessed, connecting to Microsoft SharePoint requires exactly two things:
- Set the
URL
connection property. - Set the appropriate authentication properties for your working environment.
Setting URL:
Microsoft SharePoint works with all Lists and Documents in the global Microsoft Sharepoint site, or all Lists and Documents at individual sites.
To work with all Lists and Documents in the global Microsoft Sharepoint site, set the URL
connection property to your Site Collection URL. For example:
https://teams.contoso.com
To work with all Lists and Documents at an individual site, set the URL
connection property to your individual site URL. For example:
https://teams.contoso.com/TeamA
The following sections describe how to set the appropriate authentication properties for your working environment. For information about how to create a custom OAuth application (required for use with AzureAD in a Web application; optional for AzureAD access via a Desktop application or a Headless Server), see Creating a Custom OAuth Application.
Microsoft SharePoint Online¶
Set SharePointEdition
to "SharePoint Online" and set the User
and Password
to the credentials you use to log onto SharePoint, for example, the credentials to your Microsoft Online Services account.
Microsoft SharePoint online supports a number of cloud-based architectures, each of which supports a different set of authentication schemes:
- AzureAD
- Single sign-on (SSO) via the ADFS, Okta, OneLogin, or PingFederate SSO identity provider
- Azure MSI
- Azure Password
- OAuthJWT
- SharePointOAuth
If the user account domain is different from the domain configured with the identity provider, set SSODomain
to the latter. This property may be required for any SSO.
AzureAD¶
Azure Active Directory (AzureAD) is a connection type that leverages OAuth to authenticate. OAuth requires the authenticating user to interact with Microsoft SharePoint using an internet browser. The driver facilitates this in several ways as described below. Set your AuthScheme to AzureAD
. The AzureAD flows described below assume that you have done so.
Your organization may require Admin Consent when authorizing a new AzureAD application for your Azure Tenant. In all AzureAD flows, any initial installation and use of an AzureAD application requires that an administrator approve the application for their Azure Tenant. For details, see Creating a Custom OAuth Application.
Desktop Applications
An embedded OAuth application is provided that simplifies OAuth desktop Authentication. Alternatively, you can create a custom AzureAD application. See Creating a Custom OAuth Application for information about creating custom applications and reasons for doing so.
For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom AzureAD applications.
After setting the following connection properties, you are ready to connect:
InitiateOAuth
:GETANDREFRESH
. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting theOAuthAccessToken
.CallbackURL
: The Redirect URL in your application settings.Custom applications only
:OAuthClientId
: The client ID in your application settings.OAuthClientSecret
: The client secret in your application settings.
When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:
- Extracts the access token from the callback URL and authenticates requests.
- Obtains a new access token when the old one expires.
- Saves OAuth values in
OAuthSettingsLocation
. These stored values persist across connections.
Single Sign-On Identity Providers¶
ADFS
Set the AuthScheme
to ADFS
. You must set the following connection properties:
User
: The ADFS user.Password
: The user's ADFS password.SSODomain
(optional): The domain configured with the ADFS identity provider.
Example connection string:
AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;URL='http://sharepointserver/mysite';
Okta
Set the AuthScheme
to Okta
. The following connection properties are used to connect to Okta:
User
: The Okta user.Password
: The user's Okta password.SSODomain
(optional): The domain configured with the OKTA identity provider.
Example connection string:
AuthScheme=Okta;User=oktaUserName;Password=oktaPassword;URL='http://sharepointserver/mysite';
OneLogin
Set the AuthScheme
to OneLogin
. The following connection properties are used to connect to OneLogin:
User
: The OneLogin user.Password
: The user's OneLogin password.SSODomain
(optional): The domain configured with the OneLogin identity provider.
Example connection string:
AuthScheme=OneLogin;User=OneLoginUserName;Password=OneLoginPassword;URL='http://sharepointserver/mysite';
PingFederate
Set the AuthScheme
to PingFederate
. The following connection properties are used to connect to PingFederate:
User
: The PingFederate user.Password
: PingFederate password for the user.SSODomain
(optional): The domain configured with the PingFederate identity provider.
Example connection string:
AuthScheme=PingFederate;User=PingFederateUserName;Password=PingFederatePassword;URL='http://sharepointserver/mysite';
Azure MSI¶
If you are running Microsoft SharePoint on an Azure VM, you can leverage Azure Managed Service Identity (MSI) credentials to connect:
AuthScheme
:AzureMSI
.
The MSI credentials are automatically obtained for authentication.
Azure Password¶
To connect using your Azure dredentials directly, specify the following connection properties:
AuthScheme
:AzurePassword
User
: The user account used to connect to AzurePassword
: The password used to connect to AzureAzureTenant
:Directory (tenant) ID
, found on the Overview page of the OAuth application used to authenticate to Microsoft SharePoint on Azure.
OAuthJWT Certificate¶
Set the AuthScheme
to OAUTHJWT
. The following connection properties are used to connect to Microsoft SharePoint:
OAuthGrantType
:CLIENT
.AzureTenant
: The tenant you wish to connect to.OAuthJWTCert
: The JWT Certificate store.OAuthJWTIssuer
: The OAuth client Id.
SharePointOAuth¶
Set the AuthScheme
to SharePointOAuth
. The following connection properties are used to connect to SharePointOAuth:
Schema
:REST
.InitiateOAuth
:GETANDREFRESH
.OAuthClientId
: The application's identity/Client Id.OAuthClientSecret
: The application's Client Secret.
Example connection string:
SharePointEdition='SharepointOnline';URL=https://rssbuscrm.sharepoint.com;Schema=REST;AuthScheme=SharepointOAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=11111111-1111-1111-1111-111111111111;OAuthClientSecret=1111111111111/11111111111111/11111111111111=;
This AuthScheme works with the custom OAuth application. To generate credentials from a custom OAuth application, see Creating a Custom OAuth Application.
Microsoft SharePoint On-Premises¶
Microsoft SharePoint On-Premises supports a number of premise-based architectures:
- Windows (NTLM)
- Kerberos
- ADFS
- Anonymous Access
Set SharePointEdition
to "SharePoint On-Premises" to use the following authentication types.
Windows (NTLM)¶
This is the most common authentication type. As such, the connector is preconfigured to use NTLM as the default; simply set the Windows User
and Password
to connect.
Kerberos¶
Set the AuthScheme
to NEGOTIATE
, and then set the following Kerberos connection properties:
KerberosKDC
: The host name or IP Address of your Kerberos KDC machine.KerberosRealm
: The realm of the Microsoft SharePoint Kerberos principal. This is the value after the '@' symbol (for instance, EXAMPLE.COM) of the principal value (for instance, MyService/MyHost@EXAMPLE.COM).KerberosSPN
: The service and host of the Microsoft SharePoint Kerberos Principal. This is the value prior to the '@' symbol (for instance, MyService/MyHost) of the principal value (for instance, MyService/MyHost@EXAMPLE.COM).
For details on how to authenticate with Kerberos, see Using Kerberos.
ADFS¶
Set the AuthScheme
to ADFS
, and then set the following connection properties:
User
: The ADFS user.Password
: ADFS password for the user.SSOLoginURL
: The WS-trust endpoint of the ADFS server.
You also need the to set SSOProperties
to authenticate to ADFS. Specify the value of the RelyingParty parameter; it is located on the ADFS server for Sharepoint. Example connection string:
AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;SSOLoginURL='https://<authority>/adfs/services/trust/2005/usernamemixed';SSO Properties ='RelyingParty=urn:sharepoint:sp2016;';
Anonymous Access¶
Set the AuthScheme
to NONE
along with the URL.
Create a Custom OAuth Application¶
Create a Custom OAuth Application¶
embeds OAuth Application Credentials with branding that can be used when using Azure to connect via either a Desktop Application or a Headless Machine. However, in all cases, connecting to Azure via a Web application requires creating a custom OAuth application.
You might also want to create a custom OAuth application to:
- control branding of the Authentication Dialog,
- control the redirect URI that the application redirects the user to after the user authenticates, or
- customize the permissions that you are requesting from the user.
The following sections describe how to create a custom OAuth application using either the Azure Portal or Sharepoint App.
Azure Portal¶
To obtain OAuth values for your app, the OAuthClientId
and OAuthClientSecret
, and register a custom OAuth application:
-
Log in to the Azure Portal.
-
In the left-hand navigation pane, navigate to Azure Active Directory > App Registrations.
-
Click
Add
. -
Enter an application name.
-
Select
Any Azure AD Directory - Multi Tenant
. -
Set the redirect URL to
http://localhost:33333
(the connector's default) or set a different port of your choice. -
Set
CallbackURL
to the exact reply URL you defined.The Portal creates the new application.
-
Navigate to the "Certificates & Secrets" section.
-
Create a client secret for the application, and select a duration.
-
After you save the key, the key value is displayed once. Immediately, set
OAuthClientSecret
to the displayed value. SetOAuthClientId
to the Application Id. -
Select
API Permissions
. -
Click
Add
. If your application will only connect within a user context, specify Delegated Permissions. -
In the API Permissions section, click on
Add a permission
and selectSharePoint
. Choose the permissions you want your app to have. To view and edit lists, you have to select (at least) theAllSites.Manage
permission. -
Save your changes.
Note
If you have selected to use permissions that require admin consent, you can grant them from the current tenant on the API Permissions page. Otherwise, see "OAuth: Admin Consent", below.
OAuth: Admin Consent¶
Admin consent refers to when the Admin for an Azure Active Directory tenant grants permissions to an application that requires an administrator in your organization to consent to the use case.
The embedded application within the Jitterbit Connector for Microsoft SharePoint, contains no permissions that require administrator consent. Therefore, this information applies only to custom applications.
When creating a new OAuth application in the Azure Portal, you must specify which permissions the application requires. Some permissions may be marked with "Admin Consent Required". For example, all Groups permissions require Admin Consent. If your application requires admin consent, there are two ways you can do this.
The easiest way to grant admin consent is to have an administrator log into the Azure Portal and navigate to the application you have created in App Registrations. Under API Permissions, click Grant Consent
, which grants permissions on the tenant under which it was created.
If your organization has multiple tenants or you need to grant application permissions for other tenants outside your organization, use the GetAdminConsentURL stored procedure* to generate the Admin Authorization URL. After the OAuth application is successfully authorized, it returns a Boolean indicating that permissions have been granted.
After the administrator has approved the OAuth Application, you can continue to authenticate.
Sharepoint App¶
To create a custom OAuth app and obtain the connection properties for the OAuth authentication using Sharepoint App, you must first register the add-in, then grant permissions to that add-in.
Register the Add-In¶
- Navigate to the Register Add-In page by entering the URL as :
https://{sitename}.SharePoint.com/_layouts/15/appregnew.aspx
. - In the "App Information" section, click
Generate
, which is located next to the Client ID and Client Secret textboxes to generate the respective values. - Set Title, App Domain, and Redirect URI by filling in their respective text boxes.
- Click
Create
.
The add-in is registered, and the Sharepoint App displays the created information.
Grant Permissions to the Add-In¶
-
Navigate to the SharePoint site.
-
Enter the URL:
https://{sitename}-admin.sharepoint.com/_layouts/15/appinv.aspx
in the browser. This redirects to the Grant Permission page. -
At
App Id
, enter the Client ID which you generated earlier, and clickLookup
.This populates the values in the other text boxes (Title, App Domain and Redirect URL).
-
Enter the following permission request in XML format:
<AppPermissionRequests AllowAppOnlyPolicy="true"> <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl"/> </AppPermissionRequests>
-
Click
Create
. Sharepoint App displays a permission consent dialog. To grant the permissions, clickTrust It
.
Connect to REST API¶
Microsoft SharePoint REST API is supported both on Sharepoint OnPremise and on Sharepoint Online. To connect using the REST API set Schema
to REST.
The property SharePointEdition
may be used to define the edition of Sharepoint.
Sharepoint Online¶
SharePoint Online uses OAuth standard to authenticate. Follow the steps under "Authenticating to SharePoint Online" in Establishing a Connection for more information.
Sharepoint OnPremise¶
Follow the steps under "Authenticating to SharePoint On Premises" in Establishing a Connection for more information.
Use Kerberos¶
Kerberos¶
Authenticating to Microsoft SharePoint via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.
Set Authentication Properties¶
To authenticate to Microsoft SharePoint using Kerberos, set these properties:
- hive.server2.authentication:
Kerberos
. AuthScheme
:NEGOTIATE
.KerberosKDC
: The host name or IP Address of your Kerberos KDC machine.KerberosRealm
: The realm of the Microsoft SharePoint Kerberos principal. Find this value immediately after the '@' symbol of the principal value.KerberosSPN
: The service and host of the Microsoft SharePoint Kerberos Principal. Find this value just before the '@' symbol of the principal value.
Retrieve Kerberos Tickets¶
Kerberos tickets are used to authenticate the requester's identity. The use of tickets instead of formal logins/passwords eliminates the need to store passwords locally or send them over a network. Users are reauthenticated (tickets are refreshed) whenever they log
in at their local computer or enter kinit USER
at the command prompt.
The connector provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME
and/or KerberosKeytabFile
variables exist in your environment.
MIT Kerberos Credential Cache File
This option enables you to use the MIT Kerberos Ticket Manager or kinit
command to get tickets. With this option there is no need to set the User
or Password
connection properties.
This option requires that KRB5CCNAME
has been created in your system.
To enable ticket retrieval via MIT Cerberos Credential Cache Files:
- Ensure that the
KRB5CCNAME
variable is present in your environment. - Set
KRB5CCNAME
to a path that points to your credential cache file. (For example,C:\krb_cache\krb5cc_0
or/tmp/krb5cc_0
.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket. -
To obtain a ticket:
- Open the MIT Kerberos Ticket Manager application.
- Click
Get Ticket
. - Enter your principal name and password.
- Click
OK
.
If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.
The connector uses the cache file to obtain the Kerberos ticket to connect to Microsoft SharePoint.
Note
If you would prefer not to edit KRB5CCNAME
, you can use the KerberosTicketCache
property to set the file path manually. After this is set, the connector uses the specified cache file to obtain the Kerberos ticket to connect to Microsoft SharePoint.
Keytab File
If your environment lacks the KRB5CCNAME
environment variable, you can retrieve a Kerberos ticket using a Keytab File.
To use this method, set the User
property to the desired username, and set the KerberosKeytabFile
property to a file path pointing to the keytab file associated with the user.
User and Password
If your environment lacks the KRB5CCNAME
environment variable and the KerberosKeytabFile
property has not been set, you can retrieve a ticket using a user and password combination.
To use this method, set the User
and Password
properties to the user/password combination that you use to authenticate with Microsoft SharePoint.
Enable Cross-Realm Authentication¶
More complex Kerberos environments can require cross-realm authentication where multiple realms and KDC servers are used. For example, they might use one realm/KDC for user authentication, and another realm/KDC for obtaining the service ticket.
To enable this kind of cross-realm authentication, set the KerberosRealm
and KerberosKDC
properties to the values required for user authentication. Also, set the KerberosServiceRealm
and KerberosServiceKDC
properties to the values required to obtain the service ticket.
Fine-Tuning Data Access¶
Fine Tuning the Microsoft SharePoint Connection¶
To make it easier to access data in advanced integrations, use the following connection properties to control column name identifiers and other aspects of data access:
UseDisplayNames
: Set this to true to return column names that match field names in the underlying API
. By default, the connector uses column names that match the field names defined in SharePoint.UseSimpleNames
: Set this to true to perform substitutions on special characters in column names that SharePoint allows but that many databases typically do not.ShowPredefinedColumns
: Set this to false to exclude fields derived from fields in the list; for example, Author and CreatedAt
. This setting excludes the predefined fields from being returned inSELECT *
statements and schema discovery.ShowHiddenColumns
: When true, columns marked as hidden in SharePoint will be displayed by the connector.
Advanced Features¶
This section details a selection of advanced features of the Microsoft SharePoint connector.
User Defined Views
The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
SSL Configuration
Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert
property under "Connection String Options" for more information.
Proxy
To configure the connector using private agent proxy settings, select the Use Proxy Settings
checkbox on the connection configuration screen.
Query Processing
The connector offloads as much of the SELECT statement processing as possible to Microsoft SharePoint and then processes the rest of the query in memory (client-side).
See Query Processing for more information.
User Defined Views¶
The Jitterbit Connector for Microsoft SharePoint allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:
- Create a JSON-formatted configuration file defining the views you want.
- DDL statements.
Define Views Using a Configuration File¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM Calendar WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"
Define Views Using DDL Statements¶
The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.
Create a View¶
To create a new view using DDL statements, provide the view name and query as follows:
CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;
If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews
connection property.
Alter a View¶
To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:
ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';
The view is then updated in the JSON configuration file.
Drop a View¶
To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.
DROP LOCAL VIEW [MyViewName]
This removes the view from the JSON configuration file. It can no longer be queried.
Schema for User Defined Views¶
User Defined Views are exposed in the UserViews
schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName
property.
Work with User Defined Views¶
For example, a SQL statement with a User Defined View called UserViews.RCustomers
only lists customers in Raleigh:
SELECT * FROM Customers WHERE City = 'Raleigh';
An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';
Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';
That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.
SSL Configuration¶
Customizing the SSL Configuration¶
By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert
property for the available formats to do so.
Client SSL Certificates¶
The Microsoft SharePoint connector also supports setting client certificates. Set the following to connect using a client certificate.
SSLClientCert
: The name of the certificate store for the client certificate.SSLClientCertType
: The type of key store containing the TLS/SSL client certificate.SSLClientCertPassword
: The password for the TLS/SSL client certificate.SSLClientCertSubject
: The subject of the TLS/SSL client certificate.
SOAP Data Model¶
The Jitterbit Connector for Microsoft SharePoint models Microsoft SharePoint entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained based on your Microsoft SharePoint site. Any changes you make, such as adding a custom field or changing a field's data type, are automatically reflected when you connect.
Customizing the Data Model
The connector sets defaults to facilitate the maximum number of integrations; however, the following connection properties allow a greater granularity of customization useful in advanced integrations:
CalculatedDataType
: The data type to be used for calculated fields.CreateIDColumns
: Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists.FolderOption
: An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll.PseudoColumns
: Indicates whether or not to report pseudo columns as columns in the table metadata.
Tables
Tables describes the available tables.
The connector can expose custom lists from Microsoft SharePoint that are not mentioned in the Tables. The data model illustrates a sample of what your SharePoint site might look like. The actual data model will be obtained dynamically based on your user credentials and SharePoint site.
Views
Typically, entities that cannot be modified are represented as Views, or read-only tables. You can also access custom views of a list as relational views.
To get data from a custom view of a list, you can set the ViewID pseudo column in the WHERE clause.
SELECT * FROM ListName WHERE ViewID='ID of the view'
You can get the ID of the view from the Views list. You must specify the List pseudo column to get a list of views for that list. For instance:
SELECT * FROM Views WHERE List ='ListName'
Stored Procedures
Stored Procedures are function-like interfaces to the data source. They surface additional capabilities of the Microsoft SharePoint API such as searching, updating, and modifying information.
Tables¶
The connector models the data in Microsoft SharePoint into a list of tables that can be queried using standard SQL statements.
Generally, querying Microsoft SharePoint tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Jitterbit Connector for Microsoft SharePoint Tables¶
Name | Description |
---|---|
Attachments | Read or delete Attachments for the specified item on the specified list. |
Groups | Create, update, delete, and query Groups from SharePoint. |
Roles | Create, update, delete, and query Roles from SharePoint. |
Users | Update, delete, and query Users from SharePoint. |
Views | Create, update, delete, and query the available lists in SharePoint. |
Attachments¶
Read or delete Attachments for the specified item on the specified list.
Table Specific Information¶
Select¶
List and ItemId are required to return Attachments.
Insert¶
Call the AddAttachments stored procedure* to add new attachments to a list item.
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Url [KEY] | String | True | Description of the term set. |
List | String | True | The internal name of the list to retrieve attachments from. |
ListDisplayName | String | True | The display name of the list to retrieve attachments from. |
ItemID | String | True | The ID of the item on the list to retrieve attachments from. |
Name | String | True | The name of the attachment on the item. |
Groups¶
Create, update, delete, and query Groups from SharePoint.
Table Specific Information¶
Insert¶
The Name, DefaultLogin, and OwnerLogin columns are required to insert to this table.
To use the UserName pseudo column, you must set the value to the LoginName of the user. You can obtain the LoginName by querying the Users table.
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Name# [KEY] | String | False | The name of the group. |
Description# | String | False | A description of the group. |
OwnerLogin# | String | False | The user name of the owner of the group. This value should be in the format DOMAIN\username. |
OwnerType# | String | False | The type of owner. User or group. |
DefaultLogin# | String | False | The user name of the default user for the group. This value should be in the format DOMAIN\username. |
Pseudo-Columns¶
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
---|---|---|
UserName | String | The logical name of the user to return groups for. Specify this value on the SELECT statement to return only groups the specified User is in. |
Roles¶
Create, update, delete, and query Roles from SharePoint.
Table Specific Information¶
Select¶
To use the UserName pseudo column, you must set the value to the LoginName of the user. You can obtain the LoginName by querying the Users table.
Insert¶
To insert a Role, at least the Name is required:
INSERT INTO Roles (Name) VALUES ('My Role')
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Name# [KEY] | String | False | The name of the role. |
Description# | String | False | A description of the role. |
Permissions# | Long | False | A long representing the permissions for the role. |
RoleType | String | True | The type of role. |
IsHidden | Boolean | True | A boolean indicating if the role is hidden. |
Pseudo-Columns¶
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
---|---|---|
UserName | String | The login name of the user to return roles for. Specify this value on a SELECT statement to return only roles assigned to the specified user. |
GroupName | String | The name of the group to return roles for. Specify this value on a SELECT statement to return only roles assigned to the specified group. |
Users¶
Update, delete, and query Users from SharePoint.
Table Specific Information¶
Select¶
Retrieve all users created for the SharePoint Account:
SELECT * FROM Users
You can retrieve Users that belong to a specific Group. In this case specify the Group Name
SELECT * FROM Users WHERE [Group] = "GroupName"
Or you can retrieve Users that have a specific Role. In this case specify the Role Name
SELECT * FROM Users WHERE [Role] = "RoleName"
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID | String | True | The ID of the user. |
LoginName# [KEY] | String | False | The login name of the user. |
Name# | String | False | The name of the user. |
Email# | String | False | The email address of the user. |
IsInDomainGroup | Boolean | True | A boolean indicating if the user is in the domain group. |
IsSiteAdmin | Boolean | True | A boolean indicating if the user is a site admin. |
Notes# | String | False | Optional notes concerning the user. |
SecurityId | String | True | The security ID (SID) for the user. |
Pseudo-Columns¶
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
---|---|---|
Group | String | The group you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions. |
Role | String | The role you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions. |
Views¶
Create, update, delete, and query the available lists in SharePoint.
Table Specific Information¶
Views is a special table. It may be used to get, update, insert, and delete views from a specified List.
Select¶
In order to return results from Views, either the ID or List must be specified in the SELECT statement. For example:
SELECT * FROM Views WHERE List = 'MyListName'
Insert¶
The List, Name, Type, and Fields columns are required to insert to this table.
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | String | True | The ID of the view. |
List | String | True | The list the view is associated with. A list must be specified when performing SELECT statements if the ID is not specified. |
ViewID | String | True | The ID of the view. May only be unique for the specific list. |
Name | String | False | The name of the view. |
Type | String | False | The type of view. This must have a value on inserts and updates. The allowed values are CALENDAR, GRID, HTML. The default value is HTML. |
Fields | String | False | A comma separated list of the fields associated with the view. This is space-sensitive. |
IsDefault | Boolean | False | A boolean indicating if the view is the default view for the list. |
Query | String | False | A query for the view. |
Views¶
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.
Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.
Jitterbit Connector for Microsoft SharePoint Views¶
Name | Description |
---|---|
FileVersions | Lists the versions of files available on SharePoint. |
GetValidTerms | Gets a list of valid terms for the specified column on the specified table. |
Lists | Lists the available lists in SharePoint. |
Permissions | The permissions for a site or list. Note: If ItemID is empty, the ObjectType should be set to List or web (an ObjectName must be specified when the ObjectType is list). If not, the ObjectName must be specified along with the ItemID. |
Subsites | This lists the available subsites. |
FileVersions¶
Lists the versions of files available on SharePoint.
View-Specific Information¶
Library and File must be specified to return results from this view.
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | String | The ID of the version. |
Comments | String | Comments about the particular version. |
CreateBy | String | The username of the SharePoint user who modified this version of the file. |
Date | Datetime | When the file was modified. |
Size | String | The size of this version of the file. |
Url | String | The URL to this version of the file. |
Library | String | The library name on SharePoint you are listing versions from. A library must be specified to retrieve the versions for a file. The default value is Shared Documents. |
File | String | The name of the file on SharePoint to list versions for. A file must be specified to retrieve the versions for a file. |
GetValidTerms¶
Gets a list of valid terms for the specified column on the specified table.
Table Specific Information¶
GetValidTerms is a special view. It may be used to get valid terms for a Taxonomy or Managed Metadata column of a given list. To use the view, supply both the name of the table and the column for which you are looking to get valid terms. For example:
SELECT * FROM GetValidTerms WHERE List = 'MyListName' AND ColumnName = 'MyManagedMetadataColumn'
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | String | The identifier of the term. |
TermLabelValue | String | The label of the term. |
Description | String | Description of the term set. |
NameInRequestedLang | String | The name of the term set in the language requested by the client. |
IsOpen | Boolean | Boolean indicating if the term set is open. |
Deprecated | Boolean | Boolean indicating if the term is deprecated. |
InternalId | String | Internal identifier for the term. |
TermSetContact | String | Term set contact. |
ContainerDesc | String | Container node for the description. |
SingleTermLabelDesc | String | This fully describes a single term label. |
IsDefaultLabel | Boolean | True if the term label is the default term label. |
BelongsTo | String | This item describes a term set to which a term belongs. |
IsTaggingAvailable | Boolean | If the term set is available for tagging, this value is true. |
TermPath | String | Term path of the term with term labels. |
TermpathoftermwithIds | String | Term path of term with identifiers. |
ChildTerms | String | A string value that indicates a custom sort order for the child terms of the term identified by PertainingToTerm. |
HasChildTerms | Boolean | True if the term has child terms. |
PertainingToTerm | String | Identifier of the term that this term set information is pertaining to. |
Pseudo-Columns¶
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
---|---|---|
List | String | The name of the list to get valid terms for. |
ColumnName | String | The name of the column to get valid terms for. |
LocaleId | String | The locale ID for the term. Defaults to 1033. |
Lists¶
Lists the available lists in SharePoint.
Table Specific Information¶
Lists can be used to list the tables in SharePoint. This will only return actual lists in SharePoint and not any special tables associated with the connector.
The following columns can be used in the WHERE clause: Title and BaseTemplate.
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | String | The ID of the list. |
Title | String | The title of the list. This column may be used in the WHERE clause and may be used with a wild card (*) character. |
Description | String | A description for the list. |
BaseTemplate | String | Indicates the type of template used to create the list. This column may be used in the WHERE clause. |
Version | Double | The version of the list. |
Url | String | The default URL of the list. |
EmailAlias | String | The email alias of the list. |
ImageUrl | String | The image URL of the list. |
ItemCount | Integer | The number of items in the list. |
Item_Deleted | Datetime | The last time an item was deleted from this list. |
Item_Modified | Datetime | The last time an item was modified from this list. |
SendToUrl | String | The send-to URL of the list. |
Created | Datetime | The time when the list was created. |
AllowDeletion | String | Whether items can be deleted. |
AllowMultiResponses | Boolean | Boolean indicating if multiple responses are enabled for the survey. |
Direction | String | A string that contains LTR if the reading order is left-to-right, RTL if it is right-to-left, or None. |
EnableAssignedToEmail | Boolean | Boolean indicating if assigned-to emails are enabled. Only applies to issues lists. |
EnableAttachments | Boolean | Boolean indicating if attachments may be added to items in the list. Does not apply to document libraries. |
EnableModeration | Boolean | Boolean indicating if content approval is enabled for the list. |
EnableVersioning | Boolean | Boolean indicating if versioning is enabled for the list. |
Hidden | Boolean | Boolean indicating if the list is hidden so that it does not appear on the Documents and Lists page, Quick Launch bar, Modify Site Content page, or Add Column page as an option for lookup fields. |
MultipleDataList | Boolean | Boolean indicating if a meeting-workspace site contains data for multiple meeting instances within the site. |
Ordered | Boolean | Boolean indicating if items in the list can be sorted on the Edit View page. |
Showuser | Boolean | Boolean indicating if the names of users are shown in the results of the survey. |
Permissions¶
The permissions for a site or list. Note: If ItemID is empty, the ObjectType should be set to List or web (an ObjectName must be specified when the ObjectType is list). If not, the ObjectName must be specified along with the ItemID.
Columns¶
Name | Type | Description |
---|---|---|
MemberID [KEY] | String | The ID of the permission. |
Mask | Long | A 32-bit integer in 0x00000000 format that represents a Microsoft.SharePoint.SPRights value and defines the permission. Use the pipe symbol ('|') in C# or Or in Microsoft Visual Basic to delimit values when creating a custom permission mask that combines permissions. |
MemberIsUser | Bool | Indicate whether it is the permission for user. |
MemberGlobal | Bool | Indicate whether it is the permission for group. |
RoleName | String | A string that contains the name of the site group, the name of the cross-site group, or the user name (DOMAIN\User_Alias) of the user to whom the permission applies. |
Pseudo-Columns¶
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
---|---|---|
ObjectName | String | A string that contains the name of the list or site. |
ObjectType | String | A string that specifies either List or Web. |
ItemID | String | ID of the item. |
Subsites¶
This lists the available subsites.
Columns¶
Name | Type | Description |
---|---|---|
Title | String | The name of the subsite. |
Url | String | The URL of the subsite. |
Stored Procedures¶
Stored procedures* are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Microsoft SharePoint.
Stored procedures* accept a list of parameters, perform their intended function, and then return any relevant response data from Microsoft SharePoint, along with an indication of whether the procedure succeeded or failed.
Jitterbit Connector for Microsoft SharePoint Stored Procedures¶
Name | Description |
---|---|
AddAttachment | Add an Attachment to a SharePoint List item. |
AddList | Creates a list on a SharePoint site. |
AddListColumn | Adds a new column to the specified list. |
AddUserToGroup | Add the user to specified group. |
AddUserToRole | Add the user to specified role. |
CheckInDocument | Checks in a document to SharePoint and releases the lock on the document. |
CheckOutDocument | Checks out a document from SharePoint. |
CopyDocument | Copies a document from the SharePoint library. |
CreateFolder | Adds a folder to a document library on a SharePoint site. |
CreateSchema | Creates the schema file for the specified SharePoint list. The schema file may be customized manually to exclude unwanted columns or include additional information about columns. |
DeleteAttachment | Delete an attachment from a SharePoint list item. |
DeleteDocument | Delete a document on the SharePoint library. |
DeleteList | Permanently deletes a list from a SharePoint site. |
DeleteListColumn | Deletes a column from the specified list. |
DeleteUserFromGroup | Delete the user from specified group. |
DeleteUserFromRole | Delete the user from specified role. |
DiscardCheckOutDocument | Discards a check out on a document in SharePoint. This does not check a new file into SharePoint. It only releases the lock on the document. |
DownloadAttachment | Download a document from the SharePoint list. |
DownloadDocument | Download a document from the SharePoint library. |
MoveAttachmentOrDocument | Moves a document or attachment from a source folder to a destination folder. |
RenameAttachmentOrDocument | Renames a document or attachment. |
UpdateList | Updates a list on a SharePoint site. |
UpdateListColumn | Updates a column to the specified list. |
UploadDocument | Upload a document to the SharePoint library. |
AddAttachment¶
Add an Attachment to a SharePoint List item.
Input¶
Name | Type | Accepts Input Streams | Description |
---|---|---|---|
File | String | False | The path of the local file to be added. |
List | String | False | The name of the List on the SharePoint server. |
ItemID | String | False | The ID of the item on the List to add attachments for. |
Content | String | True | The content as InputStream to be uploaded when File is not specified. |
FileName | String | False | Name of the file to be uploaded. This will be used if content is not null. For example: test.csv |
Result Set Columns¶
Name | Type | Description |
---|---|---|
URL | String | The URL of the newly created item. |
Result | String | Boolean value indicating whether the stored procedure* was successful. |
AddList¶
Creates a list on a SharePoint site.
Input¶
Name | Type | Description |
---|---|---|
Name | String | The name of the list on the SharePoint server. |
Template | String | The name of the template to use for the list creation. The allowed values are GenericList, DocumentLibrary, Survey, Links, Announcements, Contacts, Events, Tasks, DiscussionBoard, PictureLibrary, DataSources, WebTemplateCatalog, UserInformation, WebPartCatalog, ListTemplateCatalog, XMLForm, MasterPageCatalog, NoCodeWorkflows, WorkflowProcess, WebPageLibrary, CustomGrid, DataConnectionLibrary, WorkflowHistory, GanttTasks, Meetings, Agenda, MeetingUser, Decision, MeetingObjective, TextBox, ThingsToBring, HomePageLibrary, Posts, Comments, Categories, IssueTracking, AdminTasks. The default value is GenericList. |
Description | String | The description of the list to add. |
Columns | String | The defination of the columns to add, support json/xml/tempTable |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
AddListColumn¶
Adds a new column to the specified list.
Input¶
Name | Type | Description |
---|---|---|
List | String | The name of the list on the SharePoint server. |
ColumnName | String | The name of the column to add. |
DisplayName | String | The display name of the column to add. |
DefaultValue | String | The default value of the column to add. |
ColumnType | String | The data type of the column to add. The valid options are defined by the FieldTypes available in the SharePoint API: https://docs.microsoft.com/en-us/previous-versions/office/sharepoint-csom/ee540543(v=office.15). |
MaxLength | String | The values' maximum length of the column to add. |
PrimaryKey | String | Boolean value indicating whether or not the column should be primary key. |
ReadOnly | String | Boolean value indicating whether or not the column is read only. |
Required | String | Boolean value indicating whether or not the column is required. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
AddUserToGroup¶
Add the user to specified group.
Input¶
Name | Type | Description |
---|---|---|
LoginName | String | The login name of the user. |
Group | String | The group you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | Boolean | Boolean value indicating whether the stored procedure* was successful. |
AddUserToRole¶
Add the user to specified role.
Input¶
Name | Type | Description |
---|---|---|
LoginName | String | The login name of the user. |
Role | String | The role you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | Boolean | Boolean value indicating whether the stored procedure* was successful. |
CheckInDocument¶
Checks in a document to SharePoint and releases the lock on the document.
Input¶
Name | Type | Description |
---|---|---|
File | String | The path of the file you are using to overwrite the document on SharePoint with. For example: C:\myfolder\myfile.txt. |
Library | String | The name of the library on the SharePoint server. For example: Shared Documents. |
Comment | String | A comment to leave when checking the file in. |
RemoteFile | String | The path of the file on the server. This can be the full URL or simply the file name. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
CheckOutDocument¶
Checks out a document from SharePoint.
Input¶
Name | Type | Description |
---|---|---|
Library | String | The name of the library on the SharePoint server. |
RemoteFile | String | The path of the file on the server. This can be the full URL or simply the file name. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
CopyDocument¶
Copies a document from the SharePoint library.
Note
This procedure makes use of indexed parameters
. These input parameters are denoted with a #
character at the end of their names.
Indexed parameters facilitate providing multiple instances a single parameter as inputs for the procedure.
Suppose there is an input parameter named Param#. Input multiple instances of an indexed parameter like this:
EXEC ProcedureName Param#1 = "value1", Param#2 = "value2", Param#3 = "value3"
Input¶
Name | Type | Description |
---|---|---|
DocumentName | String | The name of the document in the document library to be copied. |
DocumentLibrary | String | The name of the document library the document is currently stored on. |
NewDocumentLibrary | String | The name of the document library the document is being copied to. |
NewDocumentName | String | The new name of the document once it has been copied. If left blank, this will be the same as the DocumentName. |
MetadataName# | String | The name of a metadata field to be set for the document. |
ReturnID | String | The return ID of the document. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
\_dlc_DocId | String | The document ID. |
\_dlc_DocIdUrl | String | The URL of the document ID. |
Vti_author | String | The creator of the document. |
Vti_etag | String | The e-tag of the document. |
ID | String | The ID of the document. |
FileRef | String | The file reference of the document. |
CreateFolder¶
Adds a folder to a document library on a SharePoint site.
Input¶
Name | Type | Description |
---|---|---|
Library | String | The name of the library on the SharePoint server. |
Name | String | Name of the folder to which the document is to be added. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the stored procedure* was successful. |
CreateSchema¶
Creates the schema file for the specified SharePoint list. The schema file may be customized manually to exclude unwanted columns or include additional information about columns.
Input¶
Name | Type | Accepts Output Streams | Description |
---|---|---|---|
Table | String | False | The name of the table for which to create a schema. |
TableDescription | String | False | An optional description of the table. |
FileStream | String | True | OutputStream to write the created schema |
WriteToFile | String | False | Whether to write to an output file or not. Defaults to true, must be set to false to write to FileStream or FileData. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Returns Success or Failure. |
SchemaFile | String | The generated schema file. |
Columns | String | The number of columns found. |
FileData | String | The generated schema encoded in base64. Only returned if WriteToFile set to false and FileStream is not set. |
DeleteAttachment¶
Delete an attachment from a SharePoint list item.
Input¶
Name | Type | Description |
---|---|---|
URL | String | Full URL to attachment to be deleted. |
List | String | The name of the List on the SharePoint server. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the stored procedure* was successful. |
DeleteDocument¶
Delete a document on the SharePoint library.
Input¶
Name | Type | Description |
---|---|---|
Library | String | The name of the library on the SharePoint server. |
Path | String | The path of the file (or folder) to remove from the document library. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the stored procedure* was successful. |
DeleteList¶
Permanently deletes a list from a SharePoint site.
Input¶
Name | Type | Description |
---|---|---|
List | String | The name of the list on the SharePoint server. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
DeleteListColumn¶
Deletes a column from the specified list.
Input¶
Name | Type | Description |
---|---|---|
List | String | The name of the list on the SharePoint server. |
ColumnName | String | The name of the column to delete. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
DeleteUserFromGroup¶
Delete the user from specified group.
Input¶
Name | Type | Description |
---|---|---|
LoginName | String | The login name of the user. |
Group | String | The group you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | Boolean | Boolean value indicating whether the stored procedure* was successful. |
DeleteUserFromRole¶
Delete the user from specified role.
Input¶
Name | Type | Description |
---|---|---|
LoginName | String | The login name of the user. |
Role | String | The role you are adding a user to and selecting or deleting the user from. This is an input-only value and either Group or Role must be specified for inserts and selects, but may be optionally specified for deletions. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | Boolean | Boolean value indicating whether the stored procedure* was successful. |
DiscardCheckOutDocument¶
Discards a check out on a document in SharePoint. This does not check a new file into SharePoint. It only releases the lock on the document.
Input¶
Name | Type | Description |
---|---|---|
Library | String | The name of the library on the SharePoint server. |
RemoteFile | String | The name of the file being checked out. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
DownloadAttachment¶
Download a document from the SharePoint list.
Input¶
Name | Type | Accepts Output Streams | Description |
---|---|---|---|
File | String | False | The path of the file to be saved. |
RemoteFile | String | False | The path of the file on the server. This can be the full URL or simply the file name. If you use the name of the file, the latest version will be downloaded. |
FileStream | String | True | OutputStream to write the downloaded attachment. Only returned if File is not set. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
FileData | String | The downloaded BASE64 encoded file content. Only returned if File and FileStream is not set. |
DownloadDocument¶
Download a document from the SharePoint library.
Input¶
Name | Type | Accepts Output Streams | Description |
---|---|---|---|
File | String | False | The path of the file to be saved. |
Library | String | False | The name of the library on the SharePoint server. |
RemoteFile | String | False | The path of the file on the server. This can be the full URL or simply the file name. If you use the name of the file, the latest version will be downloaded. |
FileStream | String | True | OutputStream to write the downloaded document. Only returned if File is not set. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
FileData | String | The downloaded file content. Only returned if File and FileStream is not set. |
MoveAttachmentOrDocument¶
Moves a document or attachment from a source folder to a destination folder.
The MoveAttachmentOrDocument stored procedure* requires the List, SourceFileURL and DestinationFolderURL parameters to move an attachment or document.
For Example:
EXEC MoveAttachmentOrDocument List = 'Test134', SourceFileURL = '/Shared Documents/Dummy.txt', DestinationFolderURL = '/Shared Documents/MySite'
Input¶
Name | Type | Description |
---|---|---|
List | String | Name of the list from which you want to move the document or attachment. |
SourceFileURL | String | URL of the source file, relative to the base URL supplied in the connector's connection properties. For example: Root Directory file: /Shared Documents/filename.txt Sub-directory file:/Shared Documents/MyFolder/filename.txt If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL. |
DestinationFolderURL | String | URL of the destination folder where you want to move the file, relative to the base URL supplied in the connector's connection properties. For example: Root Directory: /Shared Documents/ Sub-directory:/Shared Documents/MyFolder/ If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Value indicating whether the operation was successful. |
RenameAttachmentOrDocument¶
Renames a document or attachment.
The RenameAttachmentOrDocument stored procedure* requires the List, SourceFileURL and NewFileName parameters to rename an attachment or document.
For Example:
EXEC RenameAttachmentOrDocument List = 'Test134', SourceFileURL = '/Shared Documents/Dummy.txt', NewFileName = 'Dummy1.txt'
Input¶
Name | Type | Description |
---|---|---|
List | String | Name of the list containing the document or attachment you would like to rename. |
SourceFileURL | String | URL of the file you want to rename, relative to the base URL supplied in the connector's connection properties. For example: Root Directory file: /Shared Documents/filename.txt Sub-directory file:/Shared Documents/MyFolder/filename.txt If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL. |
NewFileName | String | New name of the file, with extension. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Value indicating whether the operation was successful. |
UpdateList¶
Updates a list on a SharePoint site.
Input¶
Name | Type | Description |
---|---|---|
List | String | The name of the list on the SharePoint server. |
AllowMultiResponses | String | Set to True to allow multiple responses to the survey. |
Description | String | A string that contains the description for the list. |
Direction | String | A string that contains LTR if the reading order is left-to-right, RTL if it is right-to-left, or None. |
EnableAssignedToEmail | String | Set to True to enable assigned-to e-mail for the issues list. |
EnableAttachments | String | Set to True to enable attachments to items in the list. Does not apply to document libraries. |
EnableModeration | String | Set to True to enable Content Approval for the list. |
EnableVersioning | String | Set to True to enable versioning for the list. |
Hidden | String | Set to True to hide the list so that it does not appear on the Documents and Lists page, Quick Launch bar, Modify Site Content page, or Add Column page as an option for lookup fields. |
MultipleDataList | String | Set to True to specify that the list in a Meeting Workspace site contains data for multiple meeting instances within the site. |
Ordered | String | Set to True to specify that the option to allow users to reorder items in the list is available on the Edit View page for the list. |
ShowUser | String | Set to True to specify that names of users are shown in the results of the survey. |
Title | String | A string that contains the title of the list. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
UpdateListColumn¶
Updates a column to the specified list.
Input¶
Name | Type | Description |
---|---|---|
List | String | The name of the list on the SharePoint server. |
ColumnName | String | The name of the column to update. |
DisplayName | String | The updated value of the display name. |
DefaultValue | String | The updated default value of the specified column. |
ColumnType | String | The updated data type of the specified column. |
MaxLength | String | The updated maximum length of the specified column. |
PrimaryKey | String | Use this to make or not the exisitng column, primary key. |
ReadOnly | String | Use this to make or not the exisitng column, readonly. |
Required | String | Use this to make or not the exisitng column, required. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
UploadDocument¶
Upload a document to the SharePoint library.
Note
This procedure makes use of indexed parameters
. These input parameters are denoted with a #
character at the end of their names.
Indexed parameters facilitate providing multiple instances a single parameter as inputs for the procedure.
Suppose there is an input parameter named Param#. Input multiple instances of an indexed parameter like this:
EXEC ProcedureName Param#1 = "value1", Param#2 = "value2", Param#3 = "value3"
Input¶
Name | Type | Accepts Input Streams | Description |
---|---|---|---|
File | String | False | The path of the file to be added. |
FileContent | String | False | Base64 encoded content of the file to be added. If specified, the value of 'File' input will be ignored. |
Library | String | False | The URL of the directory you would like to upload file(s) to, relative to the base URL supplied in the connector's connection properties. For example: Root directory: Shared Documents If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL. |
Name | String | False | The name assigned to the new file. If uploading to the root directory of the site, supply only the filename. If uploading to a folder nested at some level inside the root directory, prepend the full parent subdirectory. For example: Root Directory: filename.txt Sub-directory:MyFolder/filename.txt |
Content | String | True | The content as InputStream to be uploaded when File is not specified. |
ReturnID | String | False | Boolean value indicating whether to return the ID and other metadata fields of the newly created record. An extra API request is needed to get this value. The default value is false. |
MetadataName# | String | False | The name of a metadata field to be set for the document. It must be the column name present in the table of library. |
MetadataValue# | String | False | The value of a metadata field to be set for the document. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the stored procedure* was successful. |
ID | String | The internal ID of the document. |
\_dlc_DocId | String | The document ID. |
\_dlc_DocIdUrl | String | The URL of the document ID. |
Vti_author | String | The creator of the document. |
Vti_etag | String | The e-tag of the document. |
FileRef | String | The file reference of the document. |
REST Data Model¶
The Jitterbit Connector for Microsoft SharePoint models Microsoft SharePoint entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained based on your Microsoft SharePoint site. Any changes you make, such as adding a custom field or changing a field's data type, are automatically reflected when you connect.
Tables
Tables describes the available tables.
Lists in your Microsoft SharePoint site are exposed as relational tables dynamically. Which means any change you make in your lists, i.e adding new list or adding new fields, will be reflected on the driver.
Views
Views are tables that cannot be modified. Typically, read-only data are shown as views.
Stored Procedures
Stored Procedures are function-like interfaces to the data source. They surface additional capabilities of the Microsoft SharePoint API such as searching, updating, and modifying information.
Use OData standard
Since the REST API is OData based, server side filters, are done using OData standard. So the driver takes the most of the server filtering, by reading the metadata file and determing which filters can be done on the server.
Note
When executing "SELECT *" queries, the Microsoft SharePoint REST API response, does not return all the available fields. So to avoid too many null values, the provider will select all the columns explicitly using the $select
filter. However, the provider will do this only if the $select
filter's length is not bigger than 1500, to avoid an error from Microsoft SharePoint REST API regarding the URL length. This is a limitation of the Microsoft SharePoint REST API, so in these cases, the only way to see the actual value of some columns, is to explicitly select them in your query.
Views¶
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.
Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.
Jitterbit Connector for Microsoft SharePoint Views¶
Name | Description |
---|---|
Attachments | Read Attachments for the specified item on the specified list. |
Files | Query the available files on your sharepoint site. |
Groups | Query the available groups on your sharepoint site. |
Lists | Query the available lists on your sharepoint site. |
RoleAssignmentMember | Get Web RoleAssignments member. |
RoleAssignments | Get Web RoleAssignments. |
RoleDefinitionBindings | Get Web Role definition binding. |
Roles | Query the roles your users can have. |
Subsites | This lists the available subsites. |
Users | Query the available users on your sharepoint site. |
Attachments¶
Read Attachments for the specified item on the specified list.
Table Specific Information¶
Select¶
Note
List and ItemId are required to return Attachments.
List can be fetched from the Lists view(Title column).
SELECT * FROM Attachments WHERE List = 'TestApp' AND ItemID = 1
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the attachment on the item. | |
Updated | Datetime | The updated date of the attachment on the item. | |
FileName | String | The FileName of the attachment on the item. | |
ServerRelativePath_DecodedUrl | String | The ServerRelativePath DecodedUrl of the attachment on the item. | |
FileNameAsPath_DecodedUrl | String | The FileNameAsPath DecodedUrl of the attachment on the item. | |
ServerRelativeUrl | String | The ServerRelativeUrl of the attachment on the item. | |
List | String | The internal name of the list to retrieve attachments from. | |
ItemID | String | The ID of the item on the list to retrieve attachments from. |
Files¶
Query the available files on your sharepoint site.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | ||
CreatedBy_Id | String | ||
CreatedBy_Name | String | ||
CreatedBy_Puid | String | ||
ETag | String | ||
LastModifiedBy_Id | String | ||
LastModifiedBy_Name | String | ||
LastModifiedBy_Puid | String | ||
Name | String | ||
Size | Int | ||
TimeCreated | Datetime | ||
TimeLastModified | Datetime | ||
Url | String |
Groups¶
Query the available groups on your sharepoint site.
Table Specific Information¶
Select¶
SELECT * FROM Groups
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | Int | The Group Id. | |
AllowMembersEditMembership | Bool | ||
AllowRequestToJoinLeave | Bool | ||
AutoAcceptRequestToJoinLeave | Bool | ||
CanCurrentUserEditMembership | Bool | ||
CanCurrentUserManageGroup | Bool | ||
CanCurrentUserViewMembership | Bool | ||
Description | String | ||
OnlyAllowMembersViewMembership | Bool | ||
OwnerTitle | String | ||
RequestToJoinLeaveEmailSetting | String | ||
LinkedOwner | String | ||
LinkedUsers | String |
Lists¶
Query the available lists on your sharepoint site.
Columns¶
Name | Type | References | Description |
---|---|---|---|
HasUniqueRoleAssignments [KEY] | Bool | ||
LinkedFirstUniqueAncestorSecurableObject | String | ||
LinkedRoleAssignments | String | ||
ServerRelativeUrl | String | The relative URL of the file based on the URL for the server. This column is available when the URL is a subsite. | |
Id [KEY] | String | ||
AllowContentTypes | Bool | ||
AllowDeletion | Bool | ||
BaseTemplate | Int | ||
BaseType | Int | ||
BrowserFileHandling | Int | ||
ContentTypesEnabled | Bool | ||
CrawlNonDefaultViews | Bool | ||
Created | Datetime | ||
CurrentChangeToken_StringValue | String | ||
CustomActionElements_Items | String | ||
DataSource_Properties | String | ||
DefaultContentApprovalWorkflowId | String | ||
DefaultDisplayFormUrl | String | ||
DefaultEditFormUrl | String | ||
DefaultItemOpenUseListSetting | Bool | ||
DefaultNewFormUrl | String | ||
DefaultViewPath_DecodedUrl | String | ||
DefaultViewUrl | String | ||
Description | String | ||
Direction | String | ||
DisableGridEditing | Bool | ||
DocumentTemplateUrl | String | ||
DraftVersionVisibility | Int | ||
EffectiveBasePermissions_High | Long | ||
EffectiveBasePermissions_Low | Long | ||
EffectiveBasePermissionsForUI_High | Long | ||
EffectiveBasePermissionsForUI_Low | Long | ||
EnableAssignToEmail | Bool | ||
EnableAttachments | Bool | ||
EnableFolderCreation | Bool | ||
EnableMinorVersions | Bool | ||
EnableModeration | Bool | ||
EnableRequestSignOff | Bool | ||
EnableVersioning | Bool | ||
EntityTypeName | String | ||
ExcludeFromOfflineClient | Bool | ||
ExemptFromBlockDownloadOfNonViewableFiles | Bool | ||
FileSavePostProcessingEnabled | Bool | ||
ForceCheckout | Bool | ||
HasExternalDataSource | Bool | ||
Hidden | Bool | ||
ImagePath_DecodedUrl | String | ||
ImageUrl | String | ||
IrmEnabled | Bool | ||
IrmExpire | Bool | ||
IrmReject | Bool | ||
IsApplicationList | Bool | ||
IsCatalog | Bool | ||
IsEnterpriseGalleryLibrary | Bool | ||
IsPrivate | Bool | ||
IsSiteAssetsLibrary | Bool | ||
IsSystemList | Bool | ||
ItemCount | Int | ||
LastItemDeletedDate | Datetime | ||
LastItemModifiedDate | Datetime | ||
LastItemUserModifiedDate | Datetime | ||
ListExperienceOptions | Int | ||
ListItemEntityTypeFullName | String | ||
MajorVersionLimit | Int | ||
MajorWithMinorVersionsLimit | Int | ||
MultipleDataList | Bool | ||
NoCrawl | Bool | ||
OnQuickLaunch | Bool | ||
PageRenderType | Int | ||
ParentWebPath_DecodedUrl | String | ||
ParentWebUrl | String | ||
ParserDisabled | Bool | ||
ReadSecurity | Int | ||
SchemaXml | String | ||
ServerTemplateCanCreateFolders | Bool | ||
TemplateFeatureId | String | ||
Title | String | ||
ValidationFormula | String | ||
ValidationMessage | String | ||
WriteSecurity | Int | ||
LinkedContentTypes | String | ||
LinkedCreatablesInfo | String | ||
LinkedDefaultView | String | ||
LinkedDescriptionResource | String | ||
LinkedEventReceivers | String | ||
LinkedFields | String | ||
LinkedForms | String | ||
LinkedInformationRightsManagementSettings | String | ||
LinkedItems | String | ||
LinkedParentWeb | String | ||
LinkedRootFolder | String | ||
LinkedSubscriptions | String | ||
LinkedTitleResource | String | ||
LinkedUserCustomActions | String | ||
LinkedViews | String | ||
LinkedWorkflowAssociations | String |
RoleAssignmentMember¶
Get Web RoleAssignments member.
Table Specific Information¶
Select¶
Note
PrincipalId is required to return RoleAssignmentMember.
SELECT * FROM RoleAssignmentMember WHERE PrincipalId = 3
SELECT * FROM RoleAssignmentMember WHERE List = 'TestApp' AND PrincipalId = 3
SELECT * FROM RoleAssignmentMember WHERE PrincipalId = 5 AND list = 'KatsunariMatsumoto' AND ItemId = '3'
Columns¶
Name | Type | References | Description |
---|---|---|---|
ID [KEY] | String | The ID of the role assigned member. | |
Updated | Datetime | The updated date for role assigned member. | |
IsHiddenInUI | Boolean | A boolean indicating if the assigned role member is hidden in UI. | |
LoginName | String | The login name of the role assigned member. | |
Title | String | The title of the role assigned member. | |
AllowMembersEditMembership | Boolean | A boolean indicating whether to allow members edit membership. | |
AllowRequestToJoinLeave | Boolean | A boolean indicating whether to allow request to join leave. | |
AutoAcceptRequestToJoinLeave | Boolean | A boolean indicating whether to auto accept request to join leave. | |
Description | String | The description of the role assigned member. | |
OnlyAllowMembersViewMembership | Boolean | A boolean indicating whether to only allow members view membership. | |
OwnerTitle | String | The owner title of the role assigned member. | |
RequestToJoinLeaveEmailSetting | String | The request to join leave email setting of the role assigned member. | |
PrincipalId | String | RoleAssignments.PrincipalId | The Principal Id. |
List | String | Lists.Title | The internal name of the list to retrieve role assigned member from. |
ItemId | String | List item ID for role assignment. |
RoleAssignments¶
Get Web RoleAssignments.
Table Specific Information¶
Select¶
SELECT * FROM RoleAssignments WHERE List = 'TestApp'
SELECT * FROM RoleAssignments WHERE PrincipalId = 5 AND list = 'KatsunariMatsumoto' AND ItemId = '3'
Columns¶
Name | Type | References | Description |
---|---|---|---|
ID [KEY] | String | The ID of the role assigned. | |
PrincipalId | Long | The Principal ID for role assigned. | |
Updated | Datetime | The updated date for role assigned. | |
List | String | Lists.Title | The internal name of the list to retrieve role assigned from. |
ItemId | String | List item ID for role assignment. |
RoleDefinitionBindings¶
Get Web Role definition binding.
Table Specific Information¶
Select¶
Note
PrincipalId is required to return RoleDefinitionBindings.
SELECT * FROM RoleDefinitionBindings WHERE PrincipalId = 3
SELECT * FROM RoleDefinitionBindings WHERE List = 'TestApp' AND PrincipalId = 3
SELECT * FROM RoleDefinitionBindings WHERE PrincipalId = 5 AND list = 'KatsunariMatsumoto' AND ItemId = '3'
Columns¶
Name | Type | References | Description |
---|---|---|---|
ID [KEY] | String | The ID of the role assigned member. | |
BasePermissions_High | Int64 | The base permissions high. | |
BasePermissions_Low | Int64 | The base permissions low. | |
Description | String | The description. | |
Hidden | Boolean | A boolean indicating if it is hidden. | |
Name | String | The name. | |
Order | Int | The order. | |
RoleTypeKind | Int | The role type kind. | |
PrincipalId | String | RoleAssignments.PrincipalId | The Principal Id. |
List | String | Lists.Title | The internal name of the list to retrieve role assigned member from. |
ItemId | String | List item ID for role defintion. |
Roles¶
Query the roles your users can have.
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | Int | ||
BasePermissions_High | Long | ||
BasePermissions_Low | Long | ||
Description | String | ||
Hidden | Bool | ||
Name | String | ||
Order | Int | ||
RoleTypeKind | Int |
Subsites¶
This lists the available subsites.
Table Specific Information¶
Select¶
SELECT * FROM Subsites
Columns¶
Name | Type | References | Description |
---|---|---|---|
Id [KEY] | String | The ID of the subsite. | |
AllowRssFeeds | Boolean | The AllowRssFeeds of the subsite. | |
AlternateCssUrl | String | The AlternateCssUrl of the subsite. | |
AppInstanceId | String | The AppInstanceId of the subsite. | |
ClassicWelcomePage | String | The ClassicWelcomePage of the subsite. | |
Configuration | Int | The Configuration of the subsite. | |
Created | Datetime | The Created of the subsite. | |
CurrentChangeToken_StringValue | String | The CurrentChangeToken_StringValue of the subsite. | |
CustomMasterUrl | String | The CustomMasterUrl of the subsite. | |
Description | String | The Description of the subsite. | |
DesignPackageId | String | The DesignPackageId of the subsite. | |
DocumentLibraryCalloutOfficeWebAppPreviewersDisabled | Boolean | The DocumentLibraryCalloutOfficeWebAppPreviewersDisabled of the subsite. | |
EnableMinimalDownload | Boolean | The EnableMinimalDownload of the subsite. | |
FooterEmphasis | Int | The FooterEmphasis of the subsite. | |
FooterEnabled | Boolean | The FooterEnabled of the subsite. | |
FooterLayout | Int | The FooterLayout of the subsite. | |
HeaderEmphasis | Int | The HeaderEmphasis of the subsite. | |
HeaderLayout | Int | The HeaderLayout of the subsite. | |
HideTitleInHeader | Boolean | The HideTitleInHeader of the subsite. | |
HorizontalQuickLaunch | Boolean | The HorizontalQuickLaunch of the subsite. | |
IsHomepageModernized | Boolean | The IsHomepageModernized of the subsite. | |
IsMultilingual | Boolean | The IsMultilingual of the subsite. | |
IsRevertHomepageLinkHidden | Boolean | The IsRevertHomepageLinkHidden of the subsite. | |
KeepFieldUserResources | Boolean | The KeepFieldUserResources of the subsite. | |
Language | Int | The Language of the subsite. | |
LastItemModifiedDate | Datetime | The LastItemModifiedDate of the subsite. | |
LastItemUserModifiedDate | Datetime | The LastItemUserModifiedDate of the subsite. | |
LogoAlignment | Int | The LogoAlignment of the subsite. | |
MasterUrl | String | The MasterUrl of the subsite. | |
MegaMenuEnabled | Boolean | The MegaMenuEnabled of the subsite. | |
NavAudienceTargetingEnabled | Boolean | The NavAudienceTargetingEnabled of the subsite. | |
NoCrawl | Boolean | The NoCrawl of the subsite. | |
ObjectCacheEnabled | Boolean | The ObjectCacheEnabled of the subsite. | |
OverwriteTranslationsOnChange | Boolean | The OverwriteTranslationsOnChange of the subsite. | |
ResourcePath_DecodedUrl | String | The ResourcePath_DecodedUrl of the subsite. | |
QuickLaunchEnabled | Boolean | The QuickLaunchEnabled of the subsite. | |
RecycleBinEnabled | Boolean | The RecycleBinEnabled of the subsite. | |
SearchScope | Int | The SearchScope of the subsite. | |
ServerRelativeUrl | String | The ServerRelativeUrl of the subsite. | |
SiteLogoUrl | String | The SiteLogoUrl of the subsite. | |
SyndicationEnabled | Boolean | The SyndicationEnabled of the subsite. | |
TenantAdminMembersCanShare | Int | The TenantAdminMembersCanShare of the subsite. | |
Title | String | The Title of the subsite. | |
TreeViewEnabled | Boolean | The TreeViewEnabled of the subsite. | |
UIVersion | Int | The UIVersion of the subsite. | |
UIVersionConfigurationEnabled | Boolean | The UIVersionConfigurationEnabled of the subsite. | |
Url | String | The Url of the subsite. | |
WebTemplate | String | The WebTemplate of the subsite. | |
WelcomePage | String | The WelcomePage of the subsite. |
Users¶
Query the available users on your sharepoint site.
Table Specific Information¶
Select¶
SELECT * FROM Users // Fetch all the Users
SELECT * FROM Users WHERE GroupId = 5 // Fetch a user for a particular Group
Columns¶
Name | Type | References | Description |
---|---|---|---|
AadObjectId_NameId | String | ||
AadObjectId_NameIdIssuer | String | ||
Email | String | ||
Expiration | String | ||
IsEmailAuthenticationGuestUser | Bool | ||
IsShareByEmailGuestUser | Bool | ||
IsSiteAdmin | Bool | ||
UserId_NameId | String | ||
UserId_NameIdIssuer | String | ||
UserPrincipalName | String | ||
LinkedAlerts | String | ||
LinkedGroups | String | ||
LoginName | String | The login name of the user. | |
Title | String | The Title of the user. | |
IsHiddenInUI | Boolean | A boolean indicating if the user is hidden in UI. | |
GroupId | Int | The group ID the user is added to. |
Stored Procedures¶
Stored procedures* are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Microsoft SharePoint.
Stored procedures* accept a list of parameters, perform their intended function, and then return any relevant response data from Microsoft SharePoint, along with an indication of whether the procedure succeeded or failed.
Jitterbit Connector for Microsoft SharePoint Stored Procedures¶
Name | Description |
---|---|
AddAttachment | Add an attachment into a sharepoint list. |
CheckInDocument | Check in specific document inside document library. |
CheckOutDocument | Check out specific document inside document library. |
CopyDocument | Copies the file to the destination URL.. |
CreateFolder | Adds a folder to a document library on a SharePoint site. |
DeleteAttachment | Deletes an attachment from Sharepoint list. |
DeleteDocument | Delete a Document from the SharePoint document library. |
DiscardCheckOutDocument | Reverts an existing checkout for the file. |
DownloadAttachment | Download a document from the SharePoint list. |
DownloadDocument | Download a document from the SharePoint library. |
GetAdminConsentURL | Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials. |
GetCurrentUser | Retrieves information about the current logged in user. |
GetOAuthAccessToken | Gets the OAuth access token from SharePoint. |
GetOAuthAuthorizationURL | Gets the SharePoint authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to SharePoint. |
MoveAttachmentOrDocument | Moves a document or attachment from a source folder to a destination folder. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with SharePoint. |
RenameAttachmentOrDocument | Renames a document or attachment. |
UploadDocument | Uploads a document into the SharePoint library. |
AddAttachment¶
Add an attachment into a sharepoint list.
Input¶
Name | Type | Accepts Input Streams | Description |
---|---|---|---|
ListTitle | String | False | Title of the list item. |
ItemId | String | False | ID of the list item. |
FileName | String | False | Name of the File to be added in document library |
InputFilePath | String | False | Location of the file to be attached. |
Content | String | True | The content as InputStream to be uploaded when InputFilePath is not specified. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
RelativeUrl | String | Relative URL of the attachment that has been added. |
Status | String | Message indicating whether the operation was successful or not. |
CheckInDocument¶
Check in specific document inside document library.
Input¶
Name | Type | Description |
---|---|---|
RelativeURL | String | Relative URL of the folder. |
DocumentName | String | Name of the File to be Checked in. |
Comment | String | Optional message while Checking in Document. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Status | String | Message indicating whether the operation was successful or not. |
CheckOutDocument¶
Check out specific document inside document library.
Input¶
Name | Type | Description |
---|---|---|
RelativeURL | String | Relative URL of the folder. |
DocumentName | String | Name of the File to be Checked in. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Status | String | Message indicating whether the operation was successful or not. |
CopyDocument¶
Copies the file to the destination URL..
Input¶
Name | Type | Description |
---|---|---|
SourceFileRelativeUrl | String | Relative URL of the source file. |
DestFileRelativeUrl | String | Relative URL of the destination file. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Status | String | Message indicating whether the operation was successful or not. |
CreateFolder¶
Adds a folder to a document library on a SharePoint site.
Input¶
Name | Type | Description |
---|---|---|
RelativeURL | String | Relative URL of the folder. |
FolderName | String | Name of the folder to be created. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Id | String | Unique identifier Value Returned after completing the operation. |
Status | String | Message indicating whether the operation was successful or not. |
DeleteAttachment¶
Deletes an attachment from Sharepoint list.
Input¶
Name | Type | Description |
---|---|---|
ListTitle | String | Title of the list item. |
ItemId | String | ID of the list item. |
FileName | String | Name of the File to be added in document library |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Status | String | Message indicating whether the operation was successful or not. |
DeleteDocument¶
Delete a Document from the SharePoint document library.
Input¶
Name | Type | Description |
---|---|---|
RelativeURL | String | Relative URL of the Folder. |
DocumentName | String | Name of the File to be Deleted. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Status | String | Message indicating whether the operation was successful or not. |
DiscardCheckOutDocument¶
Reverts an existing checkout for the file.
Input¶
Name | Type | Description |
---|---|---|
RelativeURL | String | Relative URL of the folder. |
DocumentName | String | Name of the File to be Checked in. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Status | String | Message indicating whether the operation was successful or not. |
DownloadAttachment¶
Download a document from the SharePoint list.
Stored Procedure* Specific Information¶
Insert¶
RemoteFile can be both relative to the server, or it can be the full URL of the file. Below are some examples:
EXECUTE DownloadAttachment File = 'C:/Users/User/Desktop/DownloadedFile.txt', RemoteFile = 'https://mysite.sharepoint.com/Lists/MyCustomList/Attachments/1/FileToDownload.txt';
EXECUTE DownloadAttachment File = 'C:/Users/User/Desktop/DownloadedFile.txt', RemoteFile = '/Lists/MyCustomList/Attachments/1/FileToDownload.txt';
Input¶
Name | Type | Accepts Output Streams | Description |
---|---|---|---|
File | String | False | The path of the file to be saved. |
RemoteFile | String | False | The path of the file on the server. This can be the full URL or simply the file name. If you use the name of the file, the latest version will be downloaded. |
Encoding | String | False | The data will be output to FileData in the specified encoding. The allowed values are NONE, BASE64. The default value is BASE64. |
FileStream | String | True | OutputStream to write the downloaded attachment. Only used if File is not provided. |
ReadTimeout | String | False | How many seconds the download may run. If given the download always fails if it runs longer than this, unlike Timeout which only triggers if the download stalls. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
FileData | String | The downloaded file content. Only returned if File and FileStream is not set. |
DownloadDocument¶
Download a document from the SharePoint library.
Stored Procedure* Specific Information¶
Insert¶
RemoteFile can be both relative to the library, or it can be the full URL of the file. Below are some examples:
EXECUTE DownloadDocument File = 'C:/Users/User/Desktop/DownloadedFile.txt', Library = 'Shared Documents', RemoteFile = 'https://mysite.sharepoint.com/Shared Documents/newFolder/FileToDownload.txt';
EXECUTE DownloadDocument File = 'C:/Users/User/Desktop/DownloadedFile.txt', Library = 'Shared Documents', RemoteFile = '/newFolder/FileToDownload.txt';
Input¶
Name | Type | Accepts Output Streams | Description |
---|---|---|---|
File | String | False | The path of the file to be saved. You should include the new filename. For example, 'C:/Users/User/Desktop/DownloadedFile.txt'. |
Library | String | False | The name of the library on the SharePoint server. For example, 'Shared Documents'. |
RemoteFile | String | False | This can be either the relative path to the library or the full URL of the file. |
Encoding | String | False | The data will be output to FileData in the specified encoding. The allowed values are NONE, BASE64. The default value is BASE64. |
FileStream | String | True | OutputStream to write the downloaded document. Only returned if File is not set. |
ReadTimeout | String | False | How many seconds the download may run. If given the download always fails if it runs longer than this, unlike Timeout which only triggers if the download stalls. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Boolean value indicating whether the operation was successful. |
FileData | String | The downloaded file content. Only returned if File and FileStream is not set. |
GetAdminConsentURL¶
Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.
Input¶
Name | Type | Description |
---|---|---|
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Reply URL in the Azure AD app settings. |
State | String | The same value for state that you sent when you requested the authorization code. |
Scope | String | The scope or permissions you are requesting from the Admin The default value is AllSites.Manage. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
URL | String | The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app. |
GetCurrentUser¶
Retrieves information about the current logged in user.
Result Set Columns¶
Name | Type | Description |
---|---|---|
Id | Int | The ID of the user. |
Title | String | The title of the user. |
Email | String | The email of the user. |
IsSiteAdmin | Boolean | Whether the user is a site admin or not. |
GetOAuthAccessToken¶
Gets the OAuth access token from SharePoint.
Input¶
Name | Type | Description |
---|---|---|
AuthMode | String | The type of authentication mode to use. The allowed values are APP, WEB. |
Verifier | String | The verifier token returned by SharePoint after using the URL obtained with GetOAuthAuthorizationURL. Required for only the Web AuthMode. |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. |
Scope | String | The scope or permissions you are requesting from the user. |
State | String | Any value that you wish to be sent with the callback. |
Prompt | String | Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The authentication token returned from SharePoint. |
OAuthRefreshToken | String | A token that may be used to obtain a new access token. |
ExpiresIn | String | The remaining lifetime for the access token in seconds. |
GetOAuthAuthorizationURL¶
Gets the SharePoint authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to SharePoint.
Input¶
Name | Type | Description |
---|---|---|
CallbackUrl | String | The URL that Sharepoint will return to after the user has authorized your app. |
Scope | String | The scope or permissions you are requesting from the user. The default value is AllSites.Manage. |
State | String | Any value that you wish to be sent with the callback. |
Prompt | String | Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
URL | String | The URL to be entered into a Web browser to obtain the verifier token and authorize the data provider with. |
MoveAttachmentOrDocument¶
Moves a document or attachment from a source folder to a destination folder.
The MoveAttachmentOrDocument stored procedure* requires the SourceFileURL and DestinationFolderURL parameters to move an attachment or document.
For Example:
EXEC MoveAttachmentOrDocument SourceFileURL = '/Shared Documents/Dummy.txt', DestinationFolderURL = '/Shared Documents/MySite'
Input¶
Name | Type | Description |
---|---|---|
SourceFileURL | String | URL of the source file, relative to the base URL supplied in the connector's connection properties. For example: Root Directory file: /Shared Documents/filename.txt Sub-directory file:/Shared Documents/MyFolder/filename.txt If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL. |
DestinationFolderURL | String | URL of the destination folder where you want to move the file , relative to the base URL supplied in the connector's connection properties. For example: Root Directory: /Shared Documents/ Sub-directory:/Shared Documents/MyFolder/ If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Value indicating whether the operation was successful. |
RefreshOAuthAccessToken¶
Refreshes the OAuth access token used for authentication with SharePoint.
Input¶
Name | Type | Description |
---|---|---|
OAuthRefreshToken | String | The old token to be refreshed. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The authentication token returned from SharePoint. |
ExpiresIn | String | The remaining lifetime on the access token. |
RenameAttachmentOrDocument¶
Renames a document or attachment.
The RenameAttachmentOrDocument stored procedure* requires the SourceFileURL and NewFileName parameters to rename an attachment or document.
For Example:
EXEC RenameAttachmentOrDocument SourceFileURL = '/Shared Documents/Dummy.txt', NewFileName = 'Dummy1.txt'
Input¶
Name | Type | Description |
---|---|---|
SourceFileURL | String | URL of the file you want to rename, relative to the base URL supplied in the connector's connection properties. For example: Root Directory file: /Shared Documents/filename.txt Sub-directory file:/Shared Documents/MyFolder/filename.txt If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL. |
NewFileName | String | New name of the file, with extension. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Result | String | Value indicating whether the operation was successful. |
UploadDocument¶
Uploads a document into the SharePoint library.
Input¶
Name | Type | Accepts Input Streams | Description |
---|---|---|---|
RelativeUrl | String | False | The URL of the folder you would like to upload file(s) to, relative to the base URL supplied in the connector's connection properties. For example: Root folder: Shared Documents Sub-folder: Shared Documents/MyFolder If you set the URL connection property to a site collection, the relative URL will correspond with a path on the base site. If the URL connection property points to a specific site, the relative URL will be relative to the site supplied in the URL. |
InputFilePath | String | False | The location of the file to be uploaded. |
FileName | String | False | Name of the File to be created. |
Overwrite | String | False | Boolean value to overwrite the existing file data. |
Content | String | True | The content as InputStream to be uploaded when InputFilePath is not specified. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
Id | String | Unique identifier Value Returned after completing the operation. |
Status | String | Message indicating whether the operation was successful or not. |
System Tables¶
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
Schema Tables¶
The following tables return database metadata for Microsoft SharePoint:
- sys_catalogs: Lists the available databases.
- sys_schemas: Lists the available schemas.
- sys_tables: Lists the available tables and views.
- sys_tablecolumns: Describes the columns of the available tables and views.
- sys_procedures: Describes the available stored procedures.
- sys_procedureparameters: Describes stored procedure* parameters.
- sys_keycolumns: Describes the primary and foreign keys.
- sys_indexes: Describes the available indexes.
Data Source Tables¶
The following tables return information about how to connect to and query the data source:
- sys_connection_props: Returns information on the available connection properties.
- sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.
Query Information Tables¶
The following table returns query statistics for data modification queries, including batch operations:
- sys_identity: Returns information about batch operations or single updates.
sys_catalogs¶
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
sys_schemas¶
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
sys_tables¶
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
sys_tablecolumns¶
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Calendar table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Calendar'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
sys_procedures¶
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
sys_procedureparameters¶
Describes stored procedure* parameters.
The following query returns information about all of the input parameters for the ListItems stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='ListItems' AND Direction=1 OR Direction=2
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure* containing the parameter. |
ColumnName | String | The name of the stored procedure* parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
sys_keycolumns¶
Describes the primary and foreign keys.
The following query retrieves the primary key for the Calendar table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Calendar'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
sys_foreignkeys¶
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
sys_primarykeys¶
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
sys_indexes¶
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
sys_connection_props¶
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:sharepoint:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Columns¶
Name | Type | Description |
---|---|---|
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
sys_sqlinfo¶
Describes the SELECT query processing that the connector can offload to the data source.
Discover the Data Source's SELECT Capabilities¶
Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
---|---|---|
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG , COUNT , MAX , MIN , SUM , DISTINCT |
COUNT | Whether COUNT function is supported. | YES , NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | = , > , < , >= , <= , <> , != , LIKE , NOT LIKE , IN , NOT IN , IS NULL , IS NOT NULL , AND , OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO , NO_RELATION , EQUALS_SELECT , SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH , CHAR , LOCATE , REPLACE , SUBSTRING , RTRIM , LTRIM , RIGHT , LEFT , UCASE , SPACE , SOUNDEX , LCASE , CONCAT , ASCII , REPEAT , OCTET , BIT , POSITION , INSERT , TRIM , UPPER , REGEXP , LOWER , DIFFERENCE , CHARACTER , SUBSTR , STR , REVERSE , PLAN , UUIDTOSTR , TRANSLATE , TRAILING , TO , STUFF , STRTOUUID , STRING , SPLIT , SORTKEY , SIMILAR , REPLICATE , PATINDEX , LPAD , LEN , LEADING , KEY , INSTR , INSERTSTR , HTML , GRAPHICAL , CONVERT , COLLATION , CHARINDEX , BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS , ACOS , ASIN , ATAN , ATAN2 , CEILING , COS , COT , EXP , FLOOR , LOG , MOD , SIGN , SIN , SQRT , TAN , PI , RAND , DEGREES , LOG10 , POWER , RADIANS , ROUND , TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW , CURDATE , DAYOFMONTH , DAYOFWEEK , DAYOFYEAR , MONTH , QUARTER , WEEK , YEAR , CURTIME , HOUR , MINUTE , SECOND , TIMESTAMPADD , TIMESTAMPDIFF , DAYNAME , MONTHNAME , CURRENT_DATE , CURRENT_TIME , CURRENT_TIMESTAMP , EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES , NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES , NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES , NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard , Starter , Professional , Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source , Destination , Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE , FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES , NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT , INSERT , DELETE , UPDATE , TRANSACTIONS , ORDERBY , OAUTH , ASSIGNEDID , LIMIT , LIKE , BULKINSERT , COUNT , BULKDELETE , BULKUPDATE , GROUPBY , HAVING , AGGS , OFFSET , REPLICATE , COUNTDISTINCT , JOINS , DROP , CREATE , DISTINCT , INNERJOINS , SUBQUERIES , ALTER , MULTIPLESCHEMAS , GROUPBYNORELATION , OUTERJOINS , UNIONALL , UNION , UPSERT , GETDELETED , CROSSJOINS , GROUPBYCOLLATE , MULTIPLECATS , FULLOUTERJOIN , MERGE , JSONEXTRACT , BULKUPSERT , SUM , SUBQUERIESFULL , MIN , MAX , JOINSFULL , XMLEXTRACT , AVG , MULTISTATEMENTS , FOREIGNKEYS , CASE , LEFTJOINS , COMMAJOINS , WITH , LITERALS , RENAME , NESTEDTABLES , EXECUTE , BATCH , BASIC , INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES , NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE , FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE , FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures* that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the SOAP Data Model section for more information.
Columns¶
Name | Type | Description |
---|---|---|
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
sys_identity¶
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
Data Type Mapping¶
Data Type Mappings¶
The connector maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.
Microsoft SharePoint | Schema |
---|---|
Choice (menu) | string |
Currency | float |
Date and Time | datetime |
Hyperlink or Picture | string |
Lookup | string |
Multiple lines of text | string |
Number | float |
Person or Group | string |
Single line of text | string |
Task Outcome | string |
Yes/No | bool |
Advanced Configurations Properties¶
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
---|---|
AuthScheme | The scheme used for authenticating to SharePoint. |
URL | The base URL for the site. |
SharePointEdition | The edition of SharePoint being used. Set either SharePoint Online or SharePoint On-Premise. |
User | The SharePoint user account used to authenticate. |
Password | The password used to authenticate the user. |
Property | Description |
---|---|
AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
AzureEnvironment | The Azure Environment to use when establishing a connection. |
Property | Description |
---|---|
SSOLoginURL | The identity provider's login URL. |
SSODomain | The domain of the user when using single sign-on (SSO). |
SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your Add-In settings. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
Scope | The scope used for the OAuth flow to access data from the Application. |
State | Optional value for representing extra OAuth state information. |
OAuthGrantType | The grant type for the OAuth flow. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
---|---|
OAuthJWTCert | The JWT Certificate store. |
OAuthJWTCertType | The type of key store containing the JWT Certificate. |
OAuthJWTIssuer | The issuer of the Java Web Token. |
Property | Description |
---|---|
KerberosKDC | The Kerberos Key Distribution Center (KDC) service used to authenticate the user. |
KerberosRealm | The Kerberos Realm used to authenticate the user. |
KerberosSPN | The service principal name (SPN) for the Kerberos Domain Controller. |
KerberosKeytabFile | The Keytab file containing your pairs of Kerberos principals and encrypted keys. |
KerberosServiceRealm | The Kerberos realm of the service. |
KerberosServiceKDC | The Kerberos KDC of the service. |
KerberosTicketCache | The full file path to an MIT Kerberos credential cache file. |
Property | Description |
---|---|
SSLClientCert | The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). |
SSLClientCertType | The type of key store containing the TLS/SSL client certificate. |
SSLClientCertPassword | The password for the TLS/SSL client certificate. |
SSLClientCertSubject | The subject of the TLS/SSL client certificate. |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | The type of schema to use. |
Property | Description |
---|---|
CalculatedDataType | The data type to be used for calculated fields. |
ContinueOnError | Indicates whether or not to continue updating items in a batch after an error. |
CreateIDColumns | Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists. |
DisableFilterLimit | Microsoft SharePoint natively supports listing up to 5000 records as they appear in your list filter. Enable this property to pull more than 5000 records from the list filter at the cost of additional performance overhead. |
FolderOption | An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll. |
IncludeLookupColumns | This option controls whether the driver returns the lookup columns defined on a table. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Microsoft SharePoint. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
ShowHiddenColumns | Boolean determining if hidden columns should be shown or not. If false, all hidden columns will be removed from the column listing. |
ShowPredefinedColumns | Boolean determining if predefined columns should be shown or not. If false, all columns derived from a base type will be removed from the column listing. |
ShowVersionViews | Indicate whether to display the view of list versions. Such as ListA_Versions. |
STSURL | The URL of the security token service (STS) when using single sign-on (SSO). |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UseDisplayNames | Boolean determining if the display names for the columns should be used instead of the API names in both SOAP and REST schemas. |
UseEntityTypeName | Boolean determining if the table name should be EntityTypeName instead of the title in the REST schema. |
UseNTLMV1 | Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default). |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
Authentication¶
This section provides a complete list of authentication properties you can configure.
Property | Description |
---|---|
AuthScheme | The scheme used for authenticating to SharePoint. |
URL | The base URL for the site. |
SharePointEdition | The edition of SharePoint being used. Set either SharePoint Online or SharePoint On-Premise. |
User | The SharePoint user account used to authenticate. |
Password | The password used to authenticate the user. |
AuthScheme¶
The scheme used for authenticating to SharePoint.
Data Type¶
string
Default Value¶
NTLM
Remarks¶
If authenticating to Sharepoint On-Premise instance, together with Password and User, this field is used to authenticate against the server. NTLM is the default option. Use the following options to select your authentication scheme:
- NTLM: Set this to use your Windows credentials for authentication.
- Negotiate: If
AuthScheme
is set to Negotiate, the connector will Negotiate an authentication mechanism with the server. SetAuthScheme
to Negotiate if you want to use Kerberos authentication. - None: Set this to use anonymous authentication; for example, to access a public site.
- Basic: Set this to use HTTP Basic authentication.
- ADFS: Set this to use Single Sign-On authentication with ADFS.
If authenticating to Sharepoint Online, OAuth is the default option. Use the following options to select your authentication scheme:
- AzureAD: Set this to perform Azure Active Directory OAuth Authentication.
- AzurePassword: Set this to authenticate using OAuth with Password GrantType.
- OAuthJWT: If
AuthScheme
is set to OAuthJWT, the connector will try to connect with a self signed certificate with Azure AD. - PingFederate: Set this to use Single Sign-On authentication with PingFederate.
- ADFS: Set this to use Single Sign-On authentication with ADFS.
- OneLogin: Set this to use Single Sign-On authentication with OneLogin.
- OKTA: Set this to use Single Sign-On authentication with OKTA.
- OAuth: Set this to use OAuth 2 authentication with GrantType CODE or PASSWORD.
- AzureMSI: Set this to automatically obtain Managed Service Identity credentials when running on an Azure VM.
- SharePointOAuth: Set this to use OAuth authentication with Sharepoint APP for Client Credentials.
URL¶
The base URL for the site.
Data Type¶
string
Default Value¶
""
Remarks¶
The following are examples of valid URLs:
http://server/SharePoint/
http://server/Sites/mysite/
http://server:90/
The provider will use URL
to derive URLs for other calls to the server.
SharePointEdition¶
The edition of SharePoint being used. Set either SharePoint Online or SharePoint On-Premise.
Data Type¶
string
Default Value¶
SharePoint OnPremise
Remarks¶
The edition of SharePoint being used. Set either SharePoint Online or SharePoint On-Premise.
User¶
The SharePoint user account used to authenticate.
Data Type¶
string
Default Value¶
""
Remarks¶
Together with Password, this field is used to authenticate against the SharePoint server.
For SharePoint On-Premise, User should include the domain and will look similar to the following: DOMAIN\Username.
For SharePoint Online, User will look similar to the following: username@domain.onmicrosoft.com.
Password¶
The password used to authenticate the user.
Data Type¶
string
Default Value¶
""
Remarks¶
The User and Password
are together used to authenticate with the server.
Azure Authentication¶
This section provides a complete list of Azure authentication properties you can configure.
Property | Description |
---|---|
AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
AzureEnvironment | The Azure Environment to use when establishing a connection. |
AzureTenant¶
The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
Data Type¶
string
Default Value¶
""
Remarks¶
The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory ID in the Azure Portal > Azure Active Directory > Properties.
Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.
The AzureTenant
is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant
to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.
AzureEnvironment¶
The Azure Environment to use when establishing a connection.
Possible Values¶
GLOBAL
, CHINA
, USGOVT
, USGOVTDOD
Data Type¶
string
Default Value¶
GLOBAL
Remarks¶
In most cases, leaving the environment set to global works. However, if you have added your Azure Account to a different environment, you can use AzureEnvironment
to specify which environment. The available values are GLOBAL, CHINA, USGOVT, USGOVTDOD.
Use this property to resolve issues with the URL suffix (e.g., set it to USGOVT if your URL ends in .us), based on which cloud the you are using. More information about this setting can be found in Microsoft documentation about National clouds
SSO¶
This section provides a complete list of SSO properties you can configure.
Property | Description |
---|---|
SSOLoginURL | The identity provider's login URL. |
SSODomain | The domain of the user when using single sign-on (SSO). |
SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
SSOLoginURL¶
The identity provider's login URL.
Data Type¶
string
Default Value¶
""
Remarks¶
The identity provider's login URL.
SSODomain¶
The domain of the user when using single sign-on (SSO).
Data Type¶
string
Default Value¶
""
Remarks¶
This property is only applicable when using single sign-on (AuthScheme is configured to use an SSO authentication scheme) and if the domain of the User (e.g. user@mydomain.com) is different than the domain configured within the SSO service (e.g. user@myssodomain.com).
This property may be required when using the ADFS, OneLogin, or OKTA SSO services.
SSOProperties¶
Additional properties required to connect to the identity provider in a semicolon-separated list.
Data Type¶
string
Default Value¶
""
Remarks¶
Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.
SSO configuration is discussed further in .
OAuth¶
This section provides a complete list of OAuth properties you can configure.
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your Add-In settings. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
Scope | The scope used for the OAuth flow to access data from the Application. |
State | Optional value for representing extra OAuth state information. |
OAuthGrantType | The grant type for the OAuth flow. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
InitiateOAuth¶
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
Possible Values¶
OFF
, GETANDREFRESH
, REFRESH
Data Type¶
string
Default Value¶
OFF
Remarks¶
The following options are available:
OFF
: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.GETANDREFRESH
: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.REFRESH
: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.
OAuthClientId¶
The client ID assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId
value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
OAuthClientSecret¶
The client secret assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret
property.
OAuthAccessToken¶
The access token for connecting using OAuth.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthAccessToken
property is used to connect using OAuth. The OAuthAccessToken
is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
CallbackURL¶
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your Add-In settings.
Data Type¶
string
Default Value¶
""
Remarks¶
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your Add-In settings.
OAuthSettingsLocation¶
The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'
.
Data Type¶
string
Default Value¶
%APPDATA%\SharePoint Data Provider\OAuthSettings.txt
Remarks¶
When InitiateOAuth is set to GETANDREFRESH
or REFRESH
, the connector saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.
Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://'
followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props
system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.
The default location is "%APPDATA%\SharePoint Data Provider\OAuthSettings.txt" with %APPDATA%
set to the user's configuration directory. The default values are
- Windows: "
register://%DSN
" - Unix: "%AppData%..."
- Mac: "%AppData%..."
where DSN is the name of the current DSN used in the open connection.
The following table lists the value of %APPDATA%
by OS:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
Scope¶
The scope used for the OAuth flow to access data from the Application.
Possible Values¶
NONE
, AllSites.Manage
, AllSites.Read
, AllSites.Write
, .default
Data Type¶
string
Default Value¶
NONE
Remarks¶
The scope used for the OAuth flow to access data from the Application.
Note
If not specified or left to 'NONE', the connector sets the default scope dependant upon OAuthGrantType. Defaults to 'AllSites.Manage' unless using JWT OAuth, Azure Service Principal, or CLIENT grant type. The available values are:
- AllSites.Read: Enables reading custom lists
- AllSites.Write: Enables reading & writing custom lists
- AllSites.Manage: Enables reading, writing and creating custom lists
- .default: Requests application permissions without a user context. All the application permissions that have been granted for that web API are included in the retrieved OAuthAccessToken.
State¶
Optional value for representing extra OAuth state information.
Data Type¶
string
Default Value¶
""
Remarks¶
Optional value for representing extra OAuth state information.
OAuthGrantType¶
The grant type for the OAuth flow.
Possible Values¶
CODE
, PASSWORD
, SAML_1
, CLIENT
Data Type¶
string
Default Value¶
CLIENT
Remarks¶
The following options are available: CODE,PASSWORD,SAML_1,CLIENT
OAuthVerifier¶
The verifier code returned from the OAuth authorization URL.
Data Type¶
string
Default Value¶
""
Remarks¶
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
Authentication on Headless Machines¶
See to obtain the OAuthVerifier
value.
Set OAuthSettingsLocation along with OAuthVerifier
. When you connect, the connector exchanges the OAuthVerifier
for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.
Once the OAuth settings file has been generated, you can remove OAuthVerifier
from the connection properties and connect with OAuthSettingsLocation set.
To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.
OAuthRefreshToken¶
The OAuth refresh token for the corresponding OAuth access token.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthRefreshToken
property is used to refresh the OAuthAccessToken when using OAuth authentication.
OAuthExpiresIn¶
The lifetime in seconds of the OAuth AccessToken.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
OAuthTokenTimestamp¶
The Unix epoch timestamp in milliseconds when the current Access Token was created.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
JWT OAuth¶
This section provides a complete list of JWT OAuth properties you can configure.
Property | Description |
---|---|
OAuthJWTCert | The JWT Certificate store. |
OAuthJWTCertType | The type of key store containing the JWT Certificate. |
OAuthJWTIssuer | The issuer of the Java Web Token. |
OAuthJWTCert¶
The JWT Certificate store.
Data Type¶
string
Default Value¶
""
Remarks¶
The name of the certificate store for the client certificate.
The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert
. If the store is password protected, specify the password in OAuthJWTCertPassword.
OAuthJWTCert
is used in conjunction with the OAuthJWTCertSubject
field in order to specify client certificates. If OAuthJWTCert
has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
Property | Description |
---|---|
MY | A certificate store holding personal certificates with their associated private keys. |
CA | Certifying authority certificates. |
ROOT | Root certificates. |
SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).
OAuthJWTCertType¶
The type of key store containing the JWT Certificate.
Data Type¶
string
Default Value¶
USER
Remarks¶
This property can take one of the following values:
Property | Description |
---|---|
USER | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java. |
MACHINE | For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java. |
PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java. |
JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java. |
PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
PPKFILE | The certificate store is the name of a file that contains a PPK (PuTTY Private Key). |
XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
OAuthJWTIssuer¶
The issuer of the Java Web Token.
Data Type¶
string
Default Value¶
""
Remarks¶
The issuer of the Java Web Token. In most cases, this takes the value of the OAuth App ID (Client Id) connection property and does not need to be individually set.
Kerberos¶
This section provides a complete list of Kerberos properties you can configure.
Property | Description |
---|---|
KerberosKDC | The Kerberos Key Distribution Center (KDC) service used to authenticate the user. |
KerberosRealm | The Kerberos Realm used to authenticate the user. |
KerberosSPN | The service principal name (SPN) for the Kerberos Domain Controller. |
KerberosKeytabFile | The Keytab file containing your pairs of Kerberos principals and encrypted keys. |
KerberosServiceRealm | The Kerberos realm of the service. |
KerberosServiceKDC | The Kerberos KDC of the service. |
KerberosTicketCache | The full file path to an MIT Kerberos credential cache file. |
KerberosKDC¶
The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
Data Type¶
string
Default Value¶
""
Remarks¶
The Kerberos properties are used when using SPNEGO or Windows Authentication. The connector will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.
If Kerberos KDC is not specified, the connector will attempt to detect these properties automatically from the following locations:
KRB5 Config File (krb5.ini/krb5.conf)
: If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the KDC from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS:C:\ProgramData\MIT\Kerberos5\krb5.ini
(Windows) or/etc/krb5.conf
(Linux).Java System Properties
: Using the system propertiesjava.security.krb5.realm
andjava.security.krb5.kdc
.Domain Name and Host
: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the connector will infer them from the configured domain name and host.
Note
Windows authentication is supported in JRE 1.6 and above only.
The Kerberos properties are used when using SPNEGO or Windows Authentication. The connector will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.
If Kerberos KDC is not specified, the connector will attempt to detect these properties automatically from the following locations:
KRB5 Config File (krb5.ini/krb5.conf)
: If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the KDC from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS:C:\ProgramData\MIT\Kerberos5\krb5.ini
(Windows) or/etc/krb5.conf
(Linux).Java System Properties
: Using the system propertiesjava.security.krb5.realm
andjava.security.krb5.kdc
.Domain Name and Host
: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the connector will infer them from the configured domain name and host.
Note
Windows authentication is supported in JRE 1.6 and above only.
KerberosRealm¶
The Kerberos Realm used to authenticate the user.
Data Type¶
string
Default Value¶
""
Remarks¶
The Kerberos properties are used when using SPNEGO or Windows Authentication. The Kerberos Realm is used to authenticate the user with the Kerberos Key Distribution Service (KDC). The Kerberos Realm can be configured by an administrator to be any string, but conventionally it is based on the domain name.
If Kerberos Realm is not specified, the connector will attempt to detect these properties automatically from the following locations:
KRB5 Config File (krb5.ini/krb5.conf)
: If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the default realm from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS:C:\ProgramData\MIT\Kerberos5\krb5.ini
(Windows) or/etc/krb5.conf
(Linux)Java System Properties
: Using the system propertiesjava.security.krb5.realm
andjava.security.krb5.kdc
.Domain Name and Host
: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the connector will infer them from the user-configured domain name and host. This might work in some Windows environments.
Note
Kerberos-based authentication is supported in JRE 1.6 and above only.
KerberosSPN¶
The service principal name (SPN) for the Kerberos Domain Controller.
Data Type¶
string
Default Value¶
""
Remarks¶
If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN.
If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN.
KerberosKeytabFile¶
The Keytab file containing your pairs of Kerberos principals and encrypted keys.
Data Type¶
string
Default Value¶
""
Remarks¶
The Keytab file containing your pairs of Kerberos principals and encrypted keys.
KerberosServiceRealm¶
The Kerberos realm of the service.
Data Type¶
string
Default Value¶
""
Remarks¶
The KerberosServiceRealm
is the specify the service Kerberos realm when using cross-realm Kerberos authentication.
In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.
This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).
KerberosServiceKDC¶
The Kerberos KDC of the service.
Data Type¶
string
Default Value¶
""
Remarks¶
The KerberosServiceKDC
is used to specify the service Kerberos KDC when using cross-realm Kerberos authentication.
In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.
This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).
KerberosTicketCache¶
The full file path to an MIT Kerberos credential cache file.
Data Type¶
string
Default Value¶
""
Remarks¶
This property can be set if you wish to use a credential cache file that was created using the MIT Kerberos Ticket Manager or kinit command.
SSL¶
This section provides a complete list of SSL properties you can configure.
Property | Description |
---|---|
SSLClientCert | The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). |
SSLClientCertType | The type of key store containing the TLS/SSL client certificate. |
SSLClientCertPassword | The password for the TLS/SSL client certificate. |
SSLClientCertSubject | The subject of the TLS/SSL client certificate. |
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
SSLClientCert¶
The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).
Data Type¶
string
Default Value¶
""
Remarks¶
The name of the certificate store for the client certificate.
The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert
. If the store is password protected, specify the password in SSLClientCertPassword.
SSLClientCert
is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert
has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
Property | Description |
---|---|
MY | A certificate store holding personal certificates with their associated private keys. |
CA | Certifying authority certificates. |
ROOT | Root certificates. |
SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).
SSLClientCertType¶
The type of key store containing the TLS/SSL client certificate.
Possible Values¶
USER
, MACHINE
, PFXFILE
, PFXBLOB
, JKSFILE
, JKSBLOB
, PEMKEY_FILE
, PEMKEY_BLOB
, PUBLIC_KEY_FILE
, PUBLIC_KEY_BLOB
, SSHPUBLIC_KEY_FILE
, SSHPUBLIC_KEY_BLOB
, P7BFILE
, PPKFILE
, XMLFILE
, XMLBLOB
Data Type¶
string
Default Value¶
USER
Remarks¶
This property can take one of the following values:
Property | Description |
---|---|
USER - default | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note that this store type is not available in Java. |
MACHINE | For Windows, this specifies that the certificate store is a machine store. Note that this store type is not available in Java. |
PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note that this store type is only available in Java. |
JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in JKS format. Note that this store type is only available in Java. |
PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
PPKFILE | The certificate store is the name of a file that contains a PuTTY Private Key (PPK). |
XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
SSLClientCertPassword¶
The password for the TLS/SSL client certificate.
Data Type¶
string
Default Value¶
""
Remarks¶
If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.
SSLClientCertSubject¶
The subject of the TLS/SSL client certificate.
Data Type¶
string
Default Value¶
*
Remarks¶
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, E=support@company.com". The common fields and their meanings are shown below.
Field | Meaning |
---|---|
CN | Common Name. This is commonly a host name like www.server.com. |
O | Organization |
OU | Organizational Unit |
L | Locality |
S | State |
C | Country |
E | Email Address |
If a field value contains a comma, it must be quoted.
SSLServerCert¶
The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type¶
string
Default Value¶
""
Remarks¶
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
---|---|
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
Schema¶
This section provides a complete list of schema properties you can configure.
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Schema | The type of schema to use. |
Location¶
A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type¶
string
Default Value¶
%APPDATA%\SharePoint Data Provider\Schema
Remarks¶
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location
property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
Note
Given that this connector supports multiple schemas, the structure for Microsoft SharePoint custom schema files is as follows:
- Each schema is given a folder corresponding to that schema name.
- These schema folders are contained in a parent folder.
- The
parent folder
should be set as theLocation
, not an individual schema's folder.
If left unspecified, the default location is "%APPDATA%\SharePoint Data Provider\Schema" with %APPDATA%
being set to the user's configuration directory:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
BrowsableSchemas¶
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
Tables¶
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Views¶
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Schema¶
The type of schema to use.
Possible Values¶
SOAP
, REST
Data Type¶
string
Default Value¶
SOAP
Remarks¶
The schemas available are REST (to use SharePoint REST API) and SOAP (to use Sharepoint SOAP API).
Miscellaneous¶
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
---|---|
CalculatedDataType | The data type to be used for calculated fields. |
ContinueOnError | Indicates whether or not to continue updating items in a batch after an error. |
CreateIDColumns | Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists. |
DisableFilterLimit | Microsoft SharePoint natively supports listing up to 5000 records as they appear in your list filter. Enable this property to pull more than 5000 records from the list filter at the cost of additional performance overhead. |
FolderOption | An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll. |
IncludeLookupColumns | This option controls whether the driver returns the lookup columns defined on a table. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Microsoft SharePoint. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
ShowHiddenColumns | Boolean determining if hidden columns should be shown or not. If false, all hidden columns will be removed from the column listing. |
ShowPredefinedColumns | Boolean determining if predefined columns should be shown or not. If false, all columns derived from a base type will be removed from the column listing. |
ShowVersionViews | Indicate whether to display the view of list versions. Such as ListA_Versions. |
STSURL | The URL of the security token service (STS) when using single sign-on (SSO). |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UseDisplayNames | Boolean determining if the display names for the columns should be used instead of the API names in both SOAP and REST schemas. |
UseEntityTypeName | Boolean determining if the table name should be EntityTypeName instead of the title in the REST schema. |
UseNTLMV1 | Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default). |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
CalculatedDataType¶
The data type to be used for calculated fields.
Possible Values¶
Calculated
, Boolean
, Date
, DateTime
, Double
, Integer
, String
Data Type¶
string
Default Value¶
Calculated
Remarks¶
The data type to be used for calculated fields. By default (Calculated), the data type is determined by the type of calculated field in SharePoint. However, in some cases these calculated fields may return values that are not appropriate for the specified type. In these instances, you may wish to set the Calculated Data Type to the appropriate type.
ContinueOnError¶
Indicates whether or not to continue updating items in a batch after an error.
Data Type¶
bool
Default Value¶
true
Remarks¶
If this property is set to True (default), the connector will continue adding, updating, or deleting items when an error is encountered on one of the items. When set to False, the connector will stop adding, updating, or deleting items after an error is encountered (entries preceeding the problematic entry will still be added, updated, or deleted).
CreateIDColumns¶
Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists.
Data Type¶
bool
Default Value¶
true
Remarks¶
Indicates whether or not to create supplemental ID columns for SharePoint columns that use values from information stored in other Lists (like "Lookup" or "Person or Group" columns). The ID column that is created will contain the related entry's ID (in the context of its original List). If set to false, the ID columns will not be created, the ID will be ignored, and only the value of the referenced column will be returned.
DisableFilterLimit¶
Microsoft SharePoint natively supports listing up to 5000 records as they appear in your list filter. Enable this property to pull more than 5000 records from the list filter at the cost of additional performance overhead.
Data Type¶
bool
Default Value¶
false
Remarks¶
Regardless of whether this property is set to true or false, for list filters with 5000 or fewer records, server-side processing of list filter queries is supported by Microsoft SharePoint, leading to the fastest possible performance.
When this property is set to true and the list filter exceeds 5000 entries, the connector will process the query filters client-side, which has an extra cost to performance.
However, if this property is set to false and the list filter exceeds 5000 entries, the connector will query from the list itself (as opposed to the list filter). This causes the set of columns in the query result to reflect the columns in the list instead of the list filter.
FolderOption¶
An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll.
Possible Values¶
FilesOnly
, FilesAndFolders
, RecursiveAll
Data Type¶
string
Default Value¶
RecursiveAll
Remarks¶
An option to determine how to display folders in results. FilesOnly will display only files in specified lists or libraries. FilesAndFolders will display files and folders in the specified list. RecursiveAll will display files in the specified list and all subfolders.
IncludeLookupColumns¶
This option controls whether the driver returns the lookup columns defined on a table.
Data Type¶
bool
Default Value¶
true
Remarks¶
This option controls whether the driver returns the lookup columns defined on a table. The SharePoint server may reject the request if too many lookup columns are included in a single query.
MaxRows¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Data Type¶
int
Default Value¶
-1
Remarks¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other¶
These hidden properties are used only in specific use cases.
Data Type¶
string
Default Value¶
""
Remarks¶
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting¶
Property | Description |
---|---|
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
Pagesize¶
The maximum number of results to return per page from Microsoft SharePoint.
Data Type¶
int
Default Value¶
1000
Remarks¶
The Pagesize
property affects the maximum number of results to return per page from Microsoft SharePoint. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.
PseudoColumns¶
This property indicates whether or not to include pseudo columns as columns to the table.
Data Type¶
string
Default Value¶
""
Remarks¶
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
ShowHiddenColumns¶
Boolean determining if hidden columns should be shown or not. If false, all hidden columns will be removed from the column listing.
Data Type¶
bool
Default Value¶
false
Remarks¶
Boolean determining if hidden columns should be shown or not. If false, all hidden columns will be removed from the column listing.
ShowPredefinedColumns¶
Boolean determining if predefined columns should be shown or not. If false, all columns derived from a base type will be removed from the column listing.
Data Type¶
bool
Default Value¶
true
Remarks¶
Boolean determining if predefined columns should be shown or not. If false, all columns derived from a base type will be removed from the column listing. These columns are normally system columns such as CreatedBy and Author. But, predefined columns may also include common columns such as Title.
ShowVersionViews¶
Indicate whether to display the view of list versions. Such as ListA_Versions.
Data Type¶
bool
Default Value¶
false
Remarks¶
Indicate whether to display the view of list versions. Such as ListA_Versions.
STSURL¶
The URL of the security token service (STS) when using single sign-on (SSO).
Data Type¶
string
Default Value¶
""
Remarks¶
The URL of the security token service (STS) when using single sign-on (SSO). This rarely needs to be set explicitly.
Timeout¶
The value in seconds until the timeout error is thrown, canceling the operation.
Data Type¶
int
Default Value¶
60
Remarks¶
If Timeout
= 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout
expires and the operation is not yet complete, the connector throws an exception.
UseDisplayNames¶
Boolean determining if the display names for the columns should be used instead of the API names in both SOAP and REST schemas.
Data Type¶
bool
Default Value¶
true
Remarks¶
Boolean determining if the display names for the columns should be used instead of the API names. In the REST schema, this property works with all Lists. Note that enabling this might impact performance and change the column datatype.
UseEntityTypeName¶
Boolean determining if the table name should be EntityTypeName instead of the title in the REST schema.
Data Type¶
bool
Default Value¶
false
Remarks¶
Boolean determining if the table name should be EntityTypeName instead of the title in the REST schema.
UseNTLMV1¶
Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).
Data Type¶
bool
Default Value¶
false
Remarks¶
Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).
UserDefinedViews¶
A filepath pointing to the JSON configuration file containing your custom views.
Data Type¶
string
Default Value¶
""
Remarks¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM Calendar WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"
UseSimpleNames¶
Boolean determining if simple names should be used for tables and columns.
Data Type¶
bool
Default Value¶
false
Remarks¶
Microsoft SharePoint tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames
makes the connector easier to use with traditional database tools.
Setting UseSimpleNames
to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.