Answers from Microsoft - 16 May 2000

How can I reactivate a database that SQL Server has put in suspect status?

You can try executing the sp_resetstatus stored procedure, but this action is unlikely to work. The sp_resetstatus procedure tells SQL Server only that the database is probably healthy. The next time you try to bring the database online, SQL Server will try to recover the database to a consistent state. If it can't do so, it will reset the database's status to suspect. You'll probably need to restore the database from a good backup.

Microsoft states that querying system tables isn't a good idea, but what are some possible consequences of doing so? We have a middle-tier application that runs a SELECT statement against the sysprocesses table. What effect could this query have on the database's performance?

Querying the sysprocesses table shouldn't cause artificial slowdowns although the table is heavily updated. (Also, sysprocesses isn't really a table in the traditional sense—it just looks like one in memory.) The problem is that Microsoft reserves the right to change the structure of system tables, even between service packs, so depending directly on a system table for certain data could cause your application to quit functioning after you apply a service pack or upgrade to a new release. Microsoft doesn't change the tables often, but when it needs to change a system table, backward compatibility isn't a major consideration. Therefore, Microsoft emphasizes that you shouldn't directly access the system tables. (For more information about system tables, see Kalen Delaney, Inside SQL Server, "System Tables," February 2000, and "Accessing System Tables," March 2000.)

I want to set up a batch account in database1 so that the account has execute permissions on xp_cmdshell. How do I set up the SQLAgentCmdExec account for non-system administrators with the correct permissions to run xp_cmdshell?

The SQLAgentCmdExec account should already be set up—it is installed when you install SQL Server. You simply need to grant access to xp_cmdshell to the user. However, xp_cmdshell resides in the Master database, so you need to create a database username in Master for the user, then grant execute permission on xp_cmdshell to that username.

How do I use Data Transformation Services (DTS) to convert Access text data from mixed capitalization to SQL Server table data with all capital letters?

First, in the Transform Data Task source, use a SQL query such as

SELECT upper(col1) ucol1, upper(col2) ucol2...

Next, using the table as the source, on the task's Transformations tab, specify a many-to-many transformation for all columns of the type ActiveX Script. Then edit the script from

DTSDestination("Col1") = DTSSource("Col1")DTSDestination("Col2") = DTSSource("Col2")

to

DTSDestination("Col1") =    UCase(DTSSource("Col1"))DTSDestination("Col2") =    UCase(DTSSource("Col2"))

Or, you can use SQL Server 2000's new DTS UpperCase String Transformation.

My company is looking for a failover solution for its crucial sites. I need redundancy for both server and database, so I'm setting up a primary server as my main node and a secondary server only for failover. The servers house identical databases. First, how can I keep the databases synchronized, and in the event of drive failure, how can I ensure failover to the secondary node? Second, after restoring the primary node, how do I resynchronize the databases and make a smooth transition back to the primary node? Replication isn't an option because the company might add many new tables to the database daily. And I can't use server clustering because it doesn't support redundancy for the external device where the database resides.

Microsoft's first recommendation for high availability and quick recovery is failover clustering, unless this solution simply doesn't work for you. Failover clustering can provide a second copy of the disks if you're using a storage solution from EMC or Compaq, but both brands are expensive. As an alternative, you could use SQL Server log shipping, which is in the Microsoft BackOffice 4.5 Resource Kit and is part of SQL Server 2000. Log shipping backs up your primary database and restores it on a secondary server or servers. You can continue backing up the transaction log from the primary server and applying the log to the secondary servers. To partially automate switching between the two servers, you can use Windows 2000 Network Load Balancing (formerly Network Load Balancing Services). For details about this feature, see the Microsoft article "Network Load Balancing Technical Overview" (http://www.microsoft.com/windows2000/library/howitworks/cluster/nlb.asp).

Within a stored procedure, how can I copy a text field in one table to an ntext field in another table in the same database?

Listing 1 contains an INSERT SELECT statement that will do the trick. You can easily wrap this code in a stored procedure.

My application has an option that lets users create a query by selecting tables, defining the join, and specifying the WHERE clause. I needed to populate the user tables with the user data, so I queried the system table and selected the user tables. I also use this method to select other SQL Server objects, such as stored procedures and views. Besides letting users select the tables and views, I need to populate corresponding columns. The INFORMATION_SCHEMA table doesn't query the stored procedures, so I can't use it to populate the columns. How can I populate the user-type tables and corresponding columns?

This behavior was an oversight in SQL Server 7.0, and no fix exists until you migrate to SQL Server 2000; you must query the system tables directly for the information. SQL Server 2000 has a new INFORMATION_SCHEMA table, which returns information about stored procedures. So in SQL Server 2000, you can use the INFORMATION_SCHEMA table to populate tables. (For more information about this feature, see Kalen Delaney, Inside SQL Server, "Property Functions, Schema Views," April 2000.)

The Data Transformation Services (DTS) designer doesn't let you change the database or table in a Data- PumpTask without losing the transformations. I wrote a short Visual Basic (VB) application that lets you change elements such as server, username, password, table name, and file path when you migrate DTS packages from developer to client sites. However, when you load the DTS package back into SQL Server, the package loses the formatting and text comments. I know the formatting and text information is still in the original package file because the formatting is fine when I reload it into the SQL Server designer. I believe this formatting information is in DTS through the parameter pVarPersistStgOfHost in both the LoadFromStorageFile and SaveToStorageFile steps in a DTS package. However, the parameter value won't populate. How can I change the parameter value in VB when I migrate DTS packages from development to client sites?

The Microsoft DTS program manager says pVarPersistStgOfHost is a stream of the layout from Da Vinci (the graphical database viewer you see in Access and SQL Server Enterprise Manager). When you edit the package in an application other than the DTS designer, the variable resets to the default (nil), forcing the layout manager to use the default layout. Microsoft plans to change this behavior in a future release of SQL Server, but no fix is available at this time.