Questions, Answers, and Tips About SQL Server - 01 Nov 1998

Q: What's the purpose of SQL Namespace (SQL NS) and SQL NS objects?

SQL NS objects are powerful component object model (COM) interfaces that let you access and manipulate SQL Server 7.0 Enterprise Manager (EM)'s built-in wizards, property sheets, dialog boxes, and other user interface components. SQL NS offers greater functionality than SQL Server Distributed Management Objects (SQL-DMO) offers. SQL DMO is a series of COM objects that expose the underlying administrative capabilities of SQL Server. For example, using DMO, you can build a custom database transfer utility that replicates EM's ability to transfer databases from one server to another. SQL NS objects take this capability one step further by letting you invoke the same transfer database dialog box that EM uses. You can set properties on the EM user interface component from your custom application. This technique is more powerful than simple DMO, because you don't reinvent the wheel every time you need to build an administration utility. SQL Server Books Online (BOL--at MssqlDevtoolsSamplesSqlnsVbDbprop if you've used the installation defaults; d:devtoolssamplessqlnsvbdbprop off the SQL Server 7.0 beta 3 CD-ROM) provides a simple working sample application.

Q: Does SQL Server 7.0 support trusted connections over a TCP/IP sockets connection?

Yes. SQL Server 7.0 supports integrated security (trusted connections) over a TCP/IP sockets connection. Trusted connections let users use their Windows NT credentials (i.e., a username and password) to log in to SQL Server. SQL Server 6.x limited the single sign-on option to connections that used Named Pipes or Multiprotocol Net-Libraries.

Q: Is BULK INSERT an improvement over bulk copy program (bcp)?

BULK INSERT (new in SQL Server 7.0) might help you avoid some of the xp_cmdshell-based workarounds you've used, for example, to load data directly from a Transact-SQL (T-SQL) session. But at its roots, the BULK INSERT command is little more than a T-SQL wrapper around the bcp utility. Unfortunately, as of SQL Server 7.0 beta 3, Microsoft hasn't seen fit to give users a BULK EXPORT command.

As you can see in the syntax diagram in Listing 1, page 200, BULK INSERT accepts the KEEPIDENTITY clause (much as bcp accepts the -E parameter), which prevents SQL Server from supplying identity values. If you don't use this clause, SQL Server will assign unique (new) identity values.

Q: Last month, you mentioned ANSI NULLs and potential compatibility problems between SQL Server 6.5 and SQL Server 7.0. What other compatibility problems am I likely to encounter when I upgrade old applications to SQL Server 7.0?

Microsoft has done a good job of handling backward compatibility conflicts by letting you control the compatibility level that your new SQL Server 7.0 installation will run under. Use the command

sp_dbcmptlevel [[@dbname =] name] [, [@new_cmptlevel =] version]

This command controls whether a specific database acts like a SQL Server 6.0, SQL Server 6.5, or SQL Server 7.0 database. We won't cover all of the differences between levels, but you can get detailed information from SQL Server Books Online (BOL) by searching for Setting a Backward Compatibility Level.

Sp_dbcmptlevel minimizes the effect of upgrading to SQL Server 7.0 while still letting SQL Server 6.x applications remain as unchanged as possible. Although Microsoft will continue to support this system stored procedure in future versions of SQL Server, Microsoft strongly recommends that you eventually update your applications to the latest compatibility level. In future releases, Microsoft might not continue to support features just for backward compatibility.

Of course, running a database under a SQL Server 6.0 or SQL Server 7.0 compatibility level means that you can't take advantage of all the new SQL Server 7.0 functionality. The backward compatibility function is helpful if you want to support legacy SQL Server applications and new SQL Server 7.0 development on the same box. This way, you can write your new applications to take advantage of all the cool new features and support your old applications under SQL Server 7.0 without breaking anything. We'll look more deeply at backward compatibility when we have more information.

Q: I loved using SQLTrace for tuning my applications. Will SQL Server 7.0 continue to offer this utility?

SQL Profiler has replaced SQLTrace and offers many new capabilities for tracing your applications. We'll cover SQL Profiler in detail in an upcoming column, but Brian wants to share his favorite Profiler feature right away.

I was always frustrated by SQLTrace's inability to see inside a stored procedure, because I wanted to trace individual statements within the procedure. SQLTrace told me how long the procedure ran, but not how long individual commands in the procedure took. SQLTrace tracks at the batch level; SQL Profiler lets me track statement execution and all statement details within a batch. If you've used SQLTrace for any serious tuning efforts, you'll know that this is an important improvement.

Q: SQL Server 6.5 doesn't let me handle blocking locks gracefully. For example, you can't set a wait-for-lock timeout parameter that returns control to the program if SQL Server blocks a query too long. Does SQL Server 7.0 address this problem?

Blocking locks are an annoying problem under SQL Server 6.x. Let's say you issue the query

SELECT * FROM Authors

and another user has the authors table exclusively locked. SQL Server blocks your command, because another user is holding locks on the object you're trying to access. The locks block your query and prevent it from running. You probably realize that blocking locks are a normal occurrence in a multiuser relational database management system (RDBMS), and you wouldn't want the query to fail immediately. SQL Server 6.x keeps your query in a blocked state indefinitely until the query reaches the timeout parameter or the user releases the blocking locks.

However, these options aren't always what you want to happen. Other RDBMSs provide mechanisms that let you fail the blocked query after it exceeds a lock-time threshold (which generally is a much shorter time than a query timeout). SQL Server 7.0's SET LOCK_TIMEOUT command gives you that ability, too. For example, queries time out after about 5000 milliseconds (5 seconds) and return an error message to the client application. The client application can then reissue the query or display a message to users asking them to try again later. Without a timeout parameter, the user has no idea what's happening. Maybe the query is running very slowly; maybe the system is hung. The new LOCK_TIMEOUT option offers more user-friendly behavior.

LOCK_TIMEOUT defaults to ­1, which mimics the old SQL Server 6.x behavior of waiting indefinitely for a blocked query to become free. You can check the connection-specific global variable @@ LOCK_TIMEOUT if you're not sure what the current setting is for your connection.

Q: I was confused about quotes and delimiters in SQL Server 6.5. Are they even worse in SQL Server 7.0?

In both SQL Server 6.x and SQL Server 7.0, you use single quotation marks around character data. Identifiers define the naming conventions that SQL Server uses for server names, database names, and database objects (e.g., tables, views, columns, procedures). Identifiers in SQL Server 6.x can be up to 30-characters long and can include certain special characters but can't include embedded spaces, unless you use the quoted identifiers option to define them. (Microsoft added support for quoted identifiers in SQL Server 6.0 to comply with SQL-92, which lets you use keywords in SQL statements if you enclose them in double quotation marks.) SQL Server 7.0 has a new type of delimited identifiers called bracketed identifiers, which you delimit with square brackets ([ ]), as in the statement

SELECT * FROM [Blanks In Table Name]

Note that you can use quoted identifiers only if you set the SET QUOTED_IDENTIFIER option to ON. By default, the SQL Server Object Linking and Embedding Database (OLE DB) provider and SQL Server Open Database Connectivity (ODBC) driver both SET QUOTED_IDENTIFIER to ON when they connect. DB-Library, however, does not SET QUOTED_IDENTIFIER ON by default. In both cases, individual applications or users can change the setting at any time.

When QUOTED_IDENTIFIER is ON, SQL Server 7.0 follows the SQL-92 rule regarding the use of double and single quotation marks in SQL statements (i.e., you can use double quotation marks only to delimit identifiers). When QUOTED_IDENTIFIER is OFF, SQL Server follows the Transact SQL (T-SQL) rule regarding the use of double and single quotation marks (i.e., you can't delimit identifiers by either single or double quotation marks). The important thing to remember is that any applications using or based on either the SQL Server OLE DB provider or the SQL Server ODBC driver will, by default, automatically have SET QUOTED_IDENTIFIER ON.

Q: Can I set up an application that accesses SQL Server as a Windows NT service?

Yes, you can use the SRVANY.EXE utility that ships with the Microsoft Windows NT 4.0 Resource Kit. Obviously, you don't want to willy nilly transform your favorite applications or utilities into NT services, so be sure you bone up on NT services and security issues associated with running applications as NT services.

Q: Does xp_cmdshell have a security hole?

Xp_cmdshell is a handy tool and perhaps the most popular of SQL Server's extended stored procedures (see our September 1997 column for more information). From the Transact-SQL (T-SQL) query window, xp_cmdhsell lets you issue any command prompt command as though you were sitting at a real command prompt window. We've used this feature for all sorts of tasks, including running bulk copy program (bcp) commands directly from a T-SQL window. However, the command can be too powerful in the wrong hands.

Xp_cmdshell runs in the security context of the account running the MSSQL service, which defaults to LocalSystem. This special LocalSystem account essentially has full Windows NT administrator privileges and is treated as a domain administrator if the local NT machine participates in a domain as Primary Domain Controller (PDC) or Backup Domain Controller (BDC). Running bcp commands sounds harmless, but this security loophole opens many possibilities. If you use the default installation choices, xp_cmdshell lets your SQL Server system administrators run operating system (OS)-level commands as if they were NT administrators. The simplest way to shrink the size of any security hole created by xp_cmdshell is to never install SQL Server on a PDC or a BDC. If SQL Server isn't running on a PDC or a BDC, SQL Server system administrators can still act as NT administrators on the local machine, but they can't gain domain administrator privileges. Also, consider running SQL Server under an account with NT administrator privileges. But be careful--this arrangement could cause subtle connection problems if you're conducting multiserver operations such as running replication between two boxes.

Q: How can I install SQL Server Books Online (BOL) manually?

The Microsoft Knowledge Base article "INF: How to Manually Install SQL Server Books Online" (http://support.microsoft.com/ support/kb/articles/q188/8/83.asp) answers your question for SQL Server 6.5. (SQL Server 7.0 uses a browser-based BOL, so installation is different.) Here are the steps for installing SQL Server 6.5 BOL:

infoview.exe sqlbooks.mvb

Q: Where can I find out the latest about SQL Server and the Year 2000 (Y2K) problem?

SQL Server 7.0 is Y2K-compliant. On its Y2K information page for SQL Server 6.5 (http://www.microsoft.com/technet /topics/year2k/product/SQL65.htm), Microsoft says that with Service Pack 5 (SP5), SQL Server 6.5 will be compliant. By implication, Microsoft won't support Y2K compliance in SQL Server versions earlier than SQL Server 6.5.

Q: What is a quick way to learn the names and sizes of my tables?

Reader Tom Delancey ([email protected]) suggests entering the code in Listing 2. To avoid launching sp_space used, you could execute the code in Listing 3, which directly queries the sysindexes table. Sp_spaceused gets its data from sysindexes, so the answers are basically the same.