📋

Description

Error 21396 indicates that an invalid value was provided for the subscriber type when configuring SQL Server replication. This error arises during the execution of stored procedures `sp_addsubscriber` or `sp_addsubscription` if the `@type` or `@subscriber_type` parameters, respectively, contain an unrecognized value.

💬

Error Message

The value specified for the @type parameter of sp_addsubscriber or the @subscriber_type parameter of sp_addsubscription is not valid. See SQL Server Books Online for a list of valid values.

🔍

Known Causes

4 known causes

⚠️

Typographical Error

The subscriber type was misspelled when entering the value into the stored procedure. Even a minor typo can trigger this error.

⚠️

Incorrect Numeric Value

The subscriber type is represented by a numeric value; entering an incorrect number that does not correspond to a valid type will result in this error.

⚠️

Deprecated Type Value

The specified subscriber type may be outdated or no longer supported in the current version of SQL Server. Refer to the documentation for current valid values.

⚠️

Case Sensitivity

While less common, some configurations might be case-sensitive for subscriber type values. Ensure the casing matches the expected value.

🛠️

Solutions

4 solutions available

1. Verify Subscriber Type Parameter in sp_addsubscriber easy

Correctly specify the @type parameter when adding a subscriber.

1

Consult SQL Server Books Online or the official Microsoft documentation for the valid values of the `@type` parameter for the `sp_addsubscriber` stored procedure. Common valid values include 'SQL Server', 'Oracle', 'Access', 'OLEDB Data Source', and 'ODBC'.

EXEC sp_addsubscriber @publication = N'YourPublicationName', @subscriber = N'YourSubscriberName', @type = N'SQL Server'; -- Example for a SQL Server subscriber
-- Or for an Oracle subscriber:
-- EXEC sp_addsubscriber @publication = N'YourPublicationName', @subscriber = N'YourSubscriberName', @type = N'Oracle';

2

Ensure that the value provided for the `@type` parameter in your `sp_addsubscriber` call exactly matches one of the documented valid types, paying close attention to capitalization and spelling.

EXEC sp_addsubscriber @publication = N'MyPub', @subscriber = N'MySub', @type = N'SQL Server';

2. Validate Subscriber Type Parameter in sp_addsubscription easy

Ensure the correct @subscriber_type is used when adding a subscription.

1

Refer to SQL Server Books Online for the valid values of the `@subscriber_type` parameter for the `sp_addsubscription` stored procedure. Similar to `sp_addsubscriber`, valid values typically include 'SQL Server', 'Oracle', 'Access', 'OLEDB Data Source', and 'ODBC'.

EXEC sp_addsubscription @publication = N'YourPublicationName', @article = N'YourArticleName', @subscriber = N'YourSubscriberName', @subscriber_type = N'SQL Server'; -- Example for a SQL Server subscriber
-- Or for an Oracle subscriber:
-- EXEC sp_addsubscription @publication = N'YourPublicationName', @article = N'YourArticleName', @subscriber = N'YourSubscriberName', @subscriber_type = N'Oracle';

2

Double-check the `@subscriber_type` parameter in your `sp_addsubscription` command to confirm it's a valid, recognized type for replication subscribers.

EXEC sp_addsubscription @publication = N'MyPub', @article = N'MyArticle', @subscriber = N'MySub', @subscriber_type = N'SQL Server';

3. Check for Typos and Case Sensitivity easy

Correct any spelling errors or case mismatches in the subscriber type parameter.

1

Carefully review the Transact-SQL code where you are calling `sp_addsubscriber` or `sp_addsubscription`. Look for any typographical errors in the string literal provided for the `@type` or `@subscriber_type` parameter.

EXEC sp_addsubscriber @publication = N'MyPub', @subscriber = N'MySub', @type = N'Sql Server'; -- Incorrect: 'Sql Server' instead of 'SQL Server'
-- Corrected:
EXEC sp_addsubscriber @publication = N'MyPub', @subscriber = N'MySub', @type = N'SQL Server';

2

Be mindful of case sensitivity. While SQL Server is generally case-insensitive for identifiers, string literals within stored procedure parameters can be sensitive. Ensure the casing of the provided subscriber type matches the expected format.

EXEC sp_addsubscription @publication = N'MyPub', @article = N'MyArticle', @subscriber = N'MySub', @subscriber_type = N'oracle'; -- Potentially incorrect if 'Oracle' is expected
-- Corrected:
EXEC sp_addsubscription @publication = N'MyPub', @article = N'MyArticle', @subscriber = N'MySub', @subscriber_type = N'Oracle';

4. Ensure Replication Components are Installed and Configured medium

Verify that the necessary replication components are present and that the subscriber is correctly registered.

1

If you are trying to add a subscriber of a type other than 'SQL Server' (e.g., Oracle, Access), ensure that the appropriate connectivity components (like Oracle client software, ODBC drivers) are installed and properly configured on the server where you are running the replication stored procedures.

text: Ensure Oracle client is installed and TNSNames.ora is configured if replicating to/from Oracle.

2

If the subscriber is a SQL Server instance, confirm that it has been properly registered as a subscriber in the distribution database using `sp_adddistributor` or `sp_addsubscriber` on the Publisher. The subscriber name specified in your stored procedure call must match the registered subscriber.

text: Verify the subscriber name exists in the 'sys.subscribers' catalog view on the Publisher.

3

Check the replication configuration in SQL Server Management Studio (SSMS). Navigate to Replication -> Local Publications. Right-click on the publication and select 'Properties'. Under the 'Subscribers' page, verify that the subscriber is listed and that its type is correctly identified. If not, you may need to re-add the subscriber.

text: Use SSMS to inspect replication topology and subscriber definitions.

Invalid Replication Subscriber Type