How do I back up and restore cubes that I created using SQL Server 7.0's OLAP Services?
The initial release of OLAP Services that shipped with SQL Server 7.0 doesn't provide tools to back up and restore cubes or easily move a cube from one machine to another. Users complained about this serious shortcoming, so Microsoft released the following set of add-ins for OLAP Manager:
- The archive and restore databases add-in lets you back up and restore a database that you created using OLAP Services. This tool also lets you move OLAP Services' databases between machines.
- The calculated member manager add-in lets you define calculated members in virtual cubes directly from the OLAP Manager.
- The copy and paste objects add-in lets you copy and paste objects between different databases or cubes in the OLAP Manager treeview. Using this add-in is similar to using SQL Server Enterprise Manager's Generate Scripts option to create a Data Definition Language (DDL) script. This add-in moves your OLAP objects' definition (i.e., meta data), but it doesn't move the data associated with the original objects.
You can download the Add-In Kit for OLAP Manager from http://www. microsoft.com/ sql/70/gen/ olapaddin.htm.
Does the SQL Server 7.0 version of bulk copy program (bcp) let multiple clients simultaneously load data into the same table?
According to SQL Server 7.0 Books Online (BOL), the SQL Server 7.0 version of bcp lets multiple clients simultaneously load data into the same table. We used a quad Data General 3700 AViion server to conduct an informal test, and we determined that BOL is right. We loaded 500,000 rows into a table called BigFact that had no indexes and was empty when we started each test. Parallel bcp let us load the 500,000-row data set almost 40 percent faster when we used the TABLOCK option. Without TABLOCK, parallel bcp was significantly slower than loading data with one bcp client. BOL encourages users to segment the input file into multiple pieces so that each bcp client is using a private file. We found that response time decreased by about 40 percent when we used the -F and -L switches to let two bcp clients simultaneously work on the same input file. Although this method might not be the optimal way to perform parallel bcp, it made segmenting our data easy.
Does the SQL Server 7.0 version of bulk copy program (bcp) let you create an output file from a query without having a VIEW to put the result set in?
If you want to bcp out a query's result set, earlier versions of SQL Server force you to create a VIEW. However, this method is inconvenient, especially for database developers who don't have the administrative rights to create new objects. In SQL Server 7.0, bcp has a QUERYOUT option that extends the traditional in/out bcp direction control. (Remember that bcp is a command-line utility, which means you have to enter bcp commands from the OS-level command prompt rather than from a Transact SQL—T-SQL—based query window, such as the Enterprise Manager's Query Analyzer.) The following example shows how you can bcp out data directly from a query:
bcp "SELECT au Iname FROM pubs..authors" QUERYOUT C:\temp\AuthorsQuery.txt -Usa -P -S <yourservername> -c"
Figure 1, page 172, shows the output file that the previous bcp command generates.
You can also use the QUERYOUT option to bcp out the result set of a stored procedure, as the following example shows. This example bcps the same data as the first example. Run this T-SQL batch from Query Analyzer:
USE tempdb go CREATE proc MyProc as SELECT au_lname FROM pubs..authors go
Now, run the following bcp command from a command prompt:
bcp "exec tempdb..MyProc" QUERYOUT C:\temp\AuthorsQuery.txt -Usa -P -S -c"
The result set of a stored procedure (or any T-SQL query batch) can contain multiple result sets. The following procedure, MyProc2, is a good example of this rule:
PROC MyProc2 as SELECT au_Iname FROM pubs..authors SELECT au_fname FROM pubs..authors go
This procedure contains two result sets, which is legal. In addition, most SQL Server programming environments let you manage both result sets. However, the QUERYOUT option ignores everything except the first result set.
I often use the sp_help command to quickly generate simple reports for table schema definitions. SQL Server 7.0 puts so many blank spaces after each column that the output doesn't fit on the screen and doesn't print well. Can I change the output's format to make it easier to read and print?
We agree that the new default format for the output of many system stored procedures is annoying. Fortunately, you can modify your Current Connection Options to control this behavior. In Query Analyzer, open the Current Connection Options dialog box and select the Advanced tab, which Screen 1 shows.
On this tab, you can change the value in the Maximum characters per column text box to shorten the output of columns in sp_help and other system stored procedures. You can't set this value to less than 30, but 30 is short enough to make the procedure output more readable.
I have a project that developers originally wrote using VBSQL and DB-Library. Over the years, people have introduced COM objects to the project using ADO. Can I access the new COM objects and ADO in the same application that uses DB-Library?
Unfortunately, our experience indicates that VBSQL and ADO are mutually exclusive because VBSQL and named pipe DLLs need to be a matched set. If you break ADO by installing the right named pipe interface for VBSQL, you must make the painful choice between ADO and VBSQL. Microsoft recommends ADO as the way to go.
How do I disable SQL Server 7.0 from logging to the sql.log file?
The correct answer is that you shouldn't disable logging. In fact, you can't disable logging because it's an integral part of SQL Server's backup and recovery mechanism. However, if you never delete log records from the transaction log, the log will keep growing until it fills the available space on the disk that holds the log.
To solve this problem, you can periodically truncate the log file by using, for example, the Maintenance Wizard. Truncating the log is the process of deleting log records that you no longer need to recover or restore a database. (For more information about truncation, see "Truncating the Transaction Log" in SQL Server 7.0 Books Online—BOL.)
However, you can never truncate the active portion of the transaction log because SQL Server needs the active portion to recover a database at any time. You can tell where the active portion of the log begins by identifying the minimum recovery log sequence number (MinLSN).
