Using OSQL Tools Manage SQL Server Desktop Engine (MSDE 2000) article ID: 325003 Last update Date: August 13, 2004 Version: 1.0 Content of this Task • Overview • What is OSQL? • How to use OSQL? • Interactive input Transact-SQL statement • Submit an OSQL job • Connect to the SQL Server desktop engine (MSDE 2000) • Manage MSDE 2000 • New login account • Access Database • Change login password • Create a database • Backup and restore database • Addition and separation Database • Reference
This page
Summary "SQL Server Desktop Engine" (also called MSDE 2000) does not have its own user interface because it is mainly designed to run in the background. The user interacts with it through the MSDE 2000 embedded. The only tool provided with MSDE 2000 is OSQL. The executable file SQL.EXE is in the MSSQL / BINN folder of the default instance of MSDE 2000. This article focuses on how to manage MSDE 2000 by using OSQL tools.
What is OSQL? The OSQL tool is a Microsoft Windows 32 command prompt tool that you can use to run Transact-SQL statements and script files. OSQL Tool communicates with server communication using the ODBC Database Application Programming Interface (API).
How to use OSQL? In general, an OSQL tool can be used in this way: • The user enters the Transact-SQL statement by interacting with the way of using the command prompt. • The user submits the OSQL job, the method is: • Specify a single TRANSACT-SQL statement to run. - or -
• Point this tool to a script file that contains the Transact-SQL statement you want to run. Interactive Input Transact-SQL statement To display the case list of case-sensitive cases to display the OSQL tool, type the following under the command prompt, then press ENTER:
OSQL -?
For more information on each option of the OSQL tool, see the "OSQL Utility" topic in SQL Server Online Book.
To interact with the Transact-SQL statement, follow these steps:
1. Confirm that MSDE 2000 is running. 2. Connect to MSDE 2000 (see the title "Connect to the SQL Server Desktop Engine (MSDE 2000)"). 3. On the OSQL command prompt, type the Transact-SQL statement and press ENTER. When you press ENTER, OSQL will cache the statement on the command line when you press ENTER.
• To run the current cache, type "Go", then press ENTER.
• To run a batch of Transact-SQL statements, enter each transact-sql command on a separate line. Then, type "Go" on the last row to indicate the end of the batch command and run the current cache.
The run results appear at the console window.
4. When you press Enter after you enter each line, if you want to quit from OSQL, type Quit or Exit, and press Enter.
Submit an OSQL job In general, you can submit an OSQL job in one of two ways. You can: • Specify a single Transact-SQL statement.
- or -
• Point this tool to a script file. Each method will be described in detail below.
Specify a single Transact-SQL statement
To run Transact-SQL for the local default instance of MSDE 2000, type a similar command with the following: OSQL-E -Q "Transact-Sql Statement"
among them
• -e means using Microsoft Windows NT authentication.
-and-
• -q means running the Transact-SQL statement, but does not exit OSQL at the end of the query. To run the Transact-SQL statement and exit OSQL, use the -q parameter instead of -Q.
Point this tool to a script file
To point to a script file, follow these steps: 1. Create a script file containing a group of Transact-SQL statements (such as MyQueries.SQL). 2. Open a command prompt, type a command similar to the following, then press Enter:
OSQL-E-I Input_File
among them
INPUT_FILE is a script file and its full path. For example, if the script file MyQueries.sql is in the C: / Queries folder, replace the parameter input_file to c: /queries/myqueries.sql.
The running result of the script file will appear in the console window. If you want to direct the run results to a file, add the -ooutput_file parameter to the above command. E.g:
OSQL-E-I Input_File -o Output_File
among them
Output_file is the output file and its full path.
To eliminate the number and prompt symbols in the output result, add the -n option to the above command. E.g:
OSQL-E-II Input_file -o output_file -n Connect to the SQL Server Desktop Engine (MSDE 2000) To connect to MSDE 2000, follow these steps: 1. Confirm that MSDE 2000 is running. 2. Open a command window on a computer that carries the MSDE 2000 instance you want to connect. 3. Type the command below, press ENTER:
Osql -e
This can be connected to the local default instance of MSDE 2000 by using Windows authentication.
To connect to a naming instance of MSDE 2000, type:
OSQL-E -S Servername / InstanceName
If you receive the following error message, it indicates that the MSDE 2000 may not run, or you may have an error name for the installed MSDE 2000 named instance: [Shared Memory] SQL Server Does Not Exist or Access Denied. [Shared Memory ] ConnectionOpen (Connect ()). If you successfully connect to the server, the following prompt appears:
1> This prompt indicates that OSQL has started. Now, you can enter the Transact-SQL statement to interact, and the results will appear on the command prompt line. Managing MSDE 2000 This section below will briefly introduce you the most commonly used Transact-SQL commands when managing MSDE 2000.
Users who have not provided a valid login ID cannot be connected to SQL Server. The sp_grantlogin stored procedure can be called to authorize a Microsoft Windows network account (a group or a user account) so as a SQL Server login account that connects to the SQL Server instance as a Windows authentication. The following example allows a Windows NT user named Corporate / Test to connect to SQL Server instance: Exec sp_grantlogin 'corporate / test' only members of the SYSADMIN or SecurityAdmin fixed server role can run the sp_grantlogin stored procedure. For more information on these roles, see "Roles, SQL Server Architecture" topics in SQL Server Online Books. For more information on the sp_grantlogin stored procedure, see "SP_Grantlogin, Transact-SQL Reference" topic in SQL Server Online Books.
You can create a new login account that creates a SQL Server connection using SQL Server authentication using the sp_addlogin store. The following example creates a password "Hello" SQL Server Login for a user called "test": EXEC SP_ADDLOGIN 'TEST', 'Hello' only Sysadmin and SecurityAdmin fixed server roles can run the sp_addlogin stored procedure. For more information on the sp_addlogin stored procedure, see "SP_ADDLOGIN, Transact-SQL Reference" topic in SQL Server Online Books.
After the access database is connected to an instance of the SQL Server, they can only perform activities in the database after DBO granted them to the database. You can use the sp_grantdbaccess stored procedure to add a secure account to the current database to the current database. The following example adds an account to the current database for a user named corporate / bobj, named "bob": exec sp_grantdbaccess 'corporate / bobj', 'bob'
The sp_adduser stored procedure performs the same function as the sp_grantdbaccess stored procedure. Since the SP_ADDUSER stored procedure is to be backward compatible, Microsoft recommends you use the sp_grantdbacess stored procedure.
Only Sysadmin fixed server roles, members of DB_ACCESSADMIN and DB_OWNER fixed database roles can run the sp_grantdbaccess stored procedure. For more information on the sp_grantdbaccess stored procedure, see "SP_GRANTDBACCESS, TRANSACT-SQL Reference" topic in SQL Server Online Book.
How to change the login password To modify the login password, use the sp_password stored procedure. The following example change the password "TEST" from "OK" to "Hello": exec sp_password 'ok', 'hello', 'test'
Execution Permissions By default a public role of users who are changing their own login passwords. Only the sysadmin role can change the login password for other users. For more information on the sp_password stored procedure, see "SP_Password, Transact-SQL Reference" topic in SQL Server online book. Creating a database MSDE 2000 database consists of a collection of tables, including data and other objects, such as views, indexes, stored procedures, and event triggers, which define all kinds of activities that support data execution. To create an MSDE 2000 database, use the "Create Database" Transact-SQL command. For more information on creating a database, see the "Creating A Database" topic in SQL Server Online Book. The following example creates a database called TEST. Since there is no additional parameters to this command line, the TEST database will be the same as the Model database: CREATE DATABASE TEST CREATE DATABASE Permissions A member of the Sysadmin and Dbcreator Fixed Server Role by default. For more information on the "CREATE DATABASE" command, see "Create Database, Transact-SQL Reference" topic in SQL Server Online Book.
To create a new database object, use the "CREATE Transact-SQL" command. For example, to create a table, use the "CREATE TABLE" Transact-SQL command. For more information, please refer to "SQL Server Online Book".
Backup and restore database SQL Server's backup and restore components provide an important protection to protect key data stored in the SQL Server database.
With proper planning, you can recover from many faults, including: • Storage media failure. • User error. • The permanent loss of the server. In addition, the backup and restore databases There are other purposes, such as copying the database from one server to another. By backing up a database from a computer and restoring this database to another computer, you can quickly and easily create a copy of the database.
For more information on database backups and restore operations, see the "Backing Up and Restoring Databases" topic in SQL Server Online Book.
The following example performs a full database backup for a database named mydb, name this backup MYDB.BAK, and then store this backup in the C: / MSDE / Backup folder.
Backup Database MyDB to Disk = 'c: /msde/backup/mydb.bak'
The following example is a log backup for a database named mydb, name this backup MYDB_LOG.BAK, and then store it in the C: / MSDE / Backup folder:
Backup log mydb to disk = 'c: /msde/backup/mydb_log.bak'
BACKUP DATABASE and Backup LOG Permissions Grant the sysadmin fixed server role and members of the DB_OWNER and DB_BACKUPOPERATOR fixed database roles. For more information on Backup statements, see "Backup, Transact-Sql Reference" topic in SQL Server Book Book.
MSDE includes the SQL Server Agent service to manage scheduled jobs. For example, you can create and arrange a Transact-SQL backup job. SQL Server Agent Service Management Job Arrangement. To see how to demonstrate how to use a variety of stored procedures in MSDE 2000, please refer to Microsoft Knowledge Base Article: 241397 HOWTO: Back Up A Microsoft Data Engine Database with Transact-SQL For SQL Server Agent For more information, see the "SQL Server Agent Service" topic in SQL Server Online Book. The backup database is just half of the whole process. It is important to know how to restore the database from backup. The following example restores a database called mydb from the backup file C: /MSDE/backup/mydb.bak:
RESTORE DATABASE MyDB from Disk = 'c: /msde/backup/mydb.bak' If the database to be restored does not exist, the user must have a CREATE DATABASE permission to run the Restore statement. If this data is in stock, RESTORE permissions are granted a member of the sysadmin and dbcreator fixed server roles by default, and the owner (DBO) of the database. For more information on the RESTORE statement, see "RESTORE, Transact-SQL Reference" topic in SQL Server Book Book. Addition and separation databases can separate a database of data and transaction log files and reassure them to another, or rescurate to the same server. Separating a database although the database is removed from SQL Server, there is no change in data and transaction log files that make up the database. You can then use this data and transaction log file to attach the database to any SQL Server instance, which includes the server that is separated from the database. This allows the database to be used in other locations in the same state as being separated. For more information, see "Attaching and Detaching A Database" topics in SQL Server Online Books.
The following example separates a database named mydb from the current instance of SQL Server: EXEC SP_DETACH_DB 'MyDB' only the SYSADMIN fixed server role can run the sp_detach_db stored procedure. For more information on sp_detach_db stored procedures, see "sp_detach_db, transact-sql reference" topic in SQL Server Online Books.
The following example attaches two files from the database named mydb to the current instance of SQL Server: exec sp_attach_db @dbname = n'mydb ', @ filename1 = n'c: /msde/backup/mydb.mdf', @ Filename2 = N'c: /msde/backup/mydb.ldf 'uppercase letters "N" is used to add a prefix to the "Unicode string" constant. "N" prefix represents the area language in the SQL-92 standard. For more information, see the following article in the Microsoft Knowledge Base: 239530 INF: UNICODE STRING CONSTANTS IN SQL Server Require N Prefix only members of the sysadmin and dbcreator fixed server roles can run this process. For more information on sp_attach_db stored procedures, see "sp_attach_db, transact-sql reference" topic in SQL Server Book Book. The following information about OSQL tools is available for all versions of Microsoft SQL Server 2000. Reference To download the update version of SQL Server 2000 Online Book, visit the following Microsoft Web site: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp To download "SQL Server Online Books, SQL Server 7.0, please visit the following Microsoft Web site: http://download/sql70/file/2/win98/en-us/sqlbol.exe for more than MSDE 2000 Information, please refer to the following Microsoft Knowledge Base Art: 319930 How To: Connect To Microsoft Desktop Engine 241397 HOWTO: Back Up A Microsoft DeskTop Engine Database with Transact-SQL
-------------------------------------------------- ------------------------------
Information in this article applies to: • Microsoft SQL Server 2000 Desktop Engine (MSDE)
Keywords: kbhowto kbhowtomaster kbdownload kb325003http: //support.microsoft.com/kb/325003
