SQL Server

Write data to a Microsoft SQL Server table using a staging temp table and a MERGE statement for upsert. Use Zeotap to materialize model results, enriched profiles, or audience membership into SQL Server.

Prerequisites

• A reachable Microsoft SQL Server instance (SQL Server 2016 or later recommended; Azure SQL Database is also supported)
• A database user with SELECT, INSERT, UPDATE, DELETE, and CREATE TABLE / ALTER TABLE privileges on the target schema
• Network connectivity between Zeotap and the SQL Server endpoint (port 1433 by default)

Authentication

SQL Server uses Username & Password authentication (SQL Server logins).

1. Create or identify a SQL Server login with the required privileges on the target database
2. Enter the Username and Password in Zeotap

Configuration

Target Settings

Supported Operations

Sync Modes: Upsert, Insert, Update, Mirror

Audience Sync Modes: Add, Remove, Mirror, Upsert

Features

• Field Mapping: Yes
• Schema Introspection: Yes — Zeotap reads column metadata from INFORMATION_SCHEMA.COLUMNS and skips computed columns

How It Works

Zeotap uses a staging-table pattern on a pinned session to execute sync operations atomically:

1. A local temporary table (#_ss_staging_…) is created on a dedicated connection for the batch
2. Transformed rows are inserted into the staging table in sub-batches of up to ~2000 parameters (SQL Server caps a single request at 2100 parameters)
3. Any new columns present in the batch that do not yet exist on the target are added via ALTER TABLE … ADD … NVARCHAR(MAX)
4. The sync mode operation runs on the pinned connection:
   • Upsert / Add: MERGE INTO target USING staging ON pk … WHEN MATCHED THEN UPDATE … WHEN NOT MATCHED THEN INSERT …
   • Insert: INSERT INTO target SELECT … FROM staging WHERE _diff_op = 'added'
   • Update: UPDATE target FROM target INNER JOIN staging ON pk WHERE _diff_op = 'changed'
   • Remove: DELETE target FROM target INNER JOIN staging ON pk WHERE _diff_op = 'removed'
   • Mirror: Upsert followed by Remove in the same connection
5. The staging temp table is dropped before the connection is returned to the pool

All statements in a sync batch run on the same pinned connection so the session-scoped #temp staging table remains visible across the whole operation.

Batch Size and Rate Limits

• Sub-batch size is chosen automatically from the column count so that each INSERT stays under SQL Server’s 2100-parameter request limit
• SQL Server itself does not enforce request-level rate limits; throughput is bounded by target instance resources

Best Practices

• Pre-create the target table with a clustered primary key or unique constraint on the primary key column for the best MERGE performance
• Grant only the minimum required privileges to the sync login; no db_owner or sysadmin is needed
• Prefer Strict encryption for Azure SQL Database and public-network deployments
• For very high row-count syncs, consider materializing to a dedicated staging table in the warehouse upstream and using Zeotap to project the diff

Troubleshooting

Login failed for user

Verify the username and password. Check that the login is a member of a role with access to the target database and that SQL Server authentication (not Windows-only) is enabled on the instance.

Cannot connect to host

Confirm the hostname, port, and that the SQL Server instance allows remote TCP connections. For Azure SQL Database, ensure the client IP is allowed in the server firewall rules.

Encryption required by server

Set Encryption to Enabled or Strict. Azure SQL Database requires encrypted connections.

Primary key is required for sync mode

Zeotap requires a primary key for upsert, update, mirror, and remove sync modes. Set the primary key on the sync or the source model so it can be propagated to the destination writer.

Invalid object name ‘[schema].[table]’

Zeotap creates the target table automatically on the first sync if it does not already exist. Ensure the sync login has CREATE TABLE privilege on the target schema, or pre-create the table with a primary key column typed as NVARCHAR(450) (or wider).

Cannot find temporary table ‘#ss_staging…’

This indicates a connection-pooling regression (the staging temp table is session-scoped). Report the log excerpt to your Zeotap operator; Zeotap is designed to pin a single connection per batch to avoid this.

The incoming request has too many parameters

Zeotap automatically chunks inserts to stay under SQL Server’s 2100-parameter limit. If you see this error, check for a recent change to the column count of the synced model.