Questions, Answers, and Tips About SQL Server - 10 Dec 1999

Can I run a simple script to manage a SQL Server 6.5 system from SQL Server 7.0's Enterprise Manager?

You can't manage a SQL Server 6.5 system from SQL Server 7.0's Enterprise Manager, but you can run SQL Server 6.5's Enterprise Manager and SQL Server 7.0's Enterprise Manager on the same machine. The best way to make this setup work is to install SQL Server 6.5 first, run Enterprise Manager, register the server, then install SQL Server 7.0 tools on the SQL Server 6.5 system. Any servers you register in SQL Server 6.5's Enterprise Manager will appear in SQL Server 7.0's Enterprise Manager. If you select one of the SQL Server 6.5 servers from SQL Server 7.0's Enterprise Manager, the SQL Server 6.5 Enterprise Manager automatically launches so that you can simultaneously manage both SQL Server 6.5 and SQL Server 7.0 servers from the same machine.

Does the ODBC driver that ships with SQL Server 7.0 break Data Access Objects (DAOs)?

When the ODBC 3.70 driver that Microsoft ships with SQL Server 7.0 connects to SQL Server 7.0, the driver exposes the new globally unique ID (GUID) and Unicode data types. As a result, DAO 3.x might not work with SQL Server 7.0 because Microsoft developed DAO before SQL Server supported those data types.

If you're using an ODBC 3.70 or later driver to connect to a SQL Server 7.0 system running applications you developed with an earlier version of ODBC (i.e., versions 2.x or 3.x), use SQL Server 7.0's odbccmpt utility to enable SQL Server 6.5 ODBC compatibility with DAO applications. You can set the SQL Server 6.5 ODBC-compatibility option only at the executable-file level and not for individual DLLs. In addition, running the odbccmpt utility creates or removes a Registry key. For more information about this utility, read SQL Server 7.0 Books Online (BOL).

Are the new sp_attach_db commands related to Microsoft Access' attach functionality?

No, Access' attach functionality, which is called linking in Access 2000, lets Access serve as a front end to other databases' data as though it were Access data. However, the linked data remains in the native SQL Server or Oracle database. In SQL Server 7.0, the sp_attach_db commands are powerful new features for moving databases between servers.

Sp_attach_db, sp_attach_single_file_db, and sp_detach_db make the process of moving databases between servers much smoother than it is in SQL Server 6.5. These new commands let you detach a database from a SQL Server system, then attach the database on a remote SQL Server system. While you detach the file from the original SQL Server 7.0 system, the database is nothing more than a series of Windows NT files. Thus, you don't need to have the source and target servers synchronously connected. In addition, you can email, FTP, telnet, or move the files from one location to another using the method you prefer. For more information, read about the sp_attach_db commands in SQL Server 7.0 Books Online (BOL).

The Database Consistency Checker (DBCC) MEMUSAGE command returns only the top 20 procedures in the procedure cache. How do I list all the procedures that are currently in the procedure cache?

In SQL Server 7.0, syscacheobjects is a new system table that contains information about how SQL Server is using the cache and what objects are currently cached. You can read the details about the syscacheobjects table's attributes in SQL Server 7.0 Books Online (BOL). The following query is an example script that shows you a list of all the procedures loaded in the cache:

SELECT *FROM   master..syscacheobjectsWHERE   dbid NOT IN (1, 4)

To get an idea of the objects that SQL Server has cached, glance at the list at the top of this command's result set. To shorten the result set to the specific information we were looking for, we eliminated procedures in master (dbid = 1) and msdb (dbid = 4) from the result set. To limit the results to real stored procedures (sps), experiment with other filters such as WHERE objtype = "Proc".

Can you put a query's output directly into a text file from SQL Server Query Analyzer?

No way exists to directly pipe a query's output from Query Analyzer into a text file.

I've read about new features in SQL Server 7.5 (code-named Shiloh), but I can't find any information about what to expect in OLAP Services 7.5. Can you shed some light?

Table 1 outlines Microsoft's enhancements to OLAP Services 7.5.

A character column in my development database contains numeric data with a decimal point (e.g., 2345.65). I need to remove the decimal point from all entries in this column and move the numbers together (e.g., 2345.65 becomes 234565). I researched the SUBSTRING and concatenation commands, but I'm not sure how to remove the decimal point. Do you have any ideas?

You have the right idea. You can use a script similar to the following example:

CREATE TABLE NumberString (KillTheDecimal varchar(10))INSERT INTO NumberString VALUES ("123.45")SELECT KillTheDecimal FROM NumberStringSELECT REPLACE(KillTheDecimal, '.', '') FROM NumberString

The first SELECT FROM command shows the decimal point, and the second SELECT FROM command removes the decimal point.