Keep dataset preparation separate from linking the source. Preparing a view does not activate synchronization. For the separate connection, identify Host Name, Port, Database, Schema, User and Password with your administrator. Enter private values only through the approved connection route.

Before you prepare Microsoft SQL datasets

Ask your database administrator to identify the intended database and schema. Have the Microsoft SQL field mapping open for the entities you plan to import.

Agree the required history, enabled entities and source names with support. Preserve existing agreed query names. Do not rename a working table to match an import label without reviewing its configuration.

The account used to import data needs read access to the agreed objects. Creating a view requires CREATE VIEW permission in the database and ALTER permission on its schema. Have an authorized person prepare it. The connector itself does not need schema-change permissions.

Steps to prepare Microsoft SQL datasets

  1. Open SQL Server Management Studio and connect to the intended server.
  2. In Object Explorer, expand the intended database.
  3. Review the existing source tables and their identifiers with your administrator.
  4. For a new approved view, right-click Views, then choose New View.
  5. In Add Table, select the required source objects.
  6. Choose Add, then Close.
  7. Select the columns for the entity and apply its agreed aliases.
  8. Review joins and filters with your administrator before saving.
  9. Choose File → Save, enter the agreed view name, then choose OK.
  10. Have your administrator check the result using the intended read-only connection identity.
  11. Compare the returned columns with the matching entity table in the mapping guide.
  12. Give support the non-secret database, schema and object definitions for connection review.

Microsoft's Create views guide explains these SQL Server Management Studio controls. Only a person authorized to change the source objects should create or change them.

Fields in the Microsoft SQL datasets

The import receives separate inputs for these entities. Support binds your source objects to the inputs during setup.

DatasetWhat to prepare
ProductsStable product remoteId, name, stock and applicable product settings.
SuppliersStable supplier remoteId, name and applicable contact settings.
Supplier productsSupplier-specific fields and matching source product and supplier identifiers.
Sell orders and sell order linesOrder headers plus lines linked to their source order and product.
Buy orders and buy order linesPurchase headers plus lines linked to their source order, product and supplier.
Receipt linesReceipt identity, quantity, occurrence date and the source purchase-line identifier.
Product compositionsParent and component source product identifiers with the component quantity.
Promotions and promotion productsPromotion dates and settings, plus source promotion and product identifiers.

Use stable identifiers, not row numbers that change between reads. A relationship field must match its related record's source identifier, not an Optiply-generated ID.

updated_at records a source update time. For incremental extraction, agree the replication key column that advances when a record changes. deleted_at carries an explicit deletion date. Removing a row from a view is not the same as supplying a deletion.

What you see when Microsoft SQL dataset preparation worked

Your administrator can read the agreed objects with the intended connection identity. The selected columns match the entity definitions, and related identifiers match across datasets. Support can then configure the Microsoft SQL connection.

Limits of preparing Microsoft SQL datasets

Dataset names, extraction settings and connection access are separate. An inbound read-only dataset does not enable outbound purchase-order writes. Agree any destination tables and write permissions separately.

The import converts stock and quantity values to whole units, first rounding to two decimals and then truncating fractional values. Prepare valid numeric values rather than depending on automatic conversion. Keep UTC date values accurate before extraction because the import truncates several date strings to seconds and appends Z.

If Microsoft SQL dataset preparation did not work

If a view cannot be read, ask your administrator to check the selected database, schema and object permissions. Do not broaden access to every table to bypass the error.

If records are missing, compare stable identifiers, joins and filters first. Check that products and suppliers exist before their dependent links. Give support the entity, column names and a sanitized description of the mismatch. Do not change identifiers or delete imported records to force another import.