O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required