| Downloads |
|---|
| 15580.zip |
Editor's Note:Send your SQL Server questions to Richard Waymire, program manager for SQL Server development at Microsoft, at [email protected].
How do you install the client utilities on the second node of a SQL Server 7.0 machine in a cluster configuration? After you install Microsoft Cluster Server (MSCS) and SQL Server and you run the wizard to create your virtual SQL Server, you have a SQL Server implementation that you can switch from one Windows NT server to the other. The problem is that the client utilities, such as Microsoft Management Console (MMC) and the Query Analyzer, are installed only on the primary node. The installation program won't let you install these utilities on the second node because according to Microsoft, you might overwrite .dll files and other executables. Microsoft says that you have to uncluster the SQL Server machine, install the utilities on the second node, then cluster the servers again. This is a fairly radical and error-prone solution. Is there another way to do it?
After you've clustered a SQL Server 7.0 machine, you must uncluster it before you can install the tools on the second node. Yes, it's a nasty procedure. And yes, we've fixed this problem in SQL Server 2000, which installs the tools locally on all clustered nodes. We've also updated the clustering white paper "How to Install SQL Server 7.0 Enterprise Editions on Microsoft Cluster Server" at http://www.microsoft.com/sql/ to address tool installation. Note that SQL Server 7.0 installs the tools on the cluster disk, so the tools fail over when SQL Server fails over. However, the shortcuts don't fail over, so you need to navigate to the \mssql7\binn directory and run the .exe file to restore the shortcuts.
Suppose that you want to regularly run capacity and performance reviews on your server so you can predict when you'll need to add more memory. How do you measure the memory that SQL Server consumes?
Use the Performance Monitor counters to monitor how much total memory SQL Server used. Even if you manually set SQL Server to use all the available memory, SQL Server doesn't automatically take the memory until it needs it. So, the figure that the Performance Monitor and the Task Manager show is accurate.
After my trial version of SQL Server 7.0 expired recently, I installed a licensed version of SQL Server 7.0. How can I move my databases from the trial version to the licensed version?
If you executed a clean shutdown of the trial version before it expired, you can simply attach your databases. For example, to reattach the Pubs database, you can execute the following statement in Query Analyzer:
Exec sp_attach 'pubs', 'c:\mssql7\data\pubs.mdf', 'c:\mssql7\data\pubs_log.ldf'
For details about attaching databases, look up sp_attach_db in SQL Server Books Online (BOL). If you didn't execute a clean shutdown, you won't be able to move the databases from the trial version.
In SQL Server 7.0, the statement
select isnumeric('123 ')
returns a value of 1, and the statement
select cast('123 ' as numeric)
returns the message Error converting data type varchar to numeric. Note that the space after the 123 in both queries is the tab character. Is this message a bug in SQL Server 7.0?
We designed SQL Server to ignore spaces but not tabs. To make your second statement work, use the statement
select cast((replace('123 ',' ',' ')) as numeric)
where the middle string is the tab character.
My company is setting up a disaster recovery site, where we'll install SQL Server on a new box. The Windows NT administrators want to use the SQL Server box as a primary domain controller (PDC). I've heard that this setup isn't a good idea because of the resource-intensive tasks of maintaining and replicating the network accounts database and performing network logon authentication. Can the SQL Server box serve as a backup domain controller (BDC) instead? Is it technically possible to set up SQL Server as a PDC or BDC?
SQL Server works fine when you install it on either a PDC or BDC. To decide whether one of these configurations is right for your site, consider your resources.
If you have enough network bandwidth, memory, and spare processors, using the SQL Server machine as a domain controller will work. But the processing could be a lot of work, depending on the size of your domain. If you expect SQL Server to be busy, don't run any other service with it.
When I set up a linked-server connection between two servers, I get the error message Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. I set ANSI_NULLS and ANSI_WARNINGS on in Query Analyzer. I'm running a script that uses the xp_cmdshell extended stored procedure with the argument
'ISQL-U -P -S\{ServerB\} -Q\{stored procedure\}'
This stored procedure performs a linked-server query from Server B to Server A. Why do I receive the error message?
The problem is that you're using isql.exe instead of the ODBC-based osql.exe. Using osql.exe will correct the problem.
The following query works fine:
SELECT
*
FROM
OPENROWSET
(
'SQLOLEDB'
, 'DRIVER=\{SQL
Server\};SERVER=(local);Trusted_Connection=yes'
, 'SET FMTONLY OFF EXEC sp_who2'
)
However, the following query doesn't work:
DECLARE
@STR varchar (2000)
SET
@STR = 'DRIVER=\{SQL
Server\};SERVER=(local);Trusted_Connection=yes'
SELECT
*
FROM
OPENROWSET
(
'SQLOLEDB'
, @STR
, 'SET FMTONLY OFF EXEC sp_who2'
)
Why doesn't the second query, which uses a string, work?
Usually, SQL Server doesn't allow passing parameters to OpenRowset (or OpenQuery) because SQL Server needs all the information you supply inside OpenRowset to get the meta data of the rowset and compile and optimize the query. If you use variables, SQL Server can get the values only at execution time.
You could use variables and achieve this dynamic behavior by constructing the entire query string in a variable and doing an EXEC (@str) statement. Listing 1 shows the code for the EXEC statement.
I tried to back up my databases to a file on a network share and restore the databases on the secondary server. I tried to use the Net Use command to create the share on the target server, as in
NET USE * \\PS5\C /USER:ONEDEV\\{domain id\}
\{password\} /PERSISTENT:NO
But I keep getting the message There are no available drive letters left. Enterprise Manager shows many available drives. How can I fix this problem?
Just back up your databases directly to the Uniform Naming Convention (UNC) path. SQL Server doesn't see mapped drive letters the same way a user does. You need to grant the SQL Server service account full control permissions on the share \\PS5\C, make a new share, or make the SQL Server service account a member of the administrators group on machine PS5.
