Answers from Microsoft - 24 Jan 2001

Editor's Note: Send your SQL Server questions to Richard Waymire, program manager for SQL Server development at Microsoft, at questions@sqlmag.com.

Which SQL Server releases run on Windows 2000 Server? If SQL Server 6.5 Service Pack 5a (SP5a) does, what is the installation process?

SQL Server 2000, 7.0, and 6.5 run on Win2K Server. To install SQL Server 6.5, run setup as usual, then immediately apply SP5a. For information about a side effect of installing SQL Server 6.5 on Win2K Server and a solution to the problem, see the Microsoft article "Bug: Install of SQL Server 6.5 on Windows 2000 Modifies DTC Service" at http://support.microsoft.com/support/kb/articles/q249/3/10.asp.

Since I upgraded to SQL Server 7.0, I have encountered a problem in SQL Server Profiler: I am unable to capture SQL statements that have been parameterized. Instead of the parameter @P1, I want the actual value that was used in the query execution. How can I place the substitution without referencing other lines in the trace?

You have to retrieve other parts of the trace, such as the results from SP:StmtCompleted, and put them together, statement first, to capture the full query. You're seeing the result of SQL Server Profiler's method of recovering data from the SQL Server relational engine. We have the same concern in SQL Server 2000. However, we can't make a change at this time because the place where we generate this event doesn't know the values of the parameters inside the engine.

We want to replicate data from our mainframe DB2 database to our SQL Server 7.0 database. Before migrating to SQL Server 7.0, we unloaded the data from the DB2 tables, used FTP to pull the data down to the SQL Server machine, then loaded the data with the bulk copy program (bcp). After we migrated to SQL Server 7.0, we expected to be able to save time by moving the batch files that we currently use with Data Transformation Services (DTS). However, we've been able to move only an incomplete set of data that way. We've connected the SQL Server machine to DB2 using DB2 Connect Enterprise Edition, but when we try to bring decimal values from the mainframe database to the SQL Server database, we receive an error message saying that we're trying to store too much data in the SQL Server column. However, if we bring down a table with no decimal values, the data comes through DB2 Connect successfully.

In trying another avenue to solve the problem, we succeeded in using Microsoft Access to link SQL Server to the DB2 tables through DB2 Connect. We can also use DTS to bring complete data into SQL Server, but transferring the data this way takes 10 times longer than using FTP to bring the file down, load the data into the bcp, then pour the data into the tables. How can we get DTS to bring down the data from DB2 on our mainframe?

I talked to the SQL Server DTS program manager, and we agree that you should stick with your FTP scheme. However, he also noted that SQL Server 2000 has a DTS custom task for FTP. For information about using custom tasks in SQL Server 7.0, see Don Awalt and Brian Lawton, "Constructing DTS Custom Tasks," September 1999. When you use DTS as a batch environment by calling bcp or bulk insert from DTS, you can choose the transformation option. If you don't need transformation, a bulk insert or bcp operation will always be faster.

I'm trying to set up a Data Transformation Services (DTS) package that will copy all the objects and data to our backup disaster recovery server every night, but I keep getting error messages. Should I use DTS to transfer all objects and data? I tried replication, but that process transfers only data and stored procedures. What is the best way to accomplish the backup?

Execute a detach/file copy/attach command by using sp_detach_db, executing a file copy, then executing sp_attach_db for SQL Server 7.0. In SQL Server 2000, you can use the Copy Database Wizard to execute the detach/file copy/attach command.

I need to collect statistics such as transactions per second (tps) on my SQL Server machines for periodic reporting. I've used the Performance Monitor extensively, but I'm unable to produce a set of SQL Server performance numbers that are meaningful to DBAs. Are any utilities available to easily gather these statistics?

One of the Performance Monitor counters for SQL Server collects tps, so using the Performance Monitor is the easiest way to capture this counter. You might consider installing Performance Monitor as a service using the Microsoft Windows NT Server 4.0 Resource Kit or Microsoft Windows 2000 Server Resource Kit as well. I also recommend Steve Adrien DeLuca et al., Microsoft SQL Server 7.0 Performance Tuning Technical Reference (Microsoft Press, 2000) to learn about measuring meaningful performance data with SQL Server.

In SQL Server 6.5, to replicate database 1 from server 1 to database 2 on server 2, I currently modify table 1 in database 1, for which I add only two new columns. How can I synchronize this change for table 2 in database 2 on server 2?

Replicating column changes is a feature of SQL Server 2000. In SQL Server 6.5, you'll have to stop replicating data, modify the remote table to match the source, then re-enable the replication.

I just upgraded a copy of our production database from SQL Server 6.5 to 7.0. Most objects upgraded fine except for the primary keys on 60 of our 120 tables. After the upgrade, the primary keys of those 60 tables had new names. The rest retained the primary key names from SQL Server 6.5. What happened?

The SQL Server 6.5 system probably created the old names, so SQL Server 7.0 is giving you new system-created names for the 7.0 primary keys as well. Unless you specifically named the primary keys when you created them, you're observing the correct SQL Server 7.0 behavior.

Why does my Data Transformation Services (DTS) replication package run successfully as a standalone package, whereas the SQL Server Agent can't run the same job successfully?

A The problem is with the security permissions. When you test DTS in the DTS Designer as a standalone package, you're logged in as yourself, and the security works. When you test from the SQL Server Agent, the DTS package runs as the service account of the SQL Server Agent service. So make sure the service account for the SQL Server Agent service has the permissions you need to perform this operation. The easiest way to find out whether the service account has the permissions is to log in to the computer using that account and see whether the account works.

How can I flush the data cache from Query Analyzer to test index performance?

The best and surest method of flushing the data cache is to stop and restart SQL Server. You can also use DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, but SQL Server Books Online (BOL) doesn't fully explain the implications of each command.

What is the accepted industry approach to controlling SQL Server stored procedures the way that Microsoft Visual SourceSafe controls them? Does SQL Server have tools to do this, or can you recommend third-party tools? Also, can you recommend a way to schedule the regular scripting of all objects to a file to check periodically for unexpected differences between production and development databases?

I'm not aware of any tools to control stored procedures, but Microsoft is considering integrating such tools in a future release of SQL Server. In the meantime, you can schedule scripting with SQL Distributed Management Objects (SQL-DMO) and VBScript, then schedule the job with SQL Server Agent. The SQL Server product includes samples for SQL-DMO.

When I launch Enterprise Manager for SQL Server 7.0 and expand the group of registered servers, a window is supposed to show the status of the server (such as started or stopped) without connecting to the server itself. Unfortunately, I haven't found a way to implement this functionality. I'm using the registered servers collection and the SQL Server collections that SQL Distributed Management Objects (SQL-DMO) provides. The registered server class doesn't have a status property, and the Status property of the SQL Server COM class appears only after I connect to the server. How can I determine the status of the SQL Server machine without connecting or using SQL-DMO? And why is there a delay between the time Enterprise Manager displays the registered servers and the time that it displays the servers' status? Are multiple threads at work here?

You must use the Win32 services APIs if you want to detect the status of SQL Server services without connecting to SQL Server. To answer your second question: Yes, more than one activity is going on here. SQL-DMO calls the Win32 API to check on the service status. You can find the API in the Win32 software development kit (SDK), or you can search the Microsoft Developer Network (MSDN) site. The delay is caused by SQL Server calling the Windows API and waiting for the API to respond. For more information about using SQL-DMO to find registered SQL Server systems on your computer, see Jason Fisher, "SQL-DMO Picks Up Where Enterprise Manager Leaves Off," page 62.