Background:
Cursors control the behavior of server-side cursors, with the associated T-SQL as follows:
Declare, open, Fetch, close, deallocate.
1.
Cursor_close_on_commit{on | off};
If set to on closes the open cursor when the transaction commits or rolls back,
If set to OFF, the cursor will continue to open after the transaction is committed, unless the cursor is defined as static and the rollback transaction closes any cursors.
ALTER DATABASE Studio
set CURSOR_CLOSE_ON_COMMIT on; # you can see there's no = number here! Remember
Go
2.
Cursor_default {local | global}
If set to local does not designate the cursor as global when it is defined, it is scoped only to the batch, stored procedure, and trigger in which it resides.
If set to global does not specify the cursor as local when it is defined, it is global.
ALTER DATABASE Studio
set Cursor_default local; # You can see there's no = number here! Remember
Go
-----------------------------------------------------------------------------------------------------
SQL Server Database cursor options