Executing the SF_Metadata Stored Procedure

Syntax and Examples
Using the LIST and RETRIEVE Operations
Requirements for the Input Table

Executing the SF_Metadata Stored Procedure

You can use the SF_Metadata stored procedure with an Input table to retrieve Salesforce metadata.

Syntax and Examples

EXEC SF_Metadata 'operation', 'linked-server', 'input-table'

In this syntax:

The operation parameter specifies the metadata operation to execute. This parameter accepts either LIST or RETRIEVE as values.
The linked-server parameter specifies the name of your linked server that connects to Salesforce.
The input-table parameter specifies the name of the input table that contains the metadata components to process.

Using the LIST and RETRIEVE Operations

The SF_Metadata stored procedure accepts the LIST or RETRIEVE operations as values:

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 CustomObject or Layout components, which you can then use to make a subsequent SF_Metadata call with RETRIEVE to return a subset of these components.

The following example uses the LIST operation:
```
EXEC SF_Metadata 'List', 'SALESFORCE', 'MD_Input'
```
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.

The following example uses the RETRIEVE operation:
```
EXEC SF_Metadata 'Retrieve', 'SALESFORCE', 'MD_Input'
```

In both examples, SALESFORCE is the name of the linked server, and MD_Input is the name of the input table.

Requirements for the Input Table

The SF_Metadata stored procedure requires a local SQL Server table called the Input table. The Input table must have the following syntax:

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 purpose of each column in the syntax is described in the table below:

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 (``), which are used to retrieve all custom metadata components of the type 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 when you call RETRIEVE, then new rows are created with a value of `TRUE` for this column. When you execute the SF_Metadata stored procedure with the RETRIEVE operation again, the rows that contain the value `TRUE` are deleted and repopulated.
CreatedByList	BIT NULL	Should be NULL. If you use an asterisk for the `Member` column when you call LIST, then new rows are created with a value of `TRUE` for this column. When you call SF_Metadata again with LIST, the rows that contain the value `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. The `ID` column specifies the component’s identifier as it is returned by the Salesforce server.