Managing SQL Server databases and their devices with ADO

Microsoft's SQL Server is one of the most commonly used database management systems in small networks today. The application of this kind of network database is also increasing, the normal operation of this kind of network database application system, usually depends on the existing user database. The work of creating a database can certainly be done with SQL Server's Enterprise Manager tool, but it is no doubt more desirable for users to provide a custom database management tool that specializes in managing the databases and their devices needed by the application system.

Existing problems

We know that you must have a database device with remaining space before you can create a database using the build data statement, or create a new device using the DISK INIT statement beforehand. However, these statements contain a number of required parameters, and many parameter values are often difficult to determine without using SQL Server's Administrative tools.

Take the DISK INIT statement that created the database device as an example, the complete syntax for this statement is as follows:

DISK INIT
NAME =‘logical_name,
PHYSNAME =‘physical_name,
VDEVNO = virtual_device_number,
SIZE = number_of_2K_blocks
[, VSTART = virtual_address]

The two parameters, name and size, are easy to get, and the problem is the physical name Physname and the virtual device number VDEVNO. The former requires a full pathname of the physical file on a server, and the latter requires a number that is not occupied by another device between the 1~255. When you write a database management program, the device numbers on the user's server are already occupied, and which drive the SQL Server is installed on is unpredictable.

Although using SQL Server's Administrative Tools SQL Enterprise Manager, you can easily create, delete, or expand a database that already exists, or you can easily create, delete, or modify a database, but This tool still requires us to enter a number of less commonly used parameters, the interface slightly more complex.

So the ideal scenario is that the user simply presses a command button, and the database and its devices that the application needs are automatically created immediately.

Solution

In order to achieve this goal, we must find a way to solve the problem of parameter setting in SQL statements.

1. To create a statement parameter for a device

The statement that creates the device is the DISK INIT statement mentioned earlier.

To simplify the problem, we can specify the device file name that is the same as the database name and save the device file in the subdirectory where the master device resides. The database name is determined while the application is being designed, and the subdirectory where the master device resides can be queried from the system table sysdevices. In this way, the physical name parameter of the device file is determined.

The problem with virtual device numbers is more complicated because there is no "virtual device number" in the sysdevices system table, so you have to find another way.

After analyzing the system stored procedure sp_helpdevice for SQL Server, we found that the virtual device number is "hidden" in the low field of the sysdevices system table, and the virtual device number of each device can be found by using another system table spt_values. In this way, we just need to find out in a loop whether a device number exists in sysdevices, and we can determine the virtual device number that we now have available.

As for the size of the database equipment, we might as well set the larger, or let the user specify that it can.

2. To create a statement parameter for a database

The statement to create the database is as follows:

CREATE DATABASE database_name
[ON {DEFAULT | database_device} [= size]
[, database_device [= size]]...]
[LOG ON database_device [= size]
[, database_device [= size]]...]
[FOR LOAD]

Where most of the parameters are optional, we only need to specify a device name and the size of the database, and the database name, device name, size in the creation of the device has been determined, so the parameters of this statement is not a problem.