You use the Advanced page of the Server Properties dialog box to configure most general server settings. As shown in Figure 6-6, you can set the default language, general server behavior, and other options on this page.
The default language determines default display formats for dates as well as the names of months and days. All output is in U.S. English unless you are running a localized version of SQL Server. Localized versions of SQL Server are available for French, German, Japanese, Spanish, and other languages. On a localized server, two sets of system messages are available, one in U.S. English and one in the local language. If the default language is set to the local language, SQL Server messages are displayed in the local language. Otherwise, they are displayed in U.S. English.
On the Advanced page of the Server Properties dialog box, use the Default Language drop-down list box to select the default language, and then click OK. You must stop and restart the server to apply a new default language setting. With sp_configure, the related Transact-SQL statement is:
exec sp_configure "default language", <language id number>The language ID number for U.S. English is always 0. The sys.languages system view contains one row for each language present on a server.
By default, users can only update the systems table with system stored procedures, even if they have proper permissions. This is a valuable feature because it prevents users from executing statements that may corrupt the database or could prevent SQL Server from starting. However, you may want to change this behavior to allow direct updates to system tables. Once you allow modifications, anyone with proper permissions can update systems tables by executing statements or stored procedures.
It is risky to give this control to other users. To minimize the risk involved, follow this procedure to update systems tables:
From the Server Properties dialog box, go to the Security page.
Enable system table modifications by selecting Allow Direct Updates To System Tables.
Click OK, and then stop SQL Server.
Start SQL Server in single-user mode at the command line by typing sqlservr -m.
Make the necessary changes to the system tables.
From the Server Properties dialog box, go to the Security page.
Disable system table modifications by clearing the Allow Direct Updates To System Tables checkbox.
From the command line in the window running SQL Server, press Ctrl+C.
When prompted, type Y for Yes. This stops SQL Server.
Restart the SQL Server service.
With sp_configure, the related Transact-SQL statement to allow updates to systems tables is:
exec sp_configure "allow updates", <0 or 1>By default, SQL Server allows you to nest up to 32 levels of triggers. Nested triggers are useful for executing a series of tasks within a single transaction. For example, an action can initiate a trigger that starts another trigger, which in turn can start another trigger, and so on. Because the trigger is handled within a transaction, a failure at any level causes the entire transaction to roll back, which reverses all changes to the database. As a fail-safe measure, triggers are terminated when the maximum nesting level is exceeded. This protects against an infinite loop.
An option on the Misc Server Settings page allows you to configure SQL Server to use nested triggers. To do so, complete the following steps:
From the Server Properties dialog box, go to the Advanced page.
Set Allow Triggers to True or False as appropriate.
Click OK.
With sp_configure, the related Transact-SQL statement is:
exec sp_configure "nested triggers", <0 or 1>You use 0 to disable and 1 to enable.
The query governor does not allow the execution of any query that has a running time that exceeds a specified query cost. The query cost is the estimated time, in seconds, required to execute a query, and it is estimated prior to execution based on an analysis by the query engine. By default, the query governor is turned off, meaning there is no maximum cost. To activate the query governor, complete the following steps:
From the Server Properties dialog box, go to the Connections page.
Select the option Use Query Governor To Prevent Long-Running Queries.
In the box below the option, type a maximum query cost limit. The valid range is 0 to 2,147,483,647. A value of 0 disables the query governor; any other value sets a maximum query cost limit.
Click OK.
With sp_configure, the following Transact-SQL statement will activate the query governor:
exec sp_configure "query governor cost limit", <limit>You can also set a per-connection query cost limit in Transact-SQL using the following statement:
set query_governor_cost_limit <limit>SQL Server allows you to insert or modify dates without specifying the century part of the date. However, to be Year 2000 compliant, SQL Server interprets two-digit dates within a certain time span. By default, this time span includes the years 1950 to 2049. Using this default setting, all two-digit dates from 50 to 99 are read as years beginning with 19, and all two-digit dates from 00 to 49 are read as years beginning with 20. Thus, SQL Server would interpret a two-digit year of 99 as 1999 and a two-digit year of 02 as 2002.
To maintain backward compatibility, Microsoft recommends that you leave the setting at the default value. You can, however, change this value by completing the following steps:
Note
The time span you select will affect all databases on the current server. Also, some older OLE clients only support dates in a range of years from 1931 to 2030. To maintain compatibility with these clients, you may want to use 2030 as the ending year for the time span.
With sp_configure, the related Transact-SQL statement is:
exec sp_configure "two digit year cutoff", <ending year>