Microsoft Dynamics BC documentation

About: SQLWRITER endpoint

In Business Integration Solutions, use the SQLWRITER endpoint to write XML messages to Microsoft SQL Server.

The SQL Writer endpoint writes data from a BC pipeline to Microsoft SQL Server for processing into the database. The writer uses either an automatically generated SQL script or a user-supplied stored procedure to process the data. Configure the endpoint with the external document definition, a SQL Server connection string, and optionally a user-supplied stored procedure and additional options to influence processing.

The SQL Writer endpoint is intentionally kept simple. It does not perform DDL operations on the database, such as loading assemblies, creating tables, or altering database properties. For advanced features or complex queries, define them in SQL Server directly and use them from the SQL Writer endpoint via the custom stored procedure path. The following sections describe the variants of the SQL Writer endpoint.

Writer with automatic processing

The SQL Writer with automatic processing is the easiest setup. The external document definition (an annotated XML schema) contains a number of tables, fields, and relations in a hierarchical structure. The XML processor uses this definition to perform Create/Update operations on the database tables, respecting the referential dependencies in the database. This means that for a given table, BIS first processes its associations, then the table itself, then its compositions. You can configure the automatic processing function to use either the SQL MERGE function or a combination of SELECT, INSERT, or UPDATE. You can also configure it to use a data table for processing, using Microsoft ADO technology for creation and update.

Writer with user-supplied XML stored procedure

In this setup, configure the endpoint with a SQL Server stored procedure that accepts an XML document. The XML document conforms to the external document definition. The stored procedure must open the XML document using the SQL Server OPENXML function (see Microsoft TechNet SQL OPENXML function for details). See the How-To section for examples.

Use this option in situations where the external document definition is multi-level. It requires T-SQL XML skills to prepare and process the XML document.

The intended uses of the SQL Writer endpoint with XML stored procedures are:

• Situations where custom processing is required in SQL Server.
• Situations where the connection to the database is slow or has high latency, for example, when the database is in another physical location.

Writer with user-supplied table-based stored procedure

In this setup, configure the endpoint with a SQL Server stored procedure that accepts a table variable. Define the table variable manually so it matches the structure of the external document definition. The stored procedure accesses this table variable with normal T-SQL functions and performs the required inserts and updates on the database. See the How-To section for examples.

Use this option in situations where the external document definition is single-level, as a SQL table is essentially single-level. This is the fastest way to get large amounts of relatively simple data into SQL Server.

The intended uses of the SQL Writer endpoint with stored procedures are:

• Situations where custom processing is required in SQL Server.
• Situations where large amounts of flat data export to SQL Server tables, for example, exporting a large number of items.

You can connect to SQL Server using a connection string. Various options exist for defining connection strings, depending on naming, access permissions, and other properties. See the Connection Strings website for examples.