Search

SF_Metadata

SF_Metadata

Usage

You can use the SF_Metadata stored procedure with an Input table to access Salesforce metadata. The Input table must be created in the remote database on the remote SQL Server instance.

Syntax

EXEC SF_Metadata 'operation', 'linked-server', 'input-table', 'opts', 'use-remote'

In this syntax:

  • The operation parameter specifies operations that are available to SF_Metadata.

  • The linked-server parameter is the name of your linked server.

  • The opts parameter provides additional control over remote execution. Use '' to follow the remote settings that are configured in the DBAmp Configuration Program, or use skipremote to bypass remote execution entirely. If you specify skipremote, the use-remote parameter is ignored.

  • The use-remote parameter must be set to 1 to allow the stored procedure to run on a remote SQL Server.

Examples

The following example lists metadata types:

EXEC SF_Metadata 'List', 'SALESFORCE', 'MD_Input', '', 1

The next example retrieves XML representations of components:

EXEC SF_Metadata 'Retrieve', 'SALESFORCE', 'MD_Input', '', 1

This last example retrieves metadata by using the SALESFORCE linked server, an empty string ('') for opts, and the remote mode enabled (1):

EXEC SF_Metadata 'Retrieve', 'SALESFORCE', 'MD_Input', '', 1

Note: In all three examples, SALESFORCE is the name of the linked server name and MD_Input is the name of your input table name.

Using the LIST and RETRIEVE operations

The SF_Metadata stored procedure accepts the LIST and RETRIEVE operations:

  • Use the RETRIEVE operation to retrieve XML representations of components in an organization. The Input table contains rows that specify the components you want to retrieve.

  • Use the LIST operation when you want a high-level view of particular metadata types in your organization. For example, you can use this operation to return a list of names of all the CustomObject or Layout components in your organization and then use that information to make a subsequent SF_Metadata call with RETRIEVE to return a subset of these components.

Requirements for the Input Table

The SF_Metadata stored procedure requires an SQL Server table called the Input table. The Input table must be created in the remote database. This table must have the following structure:

 CREATE TABLE MD_Settings (

 [Name] [nvarchar](255) NULL,

 [Member] [nvarchar](255) NULL,

 [MetadataXML] [XML] NULL,

 [CreatedByWildcard] [bit] NULL,

 [CreatedByList] [bit] NULL,

 [Error] [nvarchar](255) NULL,

 [ID] [nchar](18) NULL

 )

The following table describes the purpose of each column:

Name Type Description
Name Nvarchar(255) NULL The type of metadata component that is to be retrieved. For example, a value of CustomObject retrieves one or more custom objects as specified in the member column.
Member Nvarchar(255) NULL One or more named components, or the wildcard character (*), that is used to retrieve all custom metadata components of the type that is specified in the name element. To retrieve a standard object, specify it by name. For example, a value of Account retrieves the standard Account object.
MetadataXML XML NULL Returns an XML description of the specified component as a result of a RETRIEVE call. For more information, see the documentation for the specified component’s XML contents in the Salesforce Metadata API Developer Guide.
CreatedByWildcard bit NULL Should be NULL. If you use an asterisk for the Member column of a RETRIEVE call, then new rows are created with a value of TRUE for this column. When you execute SF_Metadata with the RETRIEVE call again, the rows that contain a value of TRUE are deleted and repopulated.
CreatedByList bit NULL Should be NULL. If you use an asterisk for the Member column of a LIST call, then new rows are created with a value of TRUE for this column. When you call SF_Metadata with the LIST call again, the rows that contain a value of TRUE are deleted and repopulated.
Error Nvarchar(255) NULL Should be NULL. The Error column is an output column and is populated with any error messages that are returned from the Salesforce server.
ID nchar(18) NULL Should be NULL. Specifies the component’s identifier as returned by the Salesforce server.