Deleting a Library From an SQL Database using OSQL
Technical Note 1599
Last Reviewed 19-Dec-2002
Applies To
Express Software Manager version 6.0 and higher
Summary
If you do not have a full version of Microsoft SQL Server with SQL Enterprise Manager, you can delete an application library from an SQL database using the OSQL utility. This technical note describes how to use the OSQL commands to delete an application library's data from an SQL database.
Deleting Express Application Libraries
Deleting an application library is a multi-step procedure and involves more than simply removing the library data from the reporting database. This Technical Note explains how to use OSQL commands to delete library data and references from the reporting database. In addition, you also need to uninstall any Express clients that are writing to the library, and finally, you need to delete the library files and folders on the network share. For more information about uninstalling Express clients and deleting libraries, see your Express Software documentation.
About the OSQL Utility and OSQL Commands
The OSQL utility comes with every copy of SQL Server, including MSDE. This utility allows you to run scripts, system procedures, and Transact-SQL statements directly from the operating system command prompt.
OSQL commands can include a large number of parameters or switches. To see a list of OSQL command options, enter the following command at the command prompt of the database server (the machine on which SQL Server or MSDE is running):
Many of the switches are case sensitive while some parts of the commands are not. Unless you have specific experience with OSQL, we recommend that you enter the commands exactly as they are presented in this note.
The OSQL utility can be run in interactive mode, or in batch mode. The instructions in this technical note specify a single command line that starts the utility, executes a query, and exits the utility afterward.
For more information about OSQL, see the Microsoft Web site.
Deleting a Library from an SQL Database
Deleting an application library’s data from an SQL database is a two-step process:
- Determine the library ID (libid) of the library you want to delete.
- Use that libid to actually delete the library data.
Determining the library ID
To determine an application library’s libid, enter the following case-sensitive command at the command prompt of the database server (the computer on which the database resides):
osql -U<username> -P<password> -d<database name> -o<output file name> -Q"SELECT libid, name, libpath FROM libs"
This command will return the library ID (libid), library name (name), and library path (libpath) of every library referenced in the database. You will need the library ID for the next step.
Examples
Unless otherwise stated, the below examples assume that you are using the “Express” database with the “sa” account, and that this account has a password of “esm02”. Note: The c:\temp directory must exist (this is where an output file will be written).
Determining a library ID in the Express database
To determine a library ID in the “Express” database, enter this command at the command prompt of the database server:
osql -Usa –Pesm02 –dexpress –oc:\temp\output.txt -Q"SELECT libid, name, libpath FROM libs”
Determining a library ID in the Express database on a remote database server
You can determine library IDs on a remote database server by including a “–S<database server>” option to specify the server name.
To determine a library ID in the “Express” database on a remote database server, enter this case-sensitive command at the command prompt of a computer that has OSQL installed and is able to access the database server (ESMdbSrv):
osql –SESMdbSrv -Usa –Pesm02 –dexpress –oc:\temp\output.txt -Q"SELECT libid, name, libpath FROM libs”
Using the library ID to delete the library
When you have identified the library ID of the library you want to delete (use the library name and path to help determine the correct ID), use the ID in the following command to delete all of that library’s data and references:
osql -d<database name> -U<user name> -P<password> -Q"DELETE FROM libs WHERE libid = <your libid>"
Examples
Unless otherwise stated, the below examples assume that you are using the “Express” database with the “sa” account, and that this account has a password of “esm02”. The examples also assume that the library ID for the library you want to delete is “1”. Note: The c:\temp directory must exist.
Using the library ID to delete the library
To delete a library in the “Express” database, enter this command at the command prompt of the database server:
osql -Usa –Pesm02 –dexpress –oc:\temp\output.txt -Q"DELETE FROM libs WHERE libid = 1”
Using the library ID to delete the library on a remote database server
You can delete a library on a remote database server by including a “–S<database server>” option to specify the server name.
To delete a library in the “Express” database on a remote database server, enter this case-sensitive command at the command prompt of a computer that has OSQL installed and is able to access the database server (ESMdbSrv):
osql –SESMdbSrv -Usa –Pesm02 –dexpress –oc:\temp\output.txt -Q"DELETE FROM libs WHERE libid = 1”
Related Technical Notes
| 1606 |
Maintaining Your SQL Database With OSQL |
| 9989 |
Express Software Manager 6.x Technical Notes |
|
