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 is used to write data by a BC pipeline to Microsoft SQL Server, to be processed into the database. The writer uses either an automatically generated SQL script or a user-supplied stored procedure to process the data. The endpoint is configured with the external document definition, a SQL Server connection string and optionally with 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, alter database properties etc. If advanced features or complex queries are required, they can be defined in SQL Server directly and utilized from the SQL Writer endpoint using the custom stored procedure path. In the following sections the variants of SQL Writer endpoint are described.

Writer with automatic processing

The SQL writer with automatic processing is the easiest setup. The external document definition, which is an annotated XML Schema, contains a number of tables, fields and relations in a hierarchical structure. The XML processor uses this definition to perform the Create/Update operations on the database tables, honoring the referential dependencies in the database. This means that for a given table, first its associations are processed, then the table itself is processed and then its compositions are processed. The automatic processing function can be configured to use either SQL MERGE function or a combination of SELECT, INSERT or UPDATE. The automatic processing function can also be configured to use a data table for processing, thus utilizing Microsoft ADO technology for creation and update.

Writer with user supplied XML stored procedure

In this situation the endpoint is configured with a SQL Server stored procedure that accepts an XML document. The XML document conforms to the external document definition. The stored procedure needs to 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.

This option can be used 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 a high latency, for example if the database is located in another physical location.

Writer with user supplied table-based stored procedure

In this situation the endpoint is configured with a SQL Server stored procedure that accepts a table variable. The table variable must be defined manually, and must match the structure of the external document definition. The stored procedure accesses this table variable with normal T-SQL functions and can perform the required inserts and updates on the database. See the How-To section for examples.

This option can be used in situations where the external document definition is single-level, as a SQL table is essentially single-level. This option 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 are exported to SQL Server tables, for example the export of a large number of items.

You can connect to SQL Server using a connection string. There are various options for defining connection strings, depending on naming, access permissions and other properties. See the Connection Strings web site for many examples.