SF_Metadata

Usage

You can run the SF_Metadata stored procedure, using an Input table, to access Salesforce metadata. The Input table must be created in the remote database of the remote SQL Server Instance.

Syntax

To list metadata types:

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

To retrieve XML representations of components:

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

where SALESFORCE is your linked server name and MD_Input is Input table name. The use_remote parameter must be set to 1.

Using the LIST and RETRIEVE operations

SF_Metadata accepts LIST or RETRIEVE:

• 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 could use this operation to return a list of names of all the CustomObject or Layout components in your organization, and use that information to make a subsequent SF_Metadata call with RETRIEVE returning a subset of these components.

Requirements for the Input Table

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

CREATE TABLE MD_Settings (

[Name] nvarchar NULL,

[Member] nvarchar NULL,

[MetadataXML] [XML] NULL,

[CreatedByWildcard] [bit] NULL,

[CreatedByList] [bit] NULL,

[Error] nvarchar NULL,

[ID] nchar NULL

)

The purpose of each column is described in the table below:

Name	Type	Description
Name	Nvarchar(255) NULL	The type of metadata component 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 ( * ) to retrieve all custom metadata components of the type specified in the 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 used an asterisk for the Member column of a RETRIEVE call, then new rows are created with a value of TRUE for this column. When SF_Metadata with RETRIEVE is executed again, the rows containing TRUE are deleted and repopulated.
CreatedByList	bit NULL	Should be NULL. If you used an asterisk for the Member column when you called LIST, then new rows are created with a value of TRUE for this column. When you call SF_Metadata again with LIST, the rows containing 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 returned from the Salesforce server.
ID	nchar(18) NULL	Should be NULL. Specifies the component’s ID as returned by the Salesforce server.