# Microsoft SQL: prepare datasets for Optiply

Prepare Microsoft SQL tables or views that return the fields Optiply needs, then agree their connection configuration with support.

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](/article/integrations-data-mappings-public-ms-sql) 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](https://learn.microsoft.com/en-us/sql/relational-databases/views/create-views) 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.

| Dataset | What to prepare |
| --- | --- |
| Products | Stable product `remoteId`, name, stock and applicable product settings. |
| Suppliers | Stable supplier `remoteId`, name and applicable contact settings. |
| Supplier products | Supplier-specific fields and matching source product and supplier identifiers. |
| Sell orders and sell order lines | Order headers plus lines linked to their source order and product. |
| Buy orders and buy order lines | Purchase headers plus lines linked to their source order, product and supplier. |
| Receipt lines | Receipt identity, quantity, occurrence date and the source purchase-line identifier. |
| Product compositions | Parent and component source product identifiers with the component quantity. |
| Promotions and promotion products | Promotion 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](/article/connect-microsoft-sql).

## 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.

## Related articles on Microsoft SQL connection and mapping

- [Connect Microsoft SQL with support](/article/connect-microsoft-sql)
- [Microsoft SQL field mapping](/article/integrations-data-mappings-public-ms-sql)
