Chapter 4: SQL Reference

sp_msgadmin

Description

Configures and administers messaging-related information.

Syntax

sp_msgadmin 'default', 'login', provider_name, provider_login,
		provider_password

sp_msgadmin 'help'[, 'list' | 'register' | 'default' | 'remove']

sp_msgadmin "list" [, 'login' [, provider_name [, login_name]
	'provider' [, provider_name]

sp_msgadmin 'register' 
	[, 'provider', provider_name, provider_class,
		messaging_provider_URL 
	| 'login', provider_name, local_login, provider_login,
		provider_password [, role_name] 
	| 'subscription', subscription_name, endpoint[, selector 
		[, delivery_option [, durable_name, client_id]]]]

sp_msgadmin 'remove',
	[, 'provider', provider_name 
	| 'login', provider_name, local_login [, role] 
	| 'subscription', subscription_name

Parameters

client_id

is the identification used by the messaging provider to identify the subscription as durable. client_id is a character string value. If you specify either client_id or durable_name, you must also specify the other, and the subscription is a durable subscription. Otherwise, it is a nondurable subscription.

The client_id and durable_name combination identifies durable subscriptions with the message provider, and must be unique. No two subscriptions can have the same client_id and durable_name.

client_id uniqueness extends across the messaging provider. JMS allows a particular client_id to be connected only once at any given time. For instance, if one application already has a durable subscription using a specified client_id, the client_id specified by another application cannot be the same if the applications are to be connected at the same time.

A durable subscription exists even when the client is not connected. The messaging provider saves messages that arrive even while the client is not connected.

A nondurable subscription exists only while the client is connected. The messaging provider discards messages that arrive while the client is not connected.

default: specifies a default. In the case of sp_msgadmin 'list', lists the syntax to specify the default login for a specified message provider.

delivery_option

species whether a SQL session can consume messages that it publishes. The valid values are:

local – the SQL session can consume messages that it publishes.
nonlocal – the SQL session cannot consume messages that it publishes.
null – assumes the value is local.

durable_name: is a character string value. See the description of client_id.

endpoint: is the topic to which the subscription is addressed. See the description of endpoint in msgsend.

help: provides syntax information about this stored procedure or about particular parameters.

list: lists syntax information about message providers, logins, or subscriptions.

local_login: is an Adaptive Server login that maps to the local login.

login

lists information about a particular messaging provider login mapping or about all messaging provider logins. When used with:

register – registers a login mapping.
default – specifies a default login.
remove – removes the mapping previously created between an Adaptive Server login and a service provider login, defined by this call:
```
sp_msgadmin 'register', 'login', local_login,... 
```

login_name: is a login name.

messaging_provider_URL: is the URL of the messaging provider you are registering.

provider

specifies the message provider. When used with:

register – registers a message provider.
list – lists information about a particular messaging provider or about all message providers.
remove – removes a messaging provider previously defined by this call:
```
sp_msgadmin 'register', 'provider',
    provider_name
```

provider_class: is the class of the messaging provider you are adding. Valid value is “tibco_jms”.

provider_login: is the login name of the messaging provider that local_login maps to when connecting to the message provider. It is also the login the provider uses as the default login when sending or receiving messages from the messaging provider specified by provider_name when using sp_msgadmin 'default'.

provider_name: is an alias referring to the messaging provider you are adding, which can be as many as 30 characters in length. In the case of sp_msgadmin 'register', 'provider', provider_name is an alias for messaging_provider. In the case of sp_msgadmin 'register', 'login', provider_name is the name of a previously registered provider.

provider_password: is the messaging provider password of the provider_login.

register: provides stored procedure syntax to register a message provider, login, or subscription.

remove: lists the stored procedure syntax to remove a message provider, login, or subscription.

role_name: is a SQL role name. If you specify a role_name, the local_login is ignored, and the provider_login and provider_password apply to the role_name.

selector: is a message filter that allows a client to select messages of interest. See the description of filters in msgrecv.

subscription

lists information about a particular subscription or about all subscriptions. Specifies the message provider. When used with:

register – registers a subscription.
list – lists information about a particular subscription or about all subscriptions.

remove – removes a subscription previously created by:

sp_msgadmin 'register' 'subscription',
    subscription_name, ...

subscription_name: is a subscription name.

Examples

Example 1

Specifies the default login that applies to all unmapped Adaptive Server logins, when using a specified messaging provider for either sending or receiving:

sp_msgadmin 'default', 'login', 'my_wms_provider',
     'ems_user1', 'ems_user1_password'

You must first register the provider_name by calling sp_msgadmin 'register', 'provider'.

Example 2

Specifies the default login:

sp_msgadmin 'default', 'login', 'one_ems_provider', 
    'loginsa', 'abcdef123456'

Example 3

Describes the syntax for sp_msgadmin 'list':

sp_msgadmin 'help', 'list'

Example 4

Checks the default login:

sp_msgadmin 'list', 'login', 'my_ems_provider'

Example 5

Lists the details for the user with a login of “loginsa”:

sp_msgadmin 'list', 'login', 'my_ems_provider', 
    'loginsa'

Example 6

Lists the details for the “my_ems_provider” message provider:

sp_msgadmin 'list', 'provider', 'my_ems_provider'

Example 7

Lists the details for subscription “subscription_1”:

sp_msgadmin 'list', 'subscription', 
    'subscription_1'

Example 8

Registers the login “ase_login1”, using messaging provider login “ems_user1”, and messaging provider name “my_ems_provider”:

sp_msgadmin 'register', 'login', 'my_ems_provider', 
    'ase_login1', 'ems_user1', 'ems_user1_password'

Example 9

Registers a login using the messaging provider login “ems_user1”, and a specified password used for all unmapped Adaptive Server logins:

sp_msgadmin 'register', 'login', 'my_ems_provider', 
    null, 'ems_user1', 'ems_user1_password'

Example 10

Registers a login with the messaging provider login “ems_user1”, and a specified password used for all Adaptive Server logins that have sa_role permissions:

sp_msgadmin 'register', 'login', 'my_ems_provider',
    null, 'ems_user1', 'ems_user1_password', 
    'sa_role'

Example 11

Registers the “my_ems_provider” messaging provider, which has a class of “tibco_jms” and an IP of 10.23.233.32:4823 as its address:

sp_msgadmin 'register', 'provider', 
    'my_ems_provider', 'tibco_jms',
    'tcp://10.23.233.32:4823'

Example 12

Registers a durable subscription named “durable_sub1”, then sp_msgadmin 'list' displays information about the new subscription.

sp_msgadmin
    'register', 'subscription', 'durable_sub1',
    'my_ems_provider?topic=topic.sample',
     null, null, 'durable1', 'client1'
sp_msgadmin 'list', 'subscription', 'durable_sub1'

Example 13

Registers “subscription_1”, a nondurable subscription.

sp_msgadmin 'register', 'subscription', 
    'subscription_1',
    'my_ems_provider?topic=topic.sample'

You must first use sp_msgadmin register, provider to register “my_ems_provider”.

Example 14

Removes the default login:

sp_msgadmin 'remove', 'login', 'my_ems_provider'

Example 15

Removes the messaging provider “my_ems_provider”:

sp_msgadmin 'remove', 'provider', 'my_ems_provider'

Example 16

Removes the Adaptive Server login “ase_login1” associated with the messaging provider “my_ems_provider”:

sp_msgadmin 'remove', 'login', 'my_ems_provider', 
    'ase_login1'

Example 17

Removes the default login, indicated by a null login parameter:

sp_msgadmin 'remove', 'login', 'my_ems_provider', 
    null

Example 18

Removes all logins for role sa_role on “my_ems_provider”:

sp_msgadmin 'remove', 'login', 'my_ems_provider', 
    null, 'sa_role'

Example 19

Removes “subscription_1”:

sp_msgadmin 'remove', 'subscription', 
    'subscription_1'

Usage

You cannot use sp_msgadmin inside a transaction.

sp_msgadmin 'register'

When a login name is used to connect to the message provider, login names are resolved in the following order:
1. Explict login names and passwords, specified in the endpoint, if provided.
2. Explicit login mapping for the current Adaptive Server login.
3. The default login name and password for the message provider, and the role corresponding to the Adaptive Server login.
4. The default login name and password for the message provider, with no specific role association.
5. Null login name and password if none of the above apply.
You can modify the login mapping between the Adaptive Server login and the messaging provider login only by removing and reregistering it with a different set of mappings.
See sp_msgadmin for usage common to the variants of sp_msgadmin.

sp_msgadmin 'remove'

Removing a messaging provider does not affect messages that are in transit (that is, messages that are in the process of being sent or received) to this message provider.
sp_msgadmin 'remove' does not affect any current connections to the message provider. This means that if a message provider, login, or default is removed while there is a current connection to the specified message provider, the connection is not affected. However, Sybase does not recommend this practice.
You must specify local_login as null if you specify role_name.

Permissions

You must have messaging_role to run the msgsend and msgrecv functions.

You must have messaging_role and sso_role permissions to issue:

sp_msgadmin 'default'
sp_msgadmim 'register'
sp_msgadmin 'remove'

Any user can issue:

sp_msgadmim 'help'
sp_msgadmin 'list'

View this book as PDF