Stored Procedure: easily filter SQL Server connections

In this article, we will discuss system stored procedures and use a routine to list information about SQL Server connections or operations performed on a selected connection.

The SQL Server database administrator must always be cautious about connecting to the Server. For an application in a certain mode, the administrator needs to monitor these connections; List problematic connections, and then take actions to trim them; or perform operations on a selected connection, such as ending a connection. This is difficult, but not impossible. to execute these tasks, you need to use Enterprise Manager ). A busy database administrator needs a more convenient tool.

The SQL code in List 1 creates a system stored procedure named sp_ListConnections.

This sp_ListConnections Stored Procedure accepts eight parameters, but none of them are required.

The first parameter (@ DBUltra) is optional and can be used to specify whether to restrict the list to only display blocked connections. The value 0 indicates that all qualified connections should be listed. 1 indicates that only the blocked and blocked connections are listed.

The second parameter (@ PCUltra) is optional to specify whether to restrict the list to displaying only those active connections (processing T-SQL statements .) The value 0 indicates that all qualified connections should be listed. The value 1 indicates that only active connections are displayed.

The following four parameters are optional and used together to form a combination of names as key conditions for selection. Refer to my previous article to see my explanation of how these parameters work. These Parameters specify the database, application, login, or client to be considered.

The seventh parameter (@ DBTrain) is optional and used to specify how to apply the first four parameters. The value D indicates that a database is selected by name using these parameters. A value indicates that the application is selected by name using these parameters. The value L indicates that the login name is selected using these parameters. Other values indicate that the client name is selected by using these parameters.

The last parameter (@ PCTrain) is optional and can be used to specify what operations are performed on a selected connection. This operation can be to execute T-SQL code on each connection. You can also send messages to clients. Clients that receive messages are those that are included in other parameters. All @ DBTrain values are valid and only one message is sent to any client.

If the value of @ PCTrain includes the string "@ SPID", assume it is the T-SQL code. After "@ SPID" is replaced with the current connection ID, The T-SQL code is executed on each connection.

If the value of @ PCTrain is a simple text message, the message will be transmitted to the client. A message may not contain CR/LF characters.

If the @ PCTrain value is a number, it is considered as the SQL Server error number, and the corresponding information is found in the sysmessages table and sent to the client. Customer messages can be added to the sysmessages table and stored in the sp_addmessage System (view MSDN for details ).

Note: the ability to send messages to clients depends on Windows message service. The service on the machine and client where SQL Server is located must be started. The sp_ListConnections stored procedure returns information about the SQL Server connection or the execution of an operation on a connection. The connection is filtered by @ DBUltra, @ PCUltra, and other parameters. If the specified operation provided by @ PCTrain is executed, no set of connection information will be returned.

We recommend that you use the user options in the tool menu in the Query Analyzer to create the correct stored procedure call, so that it can be executed through a simple keyboard combination. The screenshot below demonstrates my suggestions.

User options

Note that in the following example, the webpage format may cause a parameter value to be transferred to the second line. If so, delete the additional CR/LF before executing the code.

The following example lists information about blocked connections.

EXECUTE sp_ListConnections 1

This example uses the SQL Agent task system to list the connection information.

EXECUTE sp_ListConnections 0, 0, NULL, NULL, 'sqlagent % ', NULL, 'A'

This example lists all active connections on the Northwind database.

EXECUTE sp_ListConnections 0, 1, NULL, NULL, 'northwind ', NULL, 'D'

In this example, a customer message is added to the sysmessages table.

EXECUTE sp_addmessage, N 'the server will be restarted in 10 minutes .'

In this example, a customer message is sent to all computers connected to the server.

EXECUTE sp_ListConnections @ PCTrain = '2013'

In this example, a text message is sent to the computer that connects to the Northwind database.

EXECUTE sp_ListConnections, 'northwind ', NULL, 'D', 'the Northwind database will go offline in 10 minutes .'

In this example, the connection to the Northwind database is interrupted.

EXECUTE sp_ListConnections 0, 0, 'northwind ', NULL, 'D', 'Kill @ spid'

I hope this system stored procedure can be useful to you.

(