Asynchronous Stored Procedure

[Baron]

Is it possible to have two procedures running in parallel? I want to call a (B) stored procedure from within a (A) procedure without having (A) to wait until (B) finishes, so that (A) & (B) will be executed in parallel. In the manuals of Sybase there are topics only related to replication server.

[VolkerBarth · Accepted solution]

You can use events to run things "in parallel" as events run on their own connection. So your procedure A could use "trigger event MyProcBEvent(some params)" to start procB asynchronously. This forum has several FAQs dealing with events, see e.g. here. Note, that this means they run as two different transactions, that may or may not fit your requirements.

Apparently, you would then code your event that is calls the according procedure and can use event parameters to supply needed information.

[Baron · Accepted solution]

The example from Mark has helped to get what I need, interesting was the way of passing parameters to the event.

One more question please, how can I grant the permission of 'trigger event' to a normal user? Now is only DBA permitted, but my user is not!

Actually I still have a problem because ProcA & ProcB are modifying the same table, so that I get sometimes an error stating that the table is locked from the other Procedure!! Any ideas hints for this case?

[VolkerBarth · Accepted solution]

Privileges
You must be the owner of the event, or have the MANAGE ANY EVENT system privilege.

However, if the event i triggered from a procedure with default SQL SECURITY DEFINER, I would assume (but do not know) that the event must be "triggerable" by the procedure's owner, not by the caller.

[VolkerBarth · Accepted solution]

Actually I still have a problem because ProcA & ProcB are modifying the same table, so that I get sometimes an error stating that the table is locked from the other Procedure!! Any ideas hints for this case?

Well, now you have two transactions modifying the same table, so the usual isolation level and locking schemes apply...Writers always block other writers on the same row(s) but for reading you may adapt the isolation level - or use mutexes/semaphores to synchronize parts of the execution.

[Breck_Carter · Accepted solution]

> an error stating that the table is locked from the other Procedure!

Exactly what is the error?

If it is SQLCODE -210 then try setting the blocking option to 'ON' and the blocking_timeout to '0' so a blocked connection will wait until the blocking connection releases the locks. Those are the default option settings, and it is a bad idea to change defaults if you don't understand what the options are for.

Also, make sure you do a COMMIT when you're done.

[Baron · Accepted solution]

How can I be the owner of the EVENT? Unlike creating procedures, There is no possibility to nominate the owner during creating a new event (The Syntax is: CREATE EVENT event-name ....), whereas the syntax of creating procedures is (CREATE PROCEDURE [owner.]procedure-name....)

[Baron · Accepted solution]

Moreover, when I user SETUSER myuser, and then try to create the event, then I get an error (Permission denied: you dont have permisstion to use the CREATE EVENT Statement)

[VolkerBarth · Accepted solution]

Ah, alright, I had used the v16 and v17 docs but the newest v17 DCX comment explains that

CREATE [ OR REPLACE ] EVENT [user-name.]event-name...

An owner can be specified but is ignored; events do not have owners. ... Because events do not have owners, no two events can have the same name.

But then the other option (the system privilege) should work:)

[Baron · Accepted solution]

The default value for blocking_timeout in my SQL-10 DB is 20000

[Baron · Accepted solution]

How can grant the MANAGE ANY EVENT privilege to my user? grant MANAGE_ANY_EVENT to USER1;?

[VolkerBarth · Accepted solution]

Oops, it is very helpful to tell beforehand that you are using a real old SQL Anywhere version - v10 has been EOL'ed early 2012...

The mentioned MANAGE ANY EVENT system privilege has been introduced with v16, I guess with older versions, you would need to wrap the trigger event call in a procedure/function, and grant execute on that procedure/function to the desired users.

Nevertheless, the default blocking_timeout value for 10.0.1 (and AFAIK, all versions) is 0, see here...

[Breck_Carter · Accepted solution]

> 20000

Which is 20 seconds... any connection waiting longer than 20 seconds after being blocked will get an exception (and most likely fail).

...which means that no locks should be held longer than 20 seconds if you don't want other connections to fail.

Is that what you want? failed operations rather than waiting longer than 20 seconds?

The default is 0 which is "wait forever"... and almost everyone accepts that.

[Baron · Accepted solution]

The mentioned wrapping (if I get it correct) does not work, I am already triggering the event from within a procedure_A.

CREATE PROCEDURE "usr1"."procedure_A"

begin

.....

trigger event ...

end

GRANT EXECUTE ON "usr1"."procedure_A" TO "usr1";

And then I am connected to DB with dba user (or with usr1) and call the procedure_A but I get the error that I dont have the right for triggering event. The only solution is that I declare the procedure as "dba"."procedure_A" and be connected as dba.

[VolkerBarth · Accepted solution]

Hm, I had expected that the procedure's owner is dba, and you grant execute on that proc to usr1...

[Baron · Accepted solution]

Thanks for the explanation, in my case I should then choose the 0, because the transaction contained in the event must be executed 'anywhen'. Delaying the execution of this transaction is not a big problem.

The DB is several years old, and not initiated by me, I am only afraid if change the value to 0 will have any side effects on already existing functions/triggers.

[Baron · Accepted solution]

Thanks for the hint, I created the wrapper procedure in the name of dba, and then granted execution on this procedure to my user, then everything works fine 🙂

[Baron · Accepted solution]

One more question please, Is there any means to stop the triggered event? I mean in the above block we have used 'trigger event', and actually within this event I have built a wait process then execute further statements, but upon some circumstances I will need to break the wait process of the event.

So, something like 'kill event' or 'terminate event'

[VolkerBarth · Accepted solution]

I guess you have to drop the event connection.

[Breck_Carter · Accepted solution]

You manually DROP CONNECTION in dbisql if you can find the connection number. I think you can also do that Sybase Central and other Clickety-Clack-GUI-Things... the trick, of course, is choosing the connection (don't wanna make a misteak and drop the CEO's golf app 🙂

The approach used inside Foxhound is to have the EVENT record its own @@SPID (connection number) in a table. When some other piece of code wants to drop that event, it SELECTs that connection number and then does a DROP CONNECTION.

Depending on what the EVENT is doing, the DROP CONNECTION may not take effect immediately... especially if it has to roll back gigabytes of uncommitted changes.

[Baron · Accepted solution]

Actually it was a relative big challenge to know (from inside the caller procedure) which connection ID has been assigned to the Event (being triggered from within the procedure). I tried the CREATE VARIABLE inside the procedure, and then from within the EVENT assign the connection ID to this variable -> (without success).

So it looks so that there no any other choice rather than passing the connection ID over a table (Event writes the connection ID in a table, and then the procedure reads from that table)

Thank you for the support.

[VolkerBarth · Accepted solution]

I tried the CREATE VARIABLE inside the procedure, and then from within the EVENT assign the connection ID to this variable -> (without success).

Yes, that cannot work because variables are connection-specific.

However, there are more options:

• Of course, with v17, you can use database-scope variables and use those to share such information...

• And you could also use the MESSAGE ... FOR CONNECTION statement / WAITFOR...AFTER MESSAGE BREAK method to communcate between different connections. That way the event could "tell" its connection number back to the waiting calling procedure. The docs have a very simple sample on that.

[VolkerBarth · Accepted solution]

Aside: That complex topic has been discussed (among others) here and here in this forum before, and would surely be worth to be be discussed again outside those very nested comments:)

[Breck_Carter]

There are some implicit forms of parallel execution within code running on a single connection (e.g., intra-query parallelism ), but you cannot explicitly execute code in parallel within a single connection.

As Volker indicates, you can use CREATE EVENT and TRIGGER EVENT to execute procedure (event) code in parallel by running "subtasks" on separate connections.

Foxhound does this during database startup, and it makes use of ISOLATION LEVEL 0 and a table to determine when the subtasks are complete; SQL Anywhere 17 offers semaphores and mutexes, but the following code was written for SQL Anywhere 16...

CREATE TABLE rroad_subtask_progress (
   task_connection_number      BIGINT NOT NULL,
   subtask_number              INTEGER NOT NULL, -- 0 for main task, 1, 2, 3, ... for subtasks
   subtask_connection_number   BIGINT NOT NULL,
   subtask_started_at          TIMESTAMP NOT NULL DEFAULT '1900-01-01', -- for diagnostic purposes only
   subtask_finished_at         TIMESTAMP NOT NULL DEFAULT '1900-01-01', -- for diagnostic purposes only
   subtask_duration_msec       BIGINT NOT NULL COMPUTE (
                                  IF subtask_started_at >= subtask_finished_at 
                                     THEN 0
                                     ELSE DATEDIFF ( MILLISECOND, subtask_started_at, subtask_finished_at )
                                  END IF ),
   PRIMARY KEY ( task_connection_number, subtask_number ) );

-- Launch the subtasks.

TRIGGER EVENT rroad_startup_subtask1;
TRIGGER EVENT rroad_startup_subtask2;
TRIGGER EVENT rroad_startup_subtask3;

-- Wait for all the subtasks to start.

WHILE EXISTS ( SELECT * 
                 FROM rroad_subtask_progress
                WHERE task_connection_number    = CONNECTION_PROPERTY ( 'Number' )
                  AND subtask_number            > 0
                  AND subtask_connection_number = 0 ) LOOP

   -- Note: WAITFOR causes the worker to block while waiting, reducing the number of workers in the worker pool.

   WAITFOR DELAY '00:00:00.1';

END LOOP;

-- Wait for all the subtasks to finish.

WHILE EXISTS ( SELECT * 
                 FROM rroad_subtask_progress
                WHERE task_connection_number    = CONNECTION_PROPERTY ( 'Number' )
                  AND subtask_number            > 0
                  AND subtask_connection_number <> 0 ) LOOP

   -- Note: WAITFOR causes the worker to block while waiting, reducing the number of workers in the worker pool.

   WAITFOR DELAY '00:00:00.1';

   -- Update any of the subtasks that have finished.

   UPDATE rroad_subtask_progress
      SET subtask_connection_number = 0
    WHERE task_connection_number    = CONNECTION_PROPERTY ( 'Number' )
      AND subtask_number            > 0
      AND subtask_connection_number <> 0
      AND CONNECTION_PROPERTY ( 'Number', subtask_connection_number ) IS NULL;

   COMMIT;

END LOOP;

-- Record the finished timestamp for the main task.

UPDATE rroad_subtask_progress
   SET subtask_finished_at = CURRENT TIMESTAMP
 WHERE task_connection_number = CONNECTION_PROPERTY ( 'Number' )
   AND subtask_number         = 0;

COMMIT;

[Breck_Carter]

> side effects

It is very wise to NOT change any options or settings that you do not understand.

Having said that, is might also be wise to study any option settings that are different from the original defaults.

FYI here are the option settings for fresh V10 database, as displayed by Foxhound...

 -- Options in ddd10 - Mar 23 2019 5:26:07AM - Print - Foxhound © 2019 RisingRoad
 -- All options still have default values
 -- Full set of options      -- Values   -- Description 
 SET OPTION PUBLIC.allow_nulls_by_default    = 'On';     -- Controls NULL values for new columns 
 SET OPTION PUBLIC.allow_snapshot_isolation      = 'Off';    -- Controls enabling of snapshot isolation 
 SET OPTION PUBLIC.ansi_blanks   = 'Off';    -- Controls truncation errors 
 SET OPTION PUBLIC.ansi_close_cursors_on_rollback    = 'Off';    -- Controls whether WITH HOLD cursors are closed on ROLLBACK 
 SET OPTION PUBLIC.ansi_integer_overflow     = 'On';     -- Controls whether integer overflow causes an error 
 SET OPTION PUBLIC.ansi_permissions      = 'On';     -- Controls permissions checking for DELETE and UPDATE statements 
 SET OPTION PUBLIC.ansi_substring    = 'On';     -- Controls behavior of substring function with negative start or length parameter 
 SET OPTION PUBLIC.ansi_update_constraints   = 'Cursors';    -- Controls the range of updates that are permitted 
 SET OPTION PUBLIC.ansinull      = 'On';     -- Controls interpretation of NULL values 
 SET OPTION PUBLIC.assume_distinct_servers   = 'Off';    -- No longer supported 
 SET OPTION PUBLIC.auditing      = 'Off';    -- Enables and disables auditing 
 SET OPTION PUBLIC.auditing_options      = '4294967295';     -- Reserved 
 SET OPTION PUBLIC.automatic_timestamp   = 'Off';    -- Controls default value of new TIMESTAMP columns 
 SET OPTION PUBLIC.background_priority   = 'Off';    -- Controls priority of current connection 
 SET OPTION PUBLIC.blob_threshold    = '256';    -- (SQL Remote only) Controls the size of value that the Message Agent treats as a long object (BLOB) 
 SET OPTION PUBLIC.blocking      = 'On';     -- Controls response to locking conflicts 
 SET OPTION PUBLIC.blocking_timeout      = '0';      -- Controls the time a transaction waits to obtain a lock 
 SET OPTION PUBLIC.chained   = 'On';     -- Controls transaction mode if BEGIN TRANSACTION not used 
 SET OPTION PUBLIC.checkpoint_time   = '60';     -- Maximum number of minutes between checkpoints 
 SET OPTION PUBLIC.cis_option    = '0';      -- Controls display of debug information for remote data access 
 SET OPTION PUBLIC.cis_rowset_size   = '50';     -- Controls number of fetched rows returned from remote servers 
 SET OPTION PUBLIC.close_on_endtrans     = 'On';     -- Controls closing of cursors at end of transaction 
 SET OPTION PUBLIC.collect_statistics_on_dml_updates     = 'On';     -- Controls collection of statistics during INSERT/UPDATE/DELETE statement execution 
 SET OPTION PUBLIC.compression   = '6';      -- Compression enabled 
 SET OPTION PUBLIC.conn_auditing     = 'On';     -- Enables and disables auditing on a connection 
 SET OPTION PUBLIC.connection_authentication     = '';   -- Authentication string for connection 
 SET OPTION PUBLIC.continue_after_raiserror      = 'On';     -- Controls behavior following a RAISERROR statement 
 SET OPTION PUBLIC.conversion_error      = 'On';     -- Controls datatype conversion errors 
 SET OPTION PUBLIC.cooperative_commit_timeout    = '250';    -- Controls delay before a COMMIT is written to disk 
 SET OPTION PUBLIC.cooperative_commits   = 'On';     -- Controls when COMMITs are written to disk 
 SET OPTION PUBLIC.database_authentication   = '';   -- Authentication string for database 
 SET OPTION PUBLIC.date_format   = 'YYYY-MM-DD';     -- Controls format for DATE values 
 SET OPTION PUBLIC.date_order    = 'YMD';    -- Controls order of date components 
 SET OPTION PUBLIC.debug_messages    = 'Off';    -- Controls whether MESSAGE ... DEBUG ONLY statements are executed 
 SET OPTION PUBLIC.dedicated_task    = 'Off';    -- Dedicates a server task to the current connection 
 SET OPTION PUBLIC.default_dbspace   = '';   -- Sets the default dbspace for table creation 
 SET OPTION PUBLIC.default_timestamp_increment   = '1';      -- Number of microseconds to add to TIMESTAMP for next value 
 SET OPTION PUBLIC.delayed_commit_timeout    = '500';    -- Controls delay before server returns control while waiting to do COMMIT 
 SET OPTION PUBLIC.delayed_commits   = 'Off';    -- Controls when server returns control to application following a COMMIT 
 SET OPTION PUBLIC.delete_old_logs   = 'Off';    -- Controls whether transaction logs are deleted when their transactions have been replicated or synchronized 
 SET OPTION PUBLIC.divide_by_zero_error      = 'On';     -- Controls divide-by-zero errors 
 SET OPTION PUBLIC.encrypt_aes_random_iv     = 'On';     -- Whether to use a random initialization vector for the encrypt function 
 SET OPTION PUBLIC.escape_character      = 'On';     -- Reserved 
 SET OPTION PUBLIC.exclude_operators     = '';   -- Reserved 
 SET OPTION PUBLIC.extended_join_syntax      = 'On';     -- Controls errors when using duplicate correlation names in joins 
 SET OPTION PUBLIC.external_remote_options   = 'Off';    -- (SQL Remote only) Indicates whether the message link parameters should be stored in the database 
 SET OPTION PUBLIC.fire_triggers     = 'On';     -- Controls whether triggers are fired in the database 
 SET OPTION PUBLIC.first_day_of_week     = '7';      -- Sets the numbering of the days of the week 
 SET OPTION PUBLIC.float_as_double   = 'Off';    -- Controls the interpretation of the FLOAT type 
 SET OPTION PUBLIC.for_xml_null_treatment    = 'Omit';   -- Controls treatment of NULL values in queries that use FOR XML 
 SET OPTION PUBLIC.force_view_creation   = 'Off';    -- Controls errors when CREATE VIEW references non-existent view 
 SET OPTION PUBLIC.global_database_id    = '2147483647';     -- Controls initial value for DEFAULT GLOBAL AUTOINCREMENT columns 
 SET OPTION PUBLIC.http_session_timeout      = '30';     -- Timeout setting for an HTTP session 
 SET OPTION PUBLIC.integrated_server_name    = '';   -- Server name for determining user groups 
 SET OPTION PUBLIC.isolation_level   = '0';      -- Controls the locking isolation level 
 SET OPTION PUBLIC.java_location     = '';   -- File path pointing to the external java vm 
 SET OPTION PUBLIC.java_main_userid      = '';   -- Userid used by external java vm to connect to the database 
 SET OPTION PUBLIC.java_vm_options   = '';   -- Command line options used to launch external java vm 
 SET OPTION PUBLIC.lock_rejected_rows    = 'Off';    -- Reserved 
 SET OPTION PUBLIC.log_deadlocks     = 'Off';    -- Controls whether deadlocks are logged 
 SET OPTION PUBLIC.login_mode    = 'Standard';   -- Controls integrated and Kerberos logins 
 SET OPTION PUBLIC.login_procedure   = 'sp_login_environment';   -- Procedure to be run during login 
 SET OPTION PUBLIC.materialized_view_optimization    = 'Stale';      -- Controls the use of materialized views during query optimization 
 SET OPTION PUBLIC.max_client_statements_cached      = '10';     -- Maximum number of prepared statements cached by the client for a connection 
 SET OPTION PUBLIC.max_cursor_count      = '50';     -- Maximum number of cursors allowed for a connection 
 SET OPTION PUBLIC.max_hash_size     = '10';     -- Deprecated 
 SET OPTION PUBLIC.max_plans_cached      = '20';     -- Maximum number of cached execution plans for a connection 
 SET OPTION PUBLIC.max_query_tasks   = '0';      -- Maximum number of tasks that may be used by a parallel execution plan for a single query 
 SET OPTION PUBLIC.max_recursive_iterations      = '100';    -- Maximum number of recursions for common table expressions 
 SET OPTION PUBLIC.max_statement_count   = '50';     -- Maximum number of prepared statements for a connection 
 SET OPTION PUBLIC.max_temp_space    = '0';      -- Sets the maximum temp space that a connection may use 
 SET OPTION PUBLIC.min_password_length   = '0';      -- Minimum length for new database passwords 
 SET OPTION PUBLIC.nearest_century   = '50';     -- Controls interpretation of two-digit years 
 SET OPTION PUBLIC.non_keywords      = '';   -- Controls what identifiers are keywords 
 SET OPTION PUBLIC.odbc_describe_binary_as_varbinary     = 'Off';    -- Controls whether ODBC describes BINARY columns as VARBINARY 
 SET OPTION PUBLIC.odbc_distinguish_char_and_varchar     = 'Off';    -- Controls whether ODBC distinguishes CHAR and VARCHAR columns 
 SET OPTION PUBLIC.oem_string    = '';   -- Sets the OEM string in the database file header 
 SET OPTION PUBLIC.on_charset_conversion_failure     = 'Ignore';     -- Controls error handling for character set conversion failure 
 SET OPTION PUBLIC.on_tsql_error     = 'Conditional';    -- Controls error handling in stored procedures 
 SET OPTION PUBLIC.optimistic_wait_for_commit    = 'Off';    -- Controls locking behavior for WAIT_FOR_COMMIT option 
 SET OPTION PUBLIC.optimization_goal     = 'All-rows';   -- Optimize queries for first row or all rows 
 SET OPTION PUBLIC.optimization_level    = '9';      -- Controls amount of effort made by the query optimizer to find an access plan 
 SET OPTION PUBLIC.optimization_workload     = 'Mixed';      -- Controls whether optimizing for OLAP or mixed queries 
 SET OPTION PUBLIC.percent_as_comment    = 'On';     -- Controls the interpretation of the percent character 
 SET OPTION PUBLIC.pinned_cursor_percent_of_cache    = '10';     -- Controls amount of server cache to be used for pinning cursors 
 SET OPTION PUBLIC.post_login_procedure      = '';   -- Procedure to be called by the application when connecting to return messages 
 SET OPTION PUBLIC.precision     = '30';     -- Maximum number of digits in decimal arithmetic 
 SET OPTION PUBLIC.prefetch      = 'Conditional';    -- Controls prefetching of rows 
 SET OPTION PUBLIC.preserve_source_format    = 'On';     -- Controls preservation of source for procedures, triggers, views, events 
 SET OPTION PUBLIC.prevent_article_pkey_update   = 'On';     -- Controls updates to primary keys used in publications 
 SET OPTION PUBLIC.qualify_owners    = 'On';     -- (SQL Remote only) Controls whether SQL statements being replicated by SQL Remote should use qualified object names 
 SET OPTION PUBLIC.query_plan_on_open    = 'Off';    -- Controls whether query plan is returned when cursor is opened 
 SET OPTION PUBLIC.quote_all_identifiers     = 'Off';    -- (SQL Remote only) Controls whether SQL statements being replicated by SQL Remote should use quoted identifiers 
 SET OPTION PUBLIC.quoted_identifier     = 'On';     -- Controls interpretation of strings enclosed in double quotes 
 SET OPTION PUBLIC.read_past_deleted     = 'On';     -- Controls server behavior on uncommitted deletes 
 SET OPTION PUBLIC.recovery_time     = '2';      -- Maximum time to allow for database recovery 
 SET OPTION PUBLIC.remote_idle_timeout   = '15';     -- Controls the time that an HTTP procedure will wait for a response from a server 
 SET OPTION PUBLIC.replicate_all     = 'Off';    -- Allows entire database to act as primary site for Rep Server 
 SET OPTION PUBLIC.replication_error     = '';   -- (SQL Remote only) Allows you to specify a stored procedure to be called by the Message Agent when a SQL error occurs 
 SET OPTION PUBLIC.replication_error_piece   = '';   -- (SQL Remote only) Allows you to specify a long varchar stored procedure to be called by the Message Agent when a SQL error occurs 
 SET OPTION PUBLIC.request_timeout   = '0';      -- Controls the maximum time a request is allowed to execute 
 SET OPTION PUBLIC.return_date_time_as_string    = 'Off';    -- Controls how DATE, TIME and TIMESTAMP values are fetched 
 SET OPTION PUBLIC.ri_trigger_time   = 'After';      -- Controls timing of referential integrity checks and triggers 
 SET OPTION PUBLIC.rollback_on_deadlock      = 'On';     -- Controls whether or not a transaction does a rollback upon deadlock 
 SET OPTION PUBLIC.row_counts    = 'Off';    -- Controls whether row counts are estimates or exact 
 SET OPTION PUBLIC.save_remote_passwords     = 'On';     -- (SQL Remote only) Controls whether passwords are saved when entered into the message link dialog box on first connection 
 SET OPTION PUBLIC.scale     = '6';      -- Minimum number of digits after decimal point 
 SET OPTION PUBLIC.secure_feature_key    = '';   -- Sets the secure feature override key 
 SET OPTION PUBLIC.sort_collation    = 'Internal';   -- Controls implicit use of SORTKEY on ORDER BY of characters 
 SET OPTION PUBLIC.sql_flagger_error_level   = 'W';      -- Controls errors for SQL that is not from specified set of SQL/92 
 SET OPTION PUBLIC.sql_flagger_warning_level     = 'W';      -- Controls warnings for SQL that is not from specified set of SQL/92 
 SET OPTION PUBLIC.sr_date_format    = 'yyyy/mm/dd';     -- (SQL Remote only) Sets the format for dates retrieved from the database 
 SET OPTION PUBLIC.sr_time_format    = 'hh:nn:ss.Ssssss';    -- (SQL Remote only) Sets the format for times retrieved from the database 
 SET OPTION PUBLIC.sr_timestamp_format   = 'hh:nn:ss.Ssssss yyyy/mm/dd';     -- (SQL Remote only) Sets the format for timestamps that are retrieved from the database 
 SET OPTION PUBLIC.string_rtruncation    = 'On';     -- Controls truncation errors on INSERT or UPDATE 
 SET OPTION PUBLIC.subscribe_by_remote   = 'On';     -- (SQL Remote only) Controls interpretation of NULL or empty-string SUBSCRIBE BY values 
 SET OPTION PUBLIC.subsume_row_locks     = 'On';     -- Controls when server acquires row locks for table 
 SET OPTION PUBLIC.suppress_tds_debugging    = 'Off';    -- Controls display of TDS debug information 
 SET OPTION PUBLIC.synchronize_mirror_on_commit      = 'Off';    -- Controls whether database mirror server is synchronized on commit 
 SET OPTION PUBLIC.tds_empty_string_is_null      = 'Off';    -- Controls whether TDS connections return empty strings as NULL or one space 
 SET OPTION PUBLIC.temp_space_limit_check    = 'On';     -- Controls whether a connection's temp space usage is checked against an upper limit 
 SET OPTION PUBLIC.time_format   = 'HH:NN:SS.SSS';   -- Controls format for TIME values 
 SET OPTION PUBLIC.time_zone_adjustment      = '0';      -- Numbers of minutes to add to UTC for this time zone 
 SET OPTION PUBLIC.timestamp_format      = 'YYYY-MM-DD HH:NN:SS.SSS';    -- Controls format for TIMESTAMP values 
 SET OPTION PUBLIC.truncate_timestamp_values     = 'Off';    -- Controls precision of TIMESTAMP values 
 SET OPTION PUBLIC.truncate_with_auto_commit     = 'On';     -- Enables faster TRUNCATE TABLE statements 
 SET OPTION PUBLIC.tsql_hex_constant     = 'On';     -- Controls whether hexadecimal constants are BINARY type 
 SET OPTION PUBLIC.tsql_outer_joins      = 'Off';    -- Controls whether TSQL outer joins can be used in DML statements 
 SET OPTION PUBLIC.tsql_variables    = 'Off';    -- Controls whether @ can be used as host variable name prefix 
 SET OPTION PUBLIC.updatable_statement_isolation     = '0';      -- The isolation level for updatable statements when using readonly-statement-snapshot 
 SET OPTION PUBLIC.update_statistics     = 'On';     -- Controls collection of statistics during query execution 
 SET OPTION PUBLIC.user_estimates    = 'Override-magic';     -- Controls whether to respect user estimates 
 SET OPTION PUBLIC.uuid_has_hyphens      = 'On';     -- Controls format for UUID values 
 SET OPTION PUBLIC.verify_all_columns    = 'Off';    -- (SQL Remote only) Controls whether messages that contain updates published by the local database are sent with all column values included 
 SET OPTION PUBLIC.verify_password_function      = '';   -- Function to be run to verify a new password conforms to password rules 
 SET OPTION PUBLIC.verify_threshold      = '1000';   -- (SQL Remote only) Controls which columns are verified when updates are replicated 
 SET OPTION PUBLIC.wait_for_commit   = 'Off';    -- Controls when foreign key integrity is checked 
 SET OPTION PUBLIC.webservice_namespace_host     = '';   -- Server hostname specification for use in web services namespace