Tool Used with SQL Server 2005 Express

Downloading the Necessary Tools

by Michael Otey, [email protected]

In previous newsletters I’ve covered topics such as FAQs about SQL Server 2005 Express, how SQL Server Express compares to Microsoft Access, and how you can migrate your existing Microsoft Data Engine (MSDE) installation to SQL Server Express. But what if you’re a beginner SQL Server Express user? How should you get started? This issue, I’ll cover some of the groundwork you need to do to get started with SQL Server Express.

First, you need to gather the information you want to track. Databases such as SQL Server Express are designed primarily to store, organize, and retrieve data. You might want to create a database to keep track of your personal media collection, recipes, or family and friends' contact information. If you own a small business, you might want to develop an employee database to store employees' home address, phone numbers, and Social Security information, or you might want to track company equipment or inventory.

Second, is determining how you're going to use your database because you might need to download supporting applications from the Microsoft Web site. If you're a beginner and you want to ease into learning about databases by creating a simple database of your CD collection, then all you'll need is SQL Server Express.

If you're familiar with creating databases, then you already know that SQL Server Express is really a back end database. To create databases and manage SQL Server Express you'll also want to download a free copy of SQL Server Management Studio for Express (SSMSE).

Finally, if you want to use SQL Server Express in your software development work, you'll need to download the Visual Studio 2005 Express application development tool (free until November 2006).

To build database applications, you can also use other Java-type tools, such as Borland’s JBuilder, or even open source development tools such as php. (You use these tools to enter data into and pull data out of a database.) If you’re not a programmer, then Access is probably your best bet for a database development tool. Access connects to SQL Server Express databases in the same way you connect to local Access databases.

Now that you’ve collected all of the required components, you can build your database in one of two ways. You can either use an interactive design tool, such as SSMSE or Access, or you can write a script that consists of T-SQL Data Definition Language (DDL) commands. For experienced database developers, T-SQL scripts are the way to go because after you create your database you have the code that documents the objects you’ve created. Plus, you can reuse a script if you want to recreate a database or some of its objects. If you’re new to database development, then you’re probably not familiar with T-SQL (and you won’t want to have to work on the equivalent of a Master’s degree before putting SQL Server Express to work). In that case, you can use SSMSE to interactively build your database and database objects. Better yet, SSMSE can take the objects you created and use them to generate a script that contains T-SQL commands--which is an easy way to create your own library of T-SQL scripts.

-------------------------

Downloading SQL Server Management Studio for Express

by Michael Otey, [email protected]

Databases store related information and can comprise many different objects. The primary object in a database is a table. You can create a SQL Server Express database by using either the command line (CLI) sqlcmd utility or the graphical SQL Server Management Studio for Express (SSMSE) product.

If you’re not familiar with the T-SQL language, SSMSE is definitely the way to go. To begin, download and run the SQLServer2005_SSMSEE.msi program. Open SSMSE by clicking the SSMSE icon or selecting it from the Start menu. Then right-click the "Database node" option and select "New database" from the menu. On the New Database dialog, enter a name for the new database. For example, if you’re creating a database to catalog your media collection, you might enter MyMedia. Click the OK button to create the database. By default, when you create a new database, SQL Server Express creates two files: one for the data and the other for the transaction log.

If you expand the Database node in SSMSE, you’ll see the name you entered for the new database. If you want to see or save the T-SQL code SQL Server Express generated when you created that database, right-click on your new MyMedia database name and select Script Database as, Create To, and New Query Editor Window. SSMSE automatically generates the T-SQL code that creates the database for you. Now you have a copy of the T-SQL code as well as a script that you can use to document and recreate the task of creating a database. The script contains a number of commands, but the core of the script is the following Create Database statement.

CREATE DATABASE [MyMedia] ON PRIMARY( NAME = N'MyMedia', FILENAME = N'C:Program FilesMicrosoft SQLServerMSSQLDataMyMedia.mdf' , SIZE = 2048KB , MAXSIZE =UNLIMITED, FILEGROWTH = 10%)&bbsp LOG ON( NAME = N'MyMedia_log', FILENAME = N'C:Program FilesMicrosoftSQL ServerMSSQLDataMyMedia_log.ldf' , SIZE = 4096KB , MAXSIZE =2048GB , FILEGROWTH = 10%)&bbsp COLLATE SQL_Latin1_General_CP1_CI_AS

--------------------------

Check It Out

Have you been searching in vain for Microsoft’s SQL Server Express online support? You’re not alone--Microsoft's "Getting Started with SQL Server" is the place to find support for most of your SQL Server Express questions.

See you there.

-------------------------------

From the Community

I tried installing SQL Server Express on a Windows XP system on a compressed drive with NTFS. SQL Server Express displayed a message that indicated that it couldn't be installed on a compressed or encrypted drive. These requirements aren't listed anywhere. You might consider writing an article that addresses this problem and offers possible workarounds.

- Chuck Wollaston

Chuck, thanks for the heads-up. I agree that this limitation in SQL Server Express isn't documented very well. However, not being able to install SQL Server Express on a compressed drive is a general limitation that applies to all versions of SQL Server. Microsoft intended this limitation because of the performance overhead that using an encrypted drive incurs.

- Michael Otey

Thanks for the article in the newsletter I received today. One other problem that I'm aware of: If you're using Microsoft Database Library (DBLIB) in your database application, you must change to another access method because SQL Server Express doesn't support DBLIB. This ought to be trivial, but of course it's not--in fact, we've encountered several problems that we haven't solved yet. I'm using Sybase PowerBuilder 9 to create database applications, but it seems that this limitation would affect anyone with old code who used DBLIB.

- Joe Landau

Thanks for the information about DBLIB, Joe. You're right. You can't use DBLIB to access SQL Server Express. ODBC or OLE DB would probably be your best bets for converting your existing DBLIB applications, but you'll have to make some code changes.

- Michael Otey