Use osql to manage the SQL Server Desktop Engine (MSDE 2000)

How to: Use osql to manage the SQL Server Desktop Engine (MSDE 2000)

Transferred from blog of msdn accpwww

This page

	Summary
		What is osql?
		How to Use osql?
			Interactive input of transact-SQL statements
			Submit an osql job
		Connect to the SQL Server Desktop Engine (MSDE 2000)
		Manage MSDE 2000
			Create a Logon account
			Access Database
			How to change the logon Password
			Create a database
			Back up and restore Databases
			Attaching and detaching Databases
	Reference
	This articleArticleThe information in applies:

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 uses MSDE 2000 to embed Program Interact with it. 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 use Osql Tool Management MSDE 2000.

Back to Top

What is osql?

OsqlThe tool is a Microsoft Windows 32 command prompt tool that can be used to run Transact-SQL statements and script files.OsqlThe tool uses the ODBC database application programming interface (API) to communicate with the server.

Back to Top

How to Use osql?

GenerallyOsqlTools:

•	The user enters a Transact-SQL statement in a similar way as when using a command prompt.
•	User submissionOsqlJob: • Specifies a single Transact-SQL statement to run. -Or- • Point the tool to a script file containing the transact-SQL statement to be run.

Interactive input of transact-SQL statements

To displayOsqlThe tool's case-sensitive Option List. Enter the following content at the command prompt and press Enter:

Osql -?

For more information, seeOsqlFor more information about each option of the tool, see the "osql utility" topic in "SQL Server online books.

To interactively enter a Transact-SQL statement, follow these steps:

1.	Check that MSDE 2000 is running.
2.	Connect to MSDE 2000 (for more information, see section titled "connect to SQL Server Desktop Engine (MSDE 2000 ).
3.	InOsqlAt the command prompt, type A Transact-SQL statement and press Enter. When you press enter after each line,OsqlThe statements on the command line will be cached. • To run the current cached statement, type "go" and press Enter. • To run a batch of transact-SQL statements, enter each of the statements on a separate line. Then, type "go" on the last line to end the batch processing command and run the current cached statement. The running result appears in the console window.
4.	When you press enter after each lineOsqlExit. Type quit or exit and press Enter.

Submit an osql job

Generally, you can use either of the two methods to submitOsqlJob. You can:

•	Specifies a single Transact-SQL statement. -Or-
•	Point the tool to a script file.

The following describes each method in detail.

Specify a single Transact-SQL statement

To run Transact-SQL on the local default instance of MSDE 2000, type A Command similar to the following:

Osql-e-Q "Transact-SQL statement"

Where

•	-EIndicates Microsoft Windows NT authentication. -And-
•	-QIt indicates to run the transact-SQL statement, but does not exit at the end of the query.Osql.

To run a Transact-SQL statement and exitOsql, Please use-QParameter to replace-Q.

Point the tool to a script file

To direct the tool to a script file, follow these steps:

1.

Create a script file (such as myqueries. SQL) that contains a batch of transact-SQL statements ).

2.

Open a command prompt, type A Command similar to the following, and press Enter: Osql-e-I input_file Where Input_fileIs the script file and its complete path. For example, if the script file myqueries. SQL is in the C: \ queries folderInput_fileReplace it with c: \ queries \ myqueries. SQL. The running result of the script file appears in the console window. If you want to direct the running result to a file, add-OOutput_fileParameters. For example: Osql-e-I input_file-O output_file Where Output_fileIs the output file and its complete path. To remove the numbers and tooltips in the output result, add them to the preceding command.-N. For example: Osql-e-I input_file-O output_file-n

Back to Top

Connect to the SQL Server Desktop Engine (MSDE 2000)

To connect to MSDE 2000, follow these steps:

1.	Check that MSDE 2000 is running.
2.	Open a command window on the computer that hosts the MSDE 2000 instance you want to connect.
3.	Type the following command and press Enter: Osql-e You can connect to the local default instance of MSDE 2000 by using Windows authentication. To connect to a named instance of MSDE 2000, type: Osql-e-s servername \ InstanceName If you receive the following error message, it indicates that MSDE 2000 may not be running, or you may provide an incorrect name for the named instance of MSDE 2000 installed: [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 indicatesOsqlStarted. Now, you can enter a Transact-SQL statement interactively, and the running result will appear on the command prompt line.

Back to Top

Manage MSDE 2000

The following section briefly describes the most common Transact-SQL commands used to manage MSDE 2000.

Create a Logon account

A user without a valid logon ID cannot connect to SQL Server. CallableSp_grantloginA stored procedure is used to authorize a Microsoft Windows Network account (a group or a user account) as an SQL Server Logon account that uses Windows authentication to connect to the SQL server instance. The following example allows a Windows NT user named login ATE \ test to connect to the SQL server instance:

 
Exec sp_grantlogin 'your ATE \ Test'
 

OnlySysAdminOrSecurityadminFixed server role members can runSp_grantloginStored procedure. For more information about these roles, see the "roles, SQL Server architecture" topic in "SQL Server online books.

RelatedSp_grantloginFor more information about stored procedures, see the "sp_grantlogin, transact-SQL reference" topic in "SQL Server online books.

You can useSp_addloginThe stored procedure creates a new Logon account that uses SQL Server Authentication To establish an SQL server connection. The following example shows how to create an SQL Server logon password "hello" for a user named "test:

 
Exec sp_addlogin 'test', 'Hello'
 

OnlySysAdminAndSecurityadminFixed server role members can runSp_addloginStored procedure. RelatedSp_addloginFor more information about stored procedures, see the "sp_addlogin, transact-SQL reference" topic in "SQL Server online books.

Access Database

After you connect to an instance of SQL ServerDBOGrant them access to the database before they can execute activities in the database. You can useSp_grantdbaccessThe stored procedure adds a security account to the current database for a new user. The following example shows how to add an account to the current database and name it "Bob" for a Microsoft Windows NT user named mongoate \ bobj ":

 
Exec sp_grantdbaccess 'your ATE \ bobj ', 'bob'
 

Sp_adduserStored Procedure execution andSp_grantdbaccessThe stored procedure has the same functions. Because includesSp_adduserThe stored procedure is backward compatible, so Microsoft recommends that you useSp_grantdbacessStored procedure.

OnlySysAdminFixed server roles,Db_accessadminAndDb_ownerMembers of the fixed database role can runSp_grantdbaccessStored procedure. RelatedSp_grantdbaccessFor more information about stored procedures, see the "sp_grantdbaccess, transact-SQL reference" topic in "SQL Server online books.

How to change the logon Password

To change the logon password, useSp_passwordStored procedure. The following example changes the password for "test" Logon from "OK" to "hello ":

 
Exec sp_password 'OK', 'Hello', 'test'
 

By default, the execution permission is granted to the public role of the user whose logon password is being changed. OnlySysAdminRole to change the logon password for other users. RelatedSp_passwordFor more information about stored procedures, see the "sp_password, transact-SQL reference" topic in "SQL Server online books.

Create a database

MSDE 2000 a database consists of a collection of tables that contain data and other objects, such as views, indexes, stored procedures, and event triggers, these are defined as various activities that support Data Execution. To create a MSDE 2000 database, run the "create database" Transact-SQL command. For more information about database creation, see the "creating a database" topic in "SQL Server online books.

The following example createsTest. Because no other parameters are added to the command lineTestThe databaseModelThe database size is the same:

 
Create Database Test
 

Create Database permission is granted by defaultSysAdminAndDbcreatorA member of a fixed server role. For more information about the "create database" command, see the "Create Database, transact-SQL reference" topic in "SQL Server online books.

To create a new database object, run the "Create Transact-SQL" command. For example, to create a new table, run the "Create Table" Transact-SQL command. For more information, see "SQL Server online books ".

Back up and restore Databases

The backup and restoration components of SQL Server provide an important protection measure to protect key data stored in SQL Server databases.

With proper planning, you can recover from many failures, including:

•	Storage media failure.
•	User error.
•	The server is permanently lost.

In addition, there are other purposes for backing up and restoring a database, such as copying a database from one server to another. By backing up a database from one computer and restoring the database to another computer, you can quickly and conveniently create copies of the database.

For more information about database backup and restoration operations, see the "backing up and restoring databases" topic in "SQL Server online books.

The following example isMydbTo perform a full database backup, name the backup mydb. Bak, and store the backup in the C: \ MSDE \ backup folder.

 
Backup database mydb to disk = 'C: \ MSDE \ backup \ mydb. Bak'
 

The following example isMydbThe database executes a log backup. Name this backup mydb_log.bak and store it in the C: \ MSDE \ backup Folder:

Backup log mydb to disk = 'C: \ MSDE \ backup \ mydb_log.bak'
 

Backup database and backup log are granted by default.SysAdminFixed server roles andDb_ownerAndDb_backupoperatorA member of a fixed database role. For more information about backup statements, see the "backup, transact-SQL reference" topic in "SQL Server online books.

MSDE includesSQL Server AgentThe service is used to manage arranged jobs. For example, you can create and schedule a Transact-SQL backup job. SQL Server Agent service management job arrangement. To view examples of how to use various stored procedures in MSDE 2000 to execute and schedule backupsCode, See the following Microsoft Knowledge Base Article:

241397 Http://support.microsoft.com/kb/241397/EN-US) Howto: Back up a Microsoft Data Engine database with Transact-SQL

For more information about the SQL Server Agent service, see the "SQL Server Agent service" topic in "SQL Server online books.

Backing up a database is only half of the entire process. It is also important to know how to restore a database from a backup. In the following exampleMydbFrom 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, you must have the create database permission to run the restore statement. If the database exists, the restore permission is granted by default.SysAdminAndDbcreatorMembers of the fixed server role and the database owner (DBO). For more information about restore statements, see the "Restore, transact-SQL reference" topic in "SQL Server online books.

Attaching and detaching Databases

You can detach the data and transaction log files of a database and then attach them to another server or the same server. Although a database is removed from SQL Server, the data and transaction log files that constitute the database are not modified. Then you can use the data and transaction log files to attach the database to any SQL server instance, including the server from which the database is detached. This allows the database to be used in the same status as when it is detached. For more information, see the "attaching and detaching A Database" topic in "SQL Server online books.

In the following exampleMydbThe database is separated from the current instance of SQL Server:

 
Exec sp_detach_db 'mydb'
 

OnlySysAdminFixed server role members can runSp_detach_dbStored procedure. RelatedSp_detach_dbFor more information about stored procedures, see the "sp_detach_db, transact-SQL reference" topic in "SQL Server online books.

The following exampleMydbTwo files of the database are appended 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'
 

The uppercase letter "N" is used to add a prefix to the "Unicode string" constant. The "N" prefix represents the regional language in the SQL-92 standard. For more information, see the following articles in the Microsoft Knowledge Base:

239530 Http://support.microsoft.com/kb/239530/EN-US) INF: Unicode string constants in SQL Server require n prefix

OnlySysAdminAndDbcreatorMembers of the fixed server role can run this process. RelatedSp_attach_dbFor more information about stored procedures, see the "sp_attach_db, transact-SQL reference" topic in "SQL Server online books. The following information aboutOsqlThe tool usage information applies to all versions of Microsoft SQL Server 2000.

Back to Top

To download an updated version of "SQL Server 2000 online books", visit the following Microsoft Web site:

Http://www.microsoft.com/ SQL /techinfo/productdoc/2000/books.asp (Http://support.microsoft.com /? SCID = http % 3A % 2f % 2fwww.microsoft.com % 2 fsql % 2 ftechinfo % 2 fproductdoc % 2f2000% 2fbooks. asp)

To download SQL Server 7.0 of "SQL Server online books", visit the following Microsoft Web site:

Http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/SQLBOL.exe Http://download.microsoft.com/download/sql70/file/2/win98/en-us/sqlbol.exe)

For more information about MSDE 2000, see the following Microsoft Knowledge Base Article:

319930 Http://support.microsoft.com/kb/319930/EN-US) How to: connect to Microsoft Desktop Engine 241397 Http://support.microsoft.com/kb/241397/EN-US) Howto: Back up a Microsoft Desktop Engine database with Transact-SQL

Back to Top