SQL in a Nutshell

Name

SET CONNECTION

Synopsis

The SET CONNECTION statement allows users to switch between several open connections on one or more database servers.

Vendor	Command
SQL Server	Supported, with limitations
MySQL	Not supported
Oracle	Not supported
PostgreSQL	Not supported

SQL99 Syntax and Description

SET CONNECTION {DEFAULT | connection_name}

This command does not end a connection. Instead, it switches from the current connection to the connection named in the command, or to the current connection using the DEFAULT clause. When switching between connections, the old connection becomes dormant (without committing any changes), while the new connection becomes active.

The CONNECT command must be used to create a new connection; the DISCONNECT command is used to terminate one.

Microsoft SQL Server Syntax and Variations

Microsoft SQL Server supports SET CONNECTION only in Embedded-SQL (ESQL), but not within its ad hoc querying tool, SQL Query Analyzer. It supports the full SQL99 syntax.

Example

Here is a full ESQL program in SQL Server that shows CONNECT, DISCONNECT, and SET CONNECTION :

EXEC SQL CONNECT TO chicago.pubs AS chicago1 USER sa; EXEC SQL CONNECT TO new_york.pubs AS new_york1 USER read-only; // opens connections to the servers named "chicago" // // and "new_york"// EXEC SQL SET CONNECTION chicago1; EXEC SQL SELECT name FROM employee INTO :name; // sets the chicago1 connection as active and performs work // // within that session // EXEC SQL SET CONNECTION new_york1; EXEC SQL SELECT ...