rianto isaac's Weblog

rianto utomo isaac sahala utomo

windows, msde

leave a comment »

  • How to get and install MSDE
    Getting MSDE is quite simple, actually if you have SQL Server 2000 ( Enterprise , Standard, developer edition or even the evaluation copy) CD, just by browsing the CD you will find a folder called MSDE that contains all the installation files for MSDE. If you don’t have it, don’t worry at all. Smile and click here. It is there for free download but required a bandwidth that can manage around 110 MB of download (40 MB for the MSDE and 80 MB for the SP 4). The download is a self extracting compressed file that you need to extract it before you install it.
    (I assume that the installation files are in “C:\MSDERelA”folder)

    1. Define the command line parameters. There are two very important parameters that you have to define as command line parameters. First is the instancename and second the sa password. You cannot install MSDE release-A without the instancename parameter even if you are installing a default instance of SQL Server. If you don’t specify the password for sa , you can have a successful installation but you cannot install the latest service pack unless you change the blank password for sa.
      Here is my command line choice C:\MSDERelA\setup.exe INSTANCENAME=”” SAPWD=”P@ssw0rd” that makes a default instance installation of MSDE with the sa password set to P@ssw0rd.
    2. Verify and start the MSDE service. Go to Startà Settings à Control Panel à Administrative Tools à Services. You will find the MSSQLSERVER in the services list. Just make sure that the Startup type is set to automatic and then right-click and start the service.
    3. Congratulations. You have successfully installed MSDE.
  • How to get and install Client Tools for MSDE.
    Actually there is no client tool developed by Microsoft specially designed for MSDE. Once you install MSDE, the only client tool that you have is osql.exe. If you want to have Enterprise manager or Query analyzer, you need to borrow it from SQL Server 2000, Standard Edition, Developer Edition, Enterprise Edition or even Evaluation Edition.
    IMPORTANT NOTE: For client tools licensing, look here.
    Simply follow the normal installation of mentioned edition of SQL Server 2000 and go for “Install database server”. Follow the default installation process and in the Installation Definition page select Client Tool Only. Once installation is complete open the Enterprise Manager and if you don’t see the SQL Server registration just add the registration like any instance of SQL Server and you have the MSDE connected to the client tools and every thing is accessible through Enterprise manager. Of course Northwind and Pubs database are not there. If you need them just go to this link and download and restore the sample databases.
  • How to get and install SP4 for MSDE.
    You can download the service pack 4 from this link ( SP 4). You can simply expand the files and run the setup. Installation is quite straight forward. Remember if you cannot install SP_4 with blank password for sa . If you would like to go for service pack 4 you should know that SP-4 has a complete installation of MSDE inside, so by installing SP-4 you are upgrading your installation of MSDE to MSDE+SP4 so the following command will do the job for you.
    “C:\SQL2KSP4\MSDE\setup.exe” UPGRADE=1 DISABLENETWORKPROTOCOLS=1
    By the way, if you are installing SP3 you can just run the setup.exe without any parameters.
    If you plan to install MSDE Service Pack 4 from the scratch without installing MSDE first, simply run the setup.exe for MSDE+SP4 with the parameters mentioned for normal installation of MSDE. Then install the client tools.

rianto utomo bole nyontek dari http://www.dotnetking.com/ArticleDetails.aspx?ArticleID=134

——————————————————————————————————————————————

Interactively Enter Transact-SQL Statements

To display a list of the case-sensitive options of the osql utility, type the following at a command prompt, and then press ENTER:

osql -?

For more information about each option of the osql utility, see the “osql Utility” topic in SQL Server Books Online.

To interactively enter Transact-SQL statements, follow these steps:

  1. Verify that MSDE 2000 is running.
  2. Connect to MSDE 2000 (for more information, see the section titled “Connect to SQL Server Desktop Engine (MSDE 2000)”).
  3. At the osql prompt, type the Transact-SQL statements, and then press ENTER. When you press ENTER, at the end of each input line, osqlcaches the statements on that line.
    • To run the currently cached statements, type “Go”, and then press ENTER.
    • To run a batch of Transact-SQL statements, enter each Transact-SQL command on separate lines. Then, type “Go” on the last line to signal the end of the batch and to run the currently cached statements.

    The results appear in the console window.

  4. To exit from osql, type QUIT, or EXIT, and then press ENTER.

Submit an Osql Job

Typically, you submit an osqljob one of two ways. You can either:

  • Specify a single Transact-SQL statement.-or-
  • -or-Point the utility to a script file.

Here is more information about each method.

Specify a Single Transact-SQL Statement

To run a Transact-SQL statement against the local default instance of MSDE 2000, type a command similar to this one

osql -E -q “Transact-SQL statement”

where

  • -Euses Microsoft Windows NT authentication.-and-
  • -and--q runs the Transact-SQL statement but does not exit osql when the query completes.

To run the Transact-SQL statement and exit osql, use the -Q argument instead of -q.

Point the Utility to a Script File

To point the utility to a script file, follow these steps:

  1. Create a script file that contains a batch of Transact-SQL statements (such as myQueries.sql).
  2. Open a command prompt, type a command similar to the following, and then press ENTERosql -E -i input_filewhere

    input_file is the full path of the script file. For example, if the script file myQueries.sql is located in the C:\Queries folder, replace the parameter input_file with C:\Queries\myQueries.sql.

    The results of the script file appear in the console window. If you want to direct the results to a file, add the -ooutput_file argument to the command shown earlier. For example:

    osql -E -i input_file -o output_file

    where

    output_file is the full path of the output file.

    To remove the numbering and prompt symbols in the output, add the -n option to the command shown earlier. For example:

    osql -E -i input_file -o output_file -n

Back to the top

Connect to SQL Server Desktop Engine (MSDE 2000)

To connect to MSDE 2000, follow these steps:

  1. Verify that MSDE 2000 is running.
  2. Open a command prompt on the computer that is hosting the instance of MSDE 2000 to which you want to connect.
  3. Type the following command, and then press ENTER:osql -EThis connects you 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, MSDE 2000 may not be running or you may have provided an incorrect name for the named instance of MSDE 2000 that is installed:

    [Shared Memory]SQL Server does not exist or access denied.
    [Shared Memory]ConnectionOpen (Connect()).

    If you are successfully connected to the server, the following prompt appears:

       1>

    This prompt indicates that osql is started. Now, you can interactively enter Transact-SQL statements and the results appear on the command prompt line.

Back to the top

Manage MSDE 2000

The remaining sections of this article introduce you briefly to the Transact-SQL commands most frequently used to manage MSDE 2000.

Create a New Login

A user cannot connect to SQL Server without providing a valid login id. The sp_grantloginstored procedure is used to authorize a Microsoft Windows network account (either a group or a user account) for use as a SQL Server login for connecting to an instance of SQL Server by using Windows Authentication. The following example permits a Windows NT user named Corporate\Test to connect to a SQL Server instance:

EXEC sp_grantlogin 'Corporate\Test'

Only members of the sysadmin or the securityadmin fixed server roles can run the sp_grantlogin stored procedure. For more information about roles, see the “Roles, SQL Server Architecture” topic in SQL Server Books Online.

For more information about the sp_grantlogin stored procedure, see the “sp_grantlogin, Transact-SQL Reference” topic in SQL Server Books Online.

You use the sp_addloginstored procedure to create a new login account for SQL Server connections by using SQL Server Authentication. The following example creates a SQL Server login for a user named “test” with a password of “hello”:

EXEC sp_addlogin 'test','hello'

Only members of the sysadmin and the securityadmin fixed server roles can run the sp_addlogin stored procedure. For more information about the sp_addlogin stored procedure, see the “sp_addlogin, Transact-SQL Reference” topic in SQL Server Books Online.

Access a Database

After a users connects to an instance of SQL Server, they cannot perform activities in a database until the dbo grants them access to the database. You can use the sp_grantdbaccess stored procedure to add a security account for a new user to the current database. The following example adds an account for a Microsoft Windows NT user named Corporate\BobJ to the current database and names it “Bob”:

EXEC sp_grantdbaccess 'Corporate\BobJ', 'Bob'

The sp_adduser stored procedure performs the same function as the sp_grantdbaccess stored procedure. Because, the sp_adduser stored procedure is included for backward compatibility, Microsoft recommends that you use the sp_grantdbacess stored procedure.

Only members of the sysadmin fixed server role, the db_accessadmin and the db_owner fixed database roles can run the sp_grantdbaccess stored procedure. For more information about the sp_grantdbaccess stored procedure, see the “sp_grantdbaccess, Transact-SQL Reference” topic in SQL Server Books Online.

How to Change the Password for a Login

To change the password of a login, use the sp_passwordstored procedure. The following example changes the password for the login “test” from “ok” to “hello”:

EXEC sp_password 'ok', 'hello','test'

Execute permissions default to the public role for a user that is changing the password for his or her own login. Only members of the sysadmin role can change the password for another user’s login. For more information about the sp_password stored procedure, see the “sp_password, Transact-SQL Reference” topic in SQL Server Books Online

Create a Database

A MSDE 2000 database is made up of a collection of tables that contain data and other objects, such as views, indexes, stored procedures, and triggers, which are defined to support activities performed with the data. To create a MSDE 2000 database, use the “CREATE DATABASE” Transact-SQL command. For more information about creating a database, see the “Creating a Database” topic in SQL Server Books Online.

The following example creates a database named Test. Because no additional parameters are added to the command, the Test database will be the same size as the modeldatabase:

CREATE DATABASE Test

CREATE DATABASE permission defaults to members of the sysadmin and the dbcreator fixed server roles. For more information about the “CREATE DATABASE” command, see the “CREATE DATABASE, Transact-SQL Reference” topic in SQL Server Books Online.

To create a new database object, use the CREATE Transact-SQL command. For example, to create a new table, use the “CREATE TABLE” Transact-SQL command. For more information, refer to SQL Server Books Online.

Back Up and Restore Databases

The backup and restore component of SQL Server provides an important safeguard for protecting critical data stored in SQL Server databases.

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

  • Media failure.
  • User errors.
  • Permanent loss of a server.

Additionally, backing up and restoring databases is useful for other purposes, such as copying a database from one server to another. By backing up a database from one computer and restoring the database to another, you quickly and easily make a copy of a database.

For more information about database backup and restore operations, see the “Backing Up and Restoring Databases” topic in SQL Server Books Online.

The following example performs a full database backup for a database named mydb, names the backup Mydb.bak, and then stores the backup in the C:\Msde\Backup folder:

BACKUP DATABASE mydb TO DISK = 'C:\MSDE\Backup\mydb.bak'

The following example performs a log backup for a database named mydb, names the backup Mydb_log.bak, and then stores it in the C:\Msde\Backup folder:

BACKUP LOG mydb TO DISK = 'C:\MSDE\Backup\mydb_log.bak'

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. For more information about the BACKUP statement, see the “BACKUP, Transact-SQL Reference” topic in SQL Server Books Online.

MSDE includes the SQL Server AgentService for managing scheduled jobs. For example, you can create and schedule a Transact-SQL backup job. The SQL Server Agent Service manages the job scheduling. For sample code about how to use the various stored procedures with MSDE 2000 to perform a backup and schedule the backup, see the following article in the Microsoft Knowledge Base:

241397  How To 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 Books Online.

Backing up a database is only half of the process. It is important to know how to restore the database from a backup. The following example restores a database that is named mydb from the backup file C:\Msde\Backup\Mydb.bak:

RESTORE DATABASE mydb FROM DISK ='C:\MSDE\Backup\mydb.bak'

If the database that is being restored does not exist, the user must have CREATE DATABASE permissions to run the RESTORE statement. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database. For more information about the RESTORE statement, see the “RESTORE, Transact-SQL Reference” topic in SQL Server Books Online .

Attach and Detach a Database

The data and transaction log files of a database can be detached and then reattached to another server, or even to the same server. Detaching a database removes the database from SQL Server but leaves the database intact in the data and transaction log files that compose the database. You can then use these data and transaction log files to attach the database to any instance of SQL Server, including the server from which the database was detached. This makes the database available in exactly the same state it was in when it was detached. For more information, see the “Attaching and Detaching a Database” topic in SQL Server Books Online.

The following example detaches a database named mydbfrom the current instance of SQL Server:

EXEC sp_detach_db 'mydb'

Only members of the sysadmin fixed server role can run the sp_detach_db stored procedure. For more information about the sp_detach_db stored procedure, see the “sp_detach_db, Transact-SQL Reference” topic in SQL Server Books Online.

The following example attaches two files from a database named mydbto 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 capital letter N is used to prefix a Unicode String constant. The “N” prefix stands for National 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 the dbcreator fixed server roles can run this procedure. For more information about the sp_attach_db stored procedure, see the “sp_attach_db, Transact-SQL Reference” topic in SQL Server Books Online.

The following information about the use of the osql utility also applies to all editions of Microsoft SQL Server 2000.

rianto utomo bole nyontek dari http://support.microsoft.com/kb/325003

———————————————————————————————————————————————————–

Re: Reset Forgotten MSDE Password

If the account you login with is an administrator on the local machine, you
can use the osql command line tool to execute the sp_password stored
procedure:

osql -E -Q “EXEC sp_password @new=’newpassword’, @loginame = ‘sa'”

That’s not a spelling mistake in loginame. Apperently long, long ago when
the sp_password procedure was created bytes were really expensive and
someone decided to save some on that extra n.

rianto utomo bole nyontek dari http://dbaspot.com/sqlserver-tools/224745-reset-forgotten-msde-password.html

 

————————————————————————————————————————————————–

login failed for user sa reason not associated with a trusted sql server connection msde 2000

To work around this problem, use one of the following methods depending on the symptom that you are experiencing:

  • A SQL Server login

    Use a valid Windows login to connect to SQL Server. If you must continue to use a SQL Server login, you can change the security authentication mode in SQL Server to SQL Server and Windows. To do this, follow these steps:

    1. Start Enterprise Manager.
    2. Expand Microsoft SQL Servers, and then expand SQL Server Group.
    3. Right-click the server that you want to change to SQL Server and Windows authentication, and then click Properties.
    4. In the SQL Server Properties dialog box, click the Security tab, click SQL Server and Windows, and then click OK.
    5. When you are prompted to re-start the SQL Server service, click Yes.

    Note If you use SQL Server 2005, use SQL Server Management Studio instead of Enterprise Manager to change the security authentication mode.

  • A SQL Server 2000 Desktop Engine (MSDE 2000) that is installed on a Microsoft Windows 98-based computer

    When SQL Server 2000 Desktop Engine is installed on a Windows 98-based computer, you must use a standard SQL Server login to connect to the database. The Windows only authentication mode is not supported when you install SQL Server 2000 Desktop Engine on Windows 98-based computer.

  • A Windows account with insufficient permissions

    To work around this problem, you must add the Windows account to SQL Server, and then grant the appropriate permissions to each database that the user requires access to. To do this in SQL Server 2000, follow these steps:

    1. Start Enterprise Manager.
    2. Expand Microsoft SQL Server, and then expand SQL Server Group.
    3. Expand your server, and then expand Security.
    4. Right-click Logins, and then click New Login.
    5. In the SQL Server Login Properties dialog box, enter the Windows account name in the Name box. In the Domain list, select the domain that the Windows account is a member of.
    6. Click the Database Access tab, set the appropriate permissions for the client, and then click OK.

    Note If you use SQL Server 2005, use SQL Server Management Studio instead of Enterprise Manager to change the security authentication mode.

    Note After you have added the account, you will still have to grant access to the individual database objects that the user requires access to.

Note Whenever possible, we recommend that you set the SQL Server authentication to Windows only.

rianto utomo bole nyontek dari http://support.microsoft.com/kb/889615/en-us

Written by isaaconi

Februari 27, 2012 pada 1:15 pm

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: