BCC SHOW_STATISTICS

Display the current distribution statistics for the specified target on the specified table.

Syntax
DBCC SHOW_STATISTICS ('table' | 'view' , target)
[WITH [NO_INFOMSGS] option [, option...] ]

Options:
STAT_HEADER
DENSITY_VECTOR
HISTOGRAM

Key:
table / view - The table or indexed view for which to display stats
target - The column, index or statistics for which to display stats.
NO_INFOMSGS - Suppress all information messages (severity 0-10)

Specifying any of the 3 options will return only those statistics.

Example

DBCC SHOW_STATISTICS ('MySchema.MyTable', MyIndex);
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC SHRINKDATABASE

Shrink the size of the database data and log files.

Syntax
DBCC SHRINKDATABASE ('database' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE ('database_id' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE (0 option [,option]) [WITH NO_INFOMSGS]

Options:
target_percent
NOTRUNCATE
TRUNCATEONLY

Key:
0 - Shrink the current database

target_percent - Percentage of free space to remain in the database file

NOTRUNCATE - Free space at the end of the data file is not returned to the OS
(pages are still moved)
TRUNCATEONLY - Release free space at the end of the data file to the OS
(do not move pages)
NO_INFOMSGS - Suppress all information messages (severity 0-10)

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC SHRINKFILE

Shrink the size of the current database data / log file or empty a file by moving the data.

Syntax
DBCC SHRINKFILE ( file , EMPTYFILE ) [WITH NO_INFOMSGS]

DBCC SHRINKFILE ( file , target_size
[, {NOTRUNCATE | TRUNCATEONLY }] ) [WITH NO_INFOMSGS ]

Key:
file - 'file_name' or file_id

EMPTYFILE - Migrate data to other files in the same filegroup.
The file can be removed with ALTER DATABASE.

target_size - The size for the file in megabytes.
default = that specified when the file was created, or
the last size used with ALTER DATABASE.(int)

NOTRUNCATE - Free space at the end of the data file is not returned to the OS
(pages are still moved)
TRUNCATEONLY - Release free space at the end of the data file to the OS
(do not move pages)
NO_INFOMSGS - Suppress all information messages (severity 0-10)

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC SQLPERF

Display transaction-log space statistics. Reset wait and latch statistics.

Syntax
DBCC SQLPERF ( Option ) [WITH NO_INFOMSGS ]

Options:
LOGSPACE
'sys.dm_os_latch_stats' , CLEAR
'sys.dm_os_wait_stats' , CLEAR

Key:
LOGSPACE - Monitor log space, indicates when to back up or truncate the tx log.
dm_os_latch_stats - Reset the latch statistics.
dm_os_wait_stats - Reset the wait statistics.
NO_INFOMSGS - Suppress all information messages (severity 0-10)

Example

DBCC SQLPERF(LOGSPACE)
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC TRACEON - Enable trace flags.
DBCC TRACEOFF - Disable trace flags.
DBCC TRACESTATUS - Display the status of trace flags.

Syntax
DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [WITH NO_INFOMSGS]

DBCC TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [WITH NO_INFOMSGS]

DBCC TRACESTATUS ( [ [trace# [,...n ] ] [,] [-1] ] ) [WITH NO_INFOMSGS]

Key:

trace# - Number of the trace flag(s)
-1 - Display the status of trace flags that are enabled globally.
NO_INFOMSGS - Suppress all information messages.

By default all trace flags that are enabled for the session are displayed.

Examples

-- Turn flag on
DBCC TRACEON (3205)
GO
-- Turn flag on globally
DBCC TRACEON (2528, -1)
GO
-- Turn flag off
DBCC TRACEOFF (3205);
GO
-- Show flag status
DBCC TRACESTATUS (2528, 3205)
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC UPDATEUSAGE

Report and correct page and row count inaccuracies in catalog views, use this after upgrading a database to SQL Server 2005.

Syntax
DBCC UPDATEUSAGE ( database
[, {table | view} [,{index} ] ] )
[WITH [ NO_INFOMSGS ] [ , ] [COUNT_ROWS ] ]

Key:
database - 'database_name' or database_id or 0 (current db)
NO_INFOMSGS - Suppress all information messages.
COUNT_ROWS - Update the row count column.

The table/view and indexes may be specified by 'name' (in single quotes) or ID.

If 0 is specified, the current database will be used.

Example

DBCC UPDATEUSAGE ('TestDatabase','UserSchema.teblename');

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC USEROPTIONS

Return the SET options currently active (set for the current connection.)

Syntax
DBCC USEROPTIONS [WITH NO_INFOMSGS]

Options:

NO_INFOMSGS - Suppress all information messages.

Example

DBCC USEROPTION

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC - Deprecated commands

DBCC CONCURRENCYVIOLATION
DBCC DBREINDEX - use ALTER INDEX instead
DBCC DBREPAIR - use DROP DATABASE instead
DBCC INDEXDEFRAG
DBCC PINTABLE
DBCC SHOWCONTIG
DBCC UNPINTABLE

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE DEFAULT

A default specifies the value to be inserted into a column whenever no value is supplied.

This command will be removed in a future version of SQL Server. Avoid using this command in new development work, and plan to modify any application that currently uses it.

Syntax
CREATE DEFAULT [schema.] default_name
AS constant_expression [;]

Key:
default_name Name of the default.
constant_expression An expression containing only constant values

You can create a DEFAULT definition as part of the table definition when you create a table. If a table already exists, you can add a DEFAULT definition to it. Each column in a table can contain one DEFAULT definition.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP DEFAULT

Remove user-defined default(s) from the current database.

Syntax
DROP DEFAULT { [schema_name.] default_name } [ ,...n ] [ ; ]

Key:
schema_name The schema to which the default belongs
default_name Name of an existing default

Defaults must comply with the rules for identifiers.

Example

DROP DEFAULT price_dflt;

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DELETE

Remove rows from a table.

Syntax
[WITH common_table_expression [ ,...n ] ]
DELETE
[TOP ( expression ) [ PERCENT ] ]
[FROM ]
server.database.schema.table/view
database.[schema].table/view
schema.table/view
rowset_function_limited
[WITH ( table_hint_limited [ ...n ] ) ]
[OUTPUT_Clause] [;]
FROM table_source [ ,...n ] ]
[WHERE { search_condition
[ CURRENT OF
[ GLOBAL ] cursor_name
cursor_variable_name
]
[OPTION ( Query Hint [ ,...n ] ) ] [;]

Key:

TOP # Number (or percent) of rows that will be deleted.
This will be a random subset of the rows

rowset_function_limited
Either the OPENROWSET or OPENQUERY function,
e.g. OPENQUERY(MyOracleSvr, 'SELECT name, id FROM scott.sales')

FROM An optional keyword

rowset_function_limited Either an OPENQUERY or OPENROWSET function

OUTPUT_Clause Return the deleted rows (or expressions)
Not supported when targeting a view or remote table.

FROM table_source An additional FROM clause - specify data from table_source
but delete the corresponding rows from the table in the first FROM clause.
This is an alternative to specifying a join, or a subquery in the WHERE clause.
(T-SQL extension)

WHERE Conditions to limit the number of rows to be deleted.
If ommitted, DELETE will remove all rows from the table.

CURRENT OF
Perform the DELETE at the current position of the cursor.

GLOBAL The cursor_name refers to a global cursor.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DENY Object permissions

Deny permissions to system objects, database objects,

Syntax
DENY SELECT ON [sys.]system_object FROM principal

DENY EXECUTE ON [sys.]system_object FROM principal

DENY object_permission [,...n ]
ON Object
TO principal [,...n ]
[CASCADE]
[AS principal ]

Key:
Objects
ASSEMBLY :: assembly
ASYMMETRIC KEY :: asymmetric_key
CERTIFICATE :: certificate
CONTRACT :: contract
ENDPOINT :: endpoint
FULLTEXT CATALOG :: full-text_catalog
LOGIN :: SQL_Server_login
MESSAGE TYPE :: message_type
[OBJECT ::][schema].object [(column [,...n ])]
REMOTE SERVICE BINDING :: remote_binding
ROUTE :: route
SCHEMA :: schema
SERVICE :: service
SYMMETRIC KEY :: symmetric_key
TYPE :: [schema].type
XML SCHEMA COLLECTION :: [schema.] XML_schema_collection

system_object Objects such as stored procedures, functions and views.

principal one of:
user /role /application role
user mapped to a Windows login/group/certificate
user mapped to an asymmetric key
user not mapped to a server principal.

cascade Also DENY from other principals that have been
granted or denied by this principal.

object_permission
ALTER/CONNECT/CONTROL/RECEIVE/REFERENCES/IMPERSONATE
SELECT/UPDATE/INSERT/DELETE/
EXECUTE/TAKE OWNERSHIP/VIEW DEFINITION
ALL Against an object, DENY ALL will deny all permissions applicable to the object.
(Not every object_permission is applicable to every type of Object)

When denying rights from a SQL_Server_login the AS clause (if present) should specify the SQL Server login from which the principal executing this query derives its right to deny the permission.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DENY to user/role

Deny permissions to a database user, database role, or application role.

Syntax
DENY permission [ ,...n ]
TO grantee_principal [,...n ]
[CASCADE]
[AS grantor_principal]

DENY permission | ALL [ PRIVILEGES ] [,...n ]
[ON USER::user | ON ROLE::role | ON APPLICATION ROLE::role]
TO principal [ ,...n ] [CASCADE]
[AS principal ]

Key:
principal one of:
user /role /application role
user mapped to a Windows login/group/certificate
user mapped to an asymmetric key
user not mapped to a server principal.

grantee_principal/grantor_principal
SQL Server login or SQL Login mapped to a Windows login/group/certificate/asymmetric_key
The grantor_principal for an Endpoint must be a SQL Server login

cascade Also DENY from other principals that have been
granted or denied by this principal.

permission
ALL (= BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT,
CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW)
ALTER
ALTER ANY APPLICATION ROLE
ALTER ANY ASSEMBLY
CREATE AGGREGATE
CREATE ASSEMBLY
UNSAFE ASSEMBLY
EXTERNAL ACCESS ASSEMBLY
CREATE ASYMMETRIC KEY
ALTER ANY ASYMMETRIC KEY
AUTHENTICATE
AUTHENTICATE SERVER
BACKUP DATABASE
BACKUP LOG
ADMINISTER BULK OPERATIONS
ALTER ANY CONNECTION
ALTER ANY CREDENTIAL
CHECKPOINT
CONNECT
CONNECT REPLICATION
CONTROL
CREATE CERTIFICATE
ALTER ANY CERTIFICATE
CREATE CONTRACT
ALTER ANY CONTRACT
VIEW DATABASE STATE
CREATE DATABASE
CREATE ANY DATABASE
ALTER ANY DATABASE
VIEW ANY DATABASE
CREATE DDL EVENT NOTIFICATION
CREATE DATABASE DDL EVENT NOTIFICATION
CREATE TRACE EVENT NOTIFICATION
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATASPACE
CREATE DEFAULT
VIEW DEFINITION
VIEW ANY DEFINITION
DELETE
CREATE ENDPOINT
ALTER ANY ENDPOINT
ALTER ANY EVENT NOTIFICATION
EXECUTE
CREATE FULLTEXT CATALOG
ALTER ANY FULLTEXT CATALOG
CREATE FUNCTION
INSERT
ALTER ANY LINKED SERVER
ALTER ANY LOGIN
CREATE MESSAGE TYPE
ALTER ANY MESSAGE TYPE
TAKE OWNERSHIP
CREATE PROCEDURE
CREATE QUEUE
SUBSCRIBE QUERY NOTIFICATIONS
ALTER RESOURCES
REFERENCES
CREATE REMOTE SERVICE BINDING
ALTER ANY REMOTE SERVICE BINDING
CREATE ROLE
ALTER ANY ROLE
CREATE ROUTE
ALTER ANY ROUTE
CREATE RULE
CREATE SCHEMA
ALTER ANY SCHEMA
SELECT
ALTER SERVER STATE
VIEW SERVER STATE
ALTER SETTINGS
CREATE SERVICE
ALTER ANY SERVICE
SHOWPLAN
SHUTDOWN
CONNECT SQL
CREATE SYMMETRIC KEY
ALTER ANY SYMMETRIC KEY
CREATE SYNONYM
CREATE TABLE
ALTER TRACE
ALTER ANY DATABASE DDL TRIGGER
CREATE TYPE
ALTER ANY USER
UPDATE
CREATE VIEW
CREATE XML SCHEMA COLLECTION

When denying the cascade of permission rights from one principle to another principle (DENY permission ON user/role TO user/role) then the only valid permissions to deny are:

CONTROL, IMPERSONATE, TAKE OWNERSHIP, ALTER, VIEW DEFINITION


For user: CONTROL/IMPERSONATE/ALTER/VIEW DEFINITION
For role: CONTROL/TAKE OWNERSHIP/ALTER/VIEW DEFINITION
For app. role: CONTROL/ALTER/VIEW DEFINITION

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE ENDPOINT

Create an endpoint and set properties.

Syntax
CREATE ENDPOINT endPoint [AUTHORIZATION login]
[STATE = {STARTED | STOPPED | DISABLED} ]
AS {HTTP | TCP } (protocol_specific_arguments)
FOR {SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING} (language_specific_arguments)

AS HTTP_protocol_specific_arguments
AS HTTP (
PATH = 'url'
[[ , ] AUTHENTICATION = ( { BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } [ ,...n ] )
[[ , ] PORTS = ( { CLEAR | SSL} [ ,...n ] )
[[ , ] [SITE = {'*' | '+' | 'webSite' },]
[[ , ] CLEAR_PORT = clearPort ]
[[ , ] SSL_PORT = SSLPort ]
[[ , ] AUTH_REALM = { 'realm' | NONE } ]
[[ , ] DEFAULT_LOGON_DOMAIN = { 'domain' | NONE } ]
[[ , ] COMPRESSION = { ENABLED | DISABLED } ]
)

AS TCP_protocol_specific_arguments
AS TCP (
LISTENER_PORT = listenerPort
[[ , ] LISTENER_IP = ALL | ( 4-part-ip ) | ( "ip_address_v6" ) ]
)

FOR SOAP_language_specific_arguments
FOR SOAP(
[{ WEBMETHOD [ 'namespace' .] 'method_alias'
( NAME = 'database.schema.name'
[[ , ] SCHEMA = { NONE | STANDARD | DEFAULT } ]
[[ , ] FORMAT = { ALL_RESULTS | ROWSETS_ONLY | NONE } ]
)
}[ ,...n ] ]
[[ , ] BATCHES = { ENABLED | DISABLED } ]
[[ , ] WSDL = { NONE | DEFAULT | 'sp_name' } ]
[[ , ] SESSIONS = { ENABLED | DISABLED } ]
[[ , ] LOGIN_TYPE = { MIXED | WINDOWS } ]
[[ , ] SESSION_TIMEOUT = timeoutInterval | NEVER ]
[[ , ] DATABASE = { 'database' | DEFAULT }
[[ , ] NAMESPACE = { 'namespace' | DEFAULT } ]
[[ , ] SCHEMA = { NONE | STANDARD } ]
[[ , ] CHARACTER_SET = { SQL | XML } ]
[[ , ] HEADER_LIMIT = int ]
)


FOR SERVICE_BROKER_language_specific_arguments
FOR SERVICE_BROKER (
[AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate
| CERTIFICATE certificate WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
}]
[[ , ] ENCRYPTION = { DISABLED | { { SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }
]
[[ , ] MESSAGE_FORWARDING = { ENABLED | DISABLED* } ]
[[ , ] MESSAGE_FORWARD_SIZE = forward_size ]
)

FOR DATABASE_MIRRORING_language_specific_arguments
FOR DATABASE_MIRRORING (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate
| CERTIFICATE certificate WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
[ [ [ , ] ] ENCRYPTION = { DISABLED | { { SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }

]
[ , ] ROLE = { WITNESS | PARTNER | ALL }
)

The options above allow a choice of transport protocol - either TCP or HTTP & listening port number,
and the payload language - SOAP, Transact-SQL, service broker or database mirroring.

Key:

AUTHORIZATION - A valid SQL Server or Windows login that is assigned ownership of the newly created endpoint object.

STATE - The state of the endpoint when it is created, to change, use ALTER ENDPOINT

PATH = 'url' - For a URL like http://Server01/Folder02, The Path URL is /Folder02

SITE = webSite a specific host name for the computer.
SITE = + all possible host names for the computer.
SITE = * all possible host names for the computer that are not otherwise explicitly reserved.

COMPRESSION - If enabled, SQL Server will honor requests where gzip encoding is accepted.

BATCHES - Support for ad hoc SQL requests on the endpoint.

LOGIN_TYPE - SQL or WINDOWS authentication.

SESSIONS - If ENABLED, multiple SOAP request/response message pairs can be identified as part of a single SOAP session.

SESSION_TIMEOUT - Time in Seconds before a SOAP session expires.

NAMESPACE - The namespace for the endpoint. The default is http://tempuri.org

HEADER_LIMIT - Max size, in bytes, of the SOAP envelope header.

ENCRYPTION - Negotiate for an encryption algorithm, AES RC4 = give preference to AES while RC4 AES = give preference to RC4.

The default clear port number is 80, the default SSL port number is 443.
Listener port = any number between 1024 and 32767, by convention, 4022.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER ENDPOINT

Alter an endpoint - set properties.

Syntax
ALTER ENDPOINT endPoint [AUTHORIZATION login]
[STATE = {STARTED | STOPPED | DISABLED} ]
AS {HTTP | TCP } (protocol_specific_arguments)
FOR {SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING} (language_specific_arguments)

AS HTTP_protocol_specific_arguments
AS HTTP (
PATH = 'url'
[[ , ] AUTHENTICATION = ( { BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } [ ,...n ] )
[[ , ] PORTS = ( { CLEAR | SSL} [ ,...n ] )
[[ , ] [SITE = {'*' | '+' | 'webSite' },]
[[ , ] CLEAR_PORT = clearPort ]
[[ , ] SSL_PORT = SSLPort ]
[[ , ] AUTH_REALM = { 'realm' | NONE } ]
[[ , ] DEFAULT_LOGON_DOMAIN = { 'domain' | NONE } ]
[[ , ] COMPRESSION = { ENABLED | DISABLED } ]
)

AS TCP_protocol_specific_arguments
AS TCP (
LISTENER_PORT = listenerPort
[[ , ] LISTENER_IP = ALL | ( 4-part-ip ) | ( "ip_address_v6" ) ]
)

FOR SOAP_language_specific_arguments
FOR SOAP(
[{ WEBMETHOD [ 'namespace' .] 'method_alias'
( NAME = 'database.schema.name'
[[ , ] SCHEMA = { NONE | STANDARD | DEFAULT } ]
[[ , ] FORMAT = { ALL_RESULTS | ROWSETS_ONLY | NONE } ]
)
}[ ,...n ] ]

[ { ALTER WEBMETHOD [ 'namespace' .] 'method_alias'
( NAME = 'database.owner.name'
[ , SCHEMA = {NONE | STANDARD | DEFAULT} ]
[ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ]
)
} [ ,...n]
]
[[ , ] { DROP WEBMETHOD [ 'namespace' .] 'method_alias' } [ ,...n ] ]
[[ , ] BATCHES = { ENABLED | DISABLED } ]
[[ , ] WSDL = { NONE | DEFAULT | 'sp_name' } ]
[[ , ] SESSIONS = { ENABLED | DISABLED } ]
[[ , ] LOGIN_TYPE = { MIXED | WINDOWS } ]
[[ , ] SESSION_TIMEOUT = timeoutInterval | NEVER ]
[[ , ] DATABASE = { 'database' | DEFAULT }
[[ , ] NAMESPACE = { 'namespace' | DEFAULT } ]
[[ , ] SCHEMA = { NONE | STANDARD } ]
[[ , ] CHARACTER_SET = { SQL | XML } ]
[[ , ] HEADER_LIMIT = int ]
)


FOR SERVICE_BROKER_language_specific_arguments
FOR SERVICE_BROKER (
[AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate
| CERTIFICATE certificate WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
}]
[[ , ] ENCRYPTION = { DISABLED | { { SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }
]
[[ , ] MESSAGE_FORWARDING = { ENABLED | DISABLED* } ]
[[ , ] MESSAGE_FORWARD_SIZE = forward_size ]
)

FOR DATABASE_MIRRORING_language_specific_arguments
FOR DATABASE_MIRRORING (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate
| CERTIFICATE certificate WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
[ [ [ , ] ] ENCRYPTION = { DISABLED | { { SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }

]
[ , ] ROLE = { WITNESS | PARTNER | ALL }
)

The options above allow a choice of transport protocol - either TCP or HTTP & listening port number,
and the payload language - SOAP, Transact-SQL, service broker or database mirroring.

Key:

AUTHORIZATION - A valid SQL Server or Windows login that is assigned ownership of the newly created endpoint object.

STATE - The state of the endpoint when it is created, to change, use ALTER ENDPOINT

PATH = 'url' - For a URL like http://Server01/Folder02, The Path URL is /Folder02

SITE = webSite a specific host name for the computer.
SITE = + all possible host names for the computer.
SITE = * all possible host names for the computer that are not otherwise explicitly reserved.

COMPRESSION - If enabled, SQL Server will honor requests where gzip encoding is accepted.

BATCHES - Support for ad hoc SQL requests on the endpoint.

LOGIN_TYPE - SQL or WINDOWS authentication.

SESSIONS - If ENABLED, multiple SOAP request/response message pairs can be identified as part of a single SOAP session.

SESSION_TIMEOUT - Time in Seconds before a SOAP session expires.

NAMESPACE - The namespace for the endpoint. The default is http://tempuri.org

HEADER_LIMIT - Max size, in bytes, of the SOAP envelope header.

ENCRYPTION - Negotiate for an encryption algorithm, AES RC4 = give preference to AES while RC4 AES = give preference to RC4.

The default clear port number is 80, the default SSL port number is 443.
Listener port = any number between 1024 and 32767, by convention, 4022

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP ENDPOINT

Drop an existing endpoint.

Syntax
DROP ENDPOINT endPointName

Key:
endPointName name of the endpoint to be removed


Example

DROP ENDPOINT sales_endpt;

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE EVENT NOTIFICATION

Create an event notification trigger, that may in turn send event information to a service broker.

Syntax
CREATE EVENT NOTIFICATION event_notification
ON { SERVER | DATABASE | QUEUE queue }
[WITH FAN_IN ]
FOR {event_type | event_group } [ ,...n ]
TO SERVICE 'broker_service' ,
{'broker_instance_specifier' | 'current database' } [; ]


Key:
notification_name Name of the event notification to remove.
SERVER Apply to the current server
DATABASE Apply to the current database
queue_name The name of the queue

Options must must be specified as they originally were when the event notification was created.

Example

DROP EVENT NOTIFICATION testnotify

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP EVENT NOTIFICATION

Remove an event notification trigger from the current database.

Syntax
DROP EVENT NOTIFICATION notification_name [,...n ]
ON { SERVER | DATABASE | QUEUE queue }[;]

Key:
notification_name Name of the event notification to remove.
SERVER Apply to the current server
DATABASE Apply to the current database
queue The name of the queue

Options must must be specified as they originally were when the event notification was created.

Example

DROP EVENT NOTIFICATION testnotify

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)