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 \Mssql\Devtools\Samples\Sqlns\Vb\Dbprop if you've used the installation defaults; d:\devtoolssamples\sqlns\vb\dbprop 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.