Can I change where the SQL Server install procedure installs SQL Server tools? SQL Server 2000 install needs a large amount of space on my system drive in which to install the tools. But because space is limited on that drive, I'd prefer to install these files on another partition.
Microsoft doesn't offer a supported way to change where SQL Server installs its tools, but an unsupported solution has been floating around for a while. This solution comes with a serious warning: You'll be editing important registry keys and could easily render your system unusable if you make a mistake. Be very careful, and ensure that you have good backups before going forward.
SQL Server 2000 installs tools by default in Program Files\Microsoft\SQL Server\80\Tools on the partition on which Windows is installed. You have no option to change this directory. SQL Server install simply reads the directory location from the ProgramFilesDir registry subkey located at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion. SQL Server 2000 install will use the directory location you have in this registry subkey, which means you can change the installation location by changing the directory location. Here are the steps for changing the registry subkey to install SQL Server tools in a different location:
- Back up the registry and your system.
- Change the subkey to the new directory path in which you want to install the SQL Server tools.
- Install SQL Server.
- Do not reboot the machine after installing SQL Server.
- Change the registry subkey back to its original value.
- Reboot the machine.
Absolutely, positively make sure that you set the registry subkey back to its original location before you reboot. Otherwise, severe and unwanted side effects might occur. Many Windows system functions and other applications use this subkey's value. So, for example, Windows 2000 will apply the Win2K System File Protection (SFP) functions to the new directory you specified if you reboot before changing the registry subkey back to its original value.
Can I connect to SQL Server 7.0 using SQL Server 2000 Enterprise Manager across a TCP/IP WAN? I've tried, but I keep getting a timeout expired message. The remote server is running, and I'm sure that I've entered the registration information correctly in Enterprise Manager.
Yes, you can connect to SQL Server 7.0 servers using SQL Server 2000 tools, and connecting across a WAN doesn't make a difference as long as a firewall isn't in the way. However, the WAN link might be slow, causing Enterprise Manager to simply time out when it tries to log in. You can change the login timeout setting from Enterprise Manager by clicking Options from the Tools menu, clicking the Advanced tab, then changing the login timeout setting.
I'm issuing a T-SQL RESTORE DATABASE command through Visual Basic (VB) 6.0. However, the query is asynchronous, so it moves on to the next line in my VB code before the RESTORE DATABASE command has finished executing. Subsequent SQL statements then fail because they're trying to access a database that's unavailable. How can I find out when the restore is complete so that the VB application can issue the subsequent SQL statements against the database?
You can check the restore's status by using SQL Server 2000's DATABASEPROPERTYEX() function or SQL Server 7.0's DATABASEPROPERTY() function. Issuing the following SQL Server 2000 command:
SELECT DATABASEPROPERTYEX('Northwind', 'Status')
will return one of the following results:
- ONLINE = database is available for query
- OFFLINE = database was explicitly taken offline
- RESTORING = database is being restored
- RECOVERING = database is recovering and not yet ready for queries
- SUSPECT = database can't be recovered
Issuing the following SQL Server 7.0 command:
SELECT DATABASEPROPERTY('Northwind', 'IsInRecovery')
will return either a value of 1, which means the database is still being recovered, or a value of 0, which means that the database is available.
I don't understand how Enterprise Manager reports a table's column sizes. For example, I created a table called HowBigAmI with a single column of type nvarchar(10), then issued sp_help HowBigAmI to see what SQL Server would report as the column size. The result said the column had a length of 20. But I defined the column as varchar(10), and Enterprise Manager tells me the length is 10. Why is SQL Server reporting different lengths?
Run the following script:
CREATE TABLE HowBigAmI (Col1 nvarchar(10))
GO
sp_help HowBigAmI
GO
and you'll see that sp_help indeed shows the column size as 20, but Enterprise Manager's design view for the table shows the size as 10. SQL Server Books Online (BOL) says that Enterprise Manager shows column size as number of bytes, but that isn't the case. Enterprise Manager shows the number of characters that can fit in the column. The example above defines the Col1 column as Unicode (that's what the n in front of varchar means), which is a two-byte character set. So, Col1 can accept 10 Unicode characters, but each character consumes 2 bytes. Thus, you have Enterprise Manager reporting the column size as 10 and sp_help reporting the column size as 20. I agree that this behavior is confusing. But in reality, DBAs need both pieces of information. We need to know that we're limited to only 10 characters when storing data in the column, and we also need to know that the column consumes 20 bytes.