Getting Started
Architecture
NServiceBus
Persistence
ServiceInsight
ServicePulse
ServiceControl
Monitoring
Samples

Addressing

NuGet Package: NServiceBus.Transport.SqlServer (8.x)
Target Version: NServiceBus 9.x

Format

The SQL Server Transport address has the following canonical form:

table@[schema]@[catalog]

The meaning of the parts is the following:

  • table is an unquoted delimited identifier without the surrounding square brackets. Whitespace and special characters are allowed and are not escaped, e.g. my table and my]table are legal values. The identifier is quoted automatically by SQL Server transport when executing the SQL statements. @ is a separator between the table and schema parts and thus is not a valid character.
  • schema is either an unquoted delimited identifier without the surrounding square brackets or a standard bracket-delimited identifier. In the second form, it is always surrounded by brackets, and any right brackets (]) inside are escaped, e.g. [my]]schema]. @ is only allowed in the bracket-delimited form; otherwise, it is treated as a separator.
  • catalog has the same syntax as schema.

Resolution

The address is resolved into a fully-qualified table name that includes the table name, its schema, and catalog. In the address, the table name is the only mandatory part. An address containing only a table name is a valid address, e.g. MyTable.

Schema

The SQL Server transport needs to know what schema to use for a queue table when sending messages. The following API can be used to specify the schema for an endpoint when routing is used to find a destination queue table for a message:

var routing = endpointConfiguration.UseTransport(new SqlServerTransport("connectionString"));

routing.RouteToEndpoint(typeof(MyMessage), "MyEndpoint");
routing.UseSchemaForEndpoint(endpointName: "MyEndpoint", schema: "my_schema");

There are several cases when routing is not used and the transport needs specific configuration to find out the schema for a queue table:

Use the following API to configure the schema for a queue:

var transport = new SqlServerTransport("connectionString");

transport.SchemaAndCatalog.UseSchemaForQueue(queueName: "myqueue", schema: "my_schema");
transport.SchemaAndCatalog.UseSchemaForQueue(queueName: "myerror", schema: "sc");

The configuration above is applicable when sending to a queue or when a queue is passed in the configuration:

await messageSession.Send("myqueue", new MyMessage());
endpointConfiguration.SendFailedMessagesTo("myerror");

The entire algorithm for calculating the schema is the following:

  • If the schema is configured for a given queue via UseSchemaForQueue, the configured value is used.
  • If logical routing is used and schema is configured for a given endpoint via UseSchemaForEndpoint, the configured schema is used.
  • If destination address contains a schema, the schema from address is used.
  • If default schema is configured via DefaultSchema, the configured value is used.
  • Otherwise, dbo is used as a default schema.

Catalog

The SQL Server transport reads the default catalog from the Initial catalog or Database properties of the connection string. The following API can be used to override the default catalog for an endpoint when routing is used to find a destination queue table for a message:

var routing = endpointConfiguration.UseTransport(new SqlServerTransport("connectionString"));

routing.RouteToEndpoint(typeof(MyMessage), "MyEndpoint");
routing.UseCatalogForEndpoint(endpointName: "MyEndpoint", catalog: "MyCatalog");

There are several cases when routing is not used and the transport needs specific configuration to find out the catalog for a queue table:

Use the following API to configure the schema for a queue:

var transport = new SqlServerTransport("connectionString");

transport.SchemaAndCatalog.UseCatalogForQueue(queueName: "myqueue", catalog: "MyCatalog");
transport.SchemaAndCatalog.UseCatalogForQueue(queueName: "myerror", catalog: "ServiceControl");

The configuration above is applicable when sending to a queue or when a queue is passed in the configuration:

await messageSession.Send("myqueue", new MyMessage());
endpointConfiguration.SendFailedMessagesTo("myerror");

The entire algorithm for calculating the catalog is the following:

  • If the catalog is configured for a given queue via UseCatalogForQueue, the configured value is used.
  • If logical routing is used and the catalog is configured for a given endpoint via UseCatalogForEndpoint, the configured value is used.
  • If the destination address contains a catalog, the catalog from the address is used.
  • Otherwise the catalog configured as Initial catalog or Database in the connection string is used.