xp_trace_setqueuedestination (T-SQL)

Specifies the trace queue destination filter, or the destination for the events specified. Microsoft® SQL Server™ captures the required information, configured by executing xp_trace_seteventclassrequired, and sends the captured information to the specified destination.

Syntax

xp_trace_setqueuedestination
    {
        queue_handle, destination, value
            {[, 'server'] [, 'object'] }
    }

Arguments

queue_handle

Is an integer that represents the handle for the trace queue. queue_handle is used to reference the trace queue and is placed in an output parameter by xp_trace_addnewqueue.

destination

Is an integer that indicates the destination for the trace queue data. destination can be one of these values.

 

Value	Description
2	To file
3	To application log
4	To table
5	To forwarded server

If multiple destinations are required, xp_trace_setqueuedestination can be called multiple times. Here are some guidelines for working with destinations:

• If sending to a table, be sure to set the application parameter of xp_trace_generate_event; otherwise, no data is added to the destination table.
• If a new type value is specified (for example, To table was previously chosen and the most recently executed value for xp_trace_setqueuedestination is now To file), trace queue information is sent to both destinations.
• If type was previously To file and a new file name is specified, the new file is used as the destination.
• A maximum of one file name and one table can be specified as destinations for any one trace queue.
• If type is To file and subsequently changed To table, xp_trace_
  setqueuedestination must be called twice: the first to turn off the file destination, and the second to turn on the table destination.
• There is a maximum number of one type of each destination type, and a maximum of four total destinations for a trace queue.

value

Is whether the destination is enabled (1) or not (0).

'server'

Is the name of the registered server to which the appropriate trace queue information is sent. server is string. If destination is forwarding (5) or application log (3), this parameter is the name of the server for which events should be forwarded, or the server application log for which to send trace queue information.

'object'

Is the name of the file (if destination is 2) or table (if destination is 4) for which to send trace queue information. object is string, and can be an existing table or file (depending on destination), or it can be nonexisting. If object does not exist, SQL Server creates it.

---

Note Files created by xp_trace_setqueuedestination have the trace file format. To see a trace file from the SQL Server Profiler graphical interface, open the file as a trace file rather than as an SQL script.

---

Return Code Values

0 (success) or >1 (failure)

Result Sets

xp_trace_setqueuedestination returns this message:

The command(s) completed successfully.

Remarks

Assume there are two servers: S1 and S2. If a client (named client) writes to a table on server S2 from server S1, server S2 sees the security context of server S1. In future releases of Microsoft Windows NT®, server S2 will see the security context of the client rather than of server S1.

Forwarding is a process that involves two or more servers and allows traced events from one or more servers to be forwarded to a designated main SQL Server. Before enabling forwarding, the trace queue must be created on the originating computer. Then, forwarding (by using destination) can be enabled. In addition, the required data must include the server name.

All events captured by the main, managing SQL Server get compiled into one master list of events, which is prefaced by the originating server name. This compiled event list is useful for minimizing storage space because there is only one file to store rather than two or more (each server, without forwarding, stores its own event file). Forwarding can also assist in load balancing and central server administration because one central server can be monitored rather than many individual servers.

Here are some guidelines for using forwarding:

• Each server can only forward to one other server once.
• No recursion is possible.
• Only events that were created by the originating server can be forwarded. Events forwarded to a server cannot be forwarded again.
• With three servers, any one of the servers can see what events and data each of the other servers is capturing.

Permissions

Execute permissions for xp_trace_setqueuedestination default to members of the sysadmin fixed server role but can be granted to other users.

Examples

This example sends the trace queue information to both a file and a table.

USE master

DECLARE @queue_handle int, @column_value int

SET @column_value = 16|32|8192|128|512

EXEC xp_trace_addnewqueue 1000,
    5,
    95,
    90,
    @column_value,
    @queue_handle OUTPUT

EXEC xp_trace_setqueuedestination @queue_handle,
    2,
    0,
    NULL,
    'c:\myfile.txt'

EXEC xp_trace_setqueuedestination @queue_handle,
    4,
    0,
    'SEATTLE2',
    'mytable'

See Also