514
Part V: Enterprise Data Management
Cursor Close on Commit
The cursor close on commit property closes an open cursor after a transaction is com-
mitted when set to on. If it is set to off (default), then cursors remain open across trans-
actions until a close cursor statement is issued.The cursor close on commit option can be set from Management Studio and code at
server, database, and connection level. In Management Studio, the
cursor close on commit option can be turned on at the following level:■ (^) Server level: Checks the Cursor Close on Commit check box on the Server properties
Connections tab (refer to Figure 19-9).
■ (^) Database level: Selects True for the Close Cursor on Commit Enabled box on the
Database Properties Options tab (refer to Figure 19-2).
■ (^) Connection level: To set this property for current queries, click the Query
menu ➪ Query Options ➪ Execution ➪ ANSI, and check the Set Cursor Close on
Commit check box. To set this property for all future connections, click the Tools
menu ➪ Options ➪ Query Execution ➪ ANSI, and check Set Cursor Close on Commit
check box
To set cursor close on commit in code, do the following:
■ (^) Server level: Sets the option on for the server:
EXEC sp_configure 'user options', 4;
RECONFIGURE;
■ (^) Database level: Sets the option on for the AdventureWorks2012 sample database:
ALTER DATABASE AdventureWorks2012 SET CURSOR_CLOSE_ON_COMMIT ON;
■ (^) Connection level: Sets the option on for the current connection:
SET CURSOR_CLOSE_ON_COMMIT ON;
Cursor Default
This property makes each cursor local to the object that declared it when set to local.
When it is set to global (default), the scope of the cursor can be extended outside the
object that created it.
In Management Studio, you can set the cursor default option to the wanted scope in
the Default Cursor box on the Database Properties Options tab (refer to Figure 19-2).To set
the cursor default for the AdventureWorks2012 sample database to LOCAL in code, do the
following:
ALTER DATABASE AdventureWorks2012 SET CURSOR_DEFAULT LOCAL;
SQL ANSI–Confi guration Properties
The SQL ANSI–confi guration properties, as shown in Table 19-9, are used to set ANSI behav-
ior in SQL Server.
c19.indd 514c19.indd 514 7/30/2012 5:43:09 PM7/30/2012 5:43:09 PM
http://www.it-ebooks.info