The SQL Server address has following canonical form
tableis an unquoted delimited identifier without the surrounding square brackets. Whitespace and special characters are allowed and are not escaped e.g.
my]tableare 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.
schemais 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.
@is only allowed in the bracket-delimited form, otherwise it is treated as separator.
The address is resolved into a qualified table name that includes both table name and its schema. In the address the table name is the only mandatory part. An address containing only a table name is a valid address e.g.
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 transport = endpointConfiguration.UseTransport<SqlServerTransport>(); var routing = transport.Routing(); routing.RouteToEndpoint(typeof(MyMessage), "MyEndpoint"); transport.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 specific queue table:
- Error queue
- Audit queue
- ServiceControl queue
- Overriding the default routing mechanism
- Replies to endpoints using SQL Server transport Version 2 and below
Use the following API to configure the schema for a specific queue:
var transport = endpointConfiguration.UseTransport<SqlServerTransport>(); transport.UseSchemaForQueue(queueName: "myqueue", schema: "my_schema"); transport.UseSchemaForQueue(queueName: "myerror", schema: "sc");
The configuration above is applicable when sending to a specific queue or when a specific queue is passed in configuration:
messageSession.Send("myqueue", new MyMessage());
The entire algorithm for calculating the schema is the following:
- If 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 schema, the schema from address is used.
- If default schema is configured via
DefaultSchema, the configured value is used.
dbois used as a default schema.
Versions 1 and 2 of SQL Server transport only recognize the table name part of the address. If such an endpoint receives a two-part address e.g.
MyTable@[MySchema] (either as a reply-to address or as a subscriber address), it will ignore anything past the first occurrence of
@ when using that address as a destination.
The schema has to be configured for
MyTable at that legacy endpoint in order for it to be able to reply or publish to the correct queue.