SQL Express 12-18-06

Where the Data's At

ALERT How a Hacker Launches a Web Application Attack

Learn how to defend against Web application attacks with real world examples of recent hacking methods such as SQL Injection Cross Site Scripting and Parameter Manipulation, Learnstep-by-step vulnerability testing methods for your own Web applications and guidelines for establishing best administration and coding practices

Where the Data's At

by Michael Otey

We all know that SQL Server 2005 Express Edition is a relational database that stores and retrieves data for various database applications. These applications typically connect to the SQL Server Express database by using a variety of middleware technologies including ODBC, OLE DB, and ADO.NET. But when you're working with an application that's using the database, you don't always see the database's architectural underpinnings that make all this work. Applications typically see the data one row at a time because they issue T-SQL queries to the database to retrieve data. T-SQL's row-oriented nature lets it return collections of rows to the application. These collections of rows are called result sets.

If you're new to SQL Server Express, you might wonder where the data in these databases comes from. You might be surprised to learn that SQL Server Express stores its database data in two primary files: the database data file that ends in the extension .mdf and the transaction log file that ends in the extension .ldf. Both the OS and a standard file can see these files. For example, if you follow the SQL Server Express installation defaults, you should wind up with the following directory in the file system:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

This directory contains the SQL Server Express database and log files. In its default state, this directory will contain data and log files for the various SQL Server Express system databases including: master, model, msdb, mssqlsystemresource, and tempdb. If you create user databases and accept the default storage locations, those database data and log files will also be created in the Data directory. You define the SQL Server Express data and log files when you create a database. For example, you use SQL Server Management Studio Express (SSMSE) to create a database by right-clicking the Database node and selecting the New Database option to display the New Database dialog box. Entering MyDB into the database name field will create a database data file named MyDB.mdf and a log file named MyDB_log.ldf, both in the Data directory. If you want to distribute a database with your application, you need to include the database's .mdf and .ldf files with your application's installation program.

Although SQL Server Express databases are usually composed of two database files, they aren't limited to just two. When you create a database, you can specify multiple data files by clicking the Add button on the New Database dialog box. Most databases won't need this addition, but sometimes backup-and-restore or availability considerations might prompt you to create multiple data files.

Special Report: Perspectives on SQL Server Sprawl

How many SQL Servers are you managing? Is your database inventory out of control? Are costs difficult to manage? You're not alone. Download this special report today to find out how SQL Server sprawl affects your organization, and learn best practices for preventing it.

Jump Start: Enabling the CLR

by Michael Otey, [email protected]
If you're getting started with SQL Server 2005 Express and you're trying to use some of the new CLR functionality, you might be surprised to run into several errors when you're trying to get your new CLR objects deployed. That's because the CLR is disabled by default in both SQL Server 2005 and SQL Server 2005 Express. To get your CLR objects deployed, you first need to enable the CLR support in SQL Server 2005 Express. To do so, you can use either the SQL Server Management Studio Query Editor or the sqlcmd command-line utility to enter the following set of T-SQL commands:

EXEC sp_configure 'clr enabled' , '1' GO reconfigure; GO

Enhance SQL Reporting Services with Double-Take

SQL Reporting Services is an exciting way for organizations to gain access and insight into their important business data stored in SQL Server. Get an overview of how to increase your production server's performance by offloading Reporting Services to a secondary server. Download your free copy today!

Keyboard Shortcut Correction

by Michael Otey, [email protected]
I was browsing through your article on keyboard shortcuts, "Top Keyboard Shortcuts in SQL Server Management Studio Express," and saw that you mentioned you can comment a line by using just Ctrl+C. This is not the case. You must first use Ctrl+K, then press Ctrl+C (second chord as they say) to comment out the statement.


You're absolutely right. The Crtl+C and Ctrl+U keyboard shortcuts that comment and uncomment lines must be preceded by the Ctrl+K key, making them two-part shortcuts.

Michael Otey

Deploy Cross-Platform Data

Are you an Oracle professional who has cross-platform responsibilities, or do you need to transfer your skill set to SQL Server? If so, register to attend the free Cross Platform Data online event January 30 and 31 and February 1, 2007. In a seminar featuring SQL Server and Oracle experts Andrew Sisson from Scalability Experts and Douglas McDowell from Solid Quality Learning, you'll learn key concepts about SQL Server 2005, including how to deploy SQL Server's BI capabilities on Oracle, proof points demonstrating that SQL Server is enterprise-ready, and how to successfully deploy Oracle on the Windows platform.

Free Conversion/Migration Web Seminar

Take the necessary steps for application management, from conversion of legacy applications to MSI to customizing applications to fit corporate standards. Don't overlook an important component of an OS migration ? join us for the free on-demand Web seminar.

Reduce Your Operational Costs

Branch offices need flexibility and autonomy in implementing IT solutions; corporate requirements require centralized management, security, and compliance initiatives. Learn to resolve these conflicts and reduce your operational costs for branch offices with limited IT resources. Download the free white paper today!


Red Gate Software Simplifies Database Documentation

by Blake Eno

Red Gate Software announced SQL Doc, a tool that transforms time-consuming database documentation into a simple point-and- click procedure. SQL Doc lets you access database overviews-- including schema and dependencies--and select which databases or sections of databases you want to document, even down to the object level. SQL Doc uses Red Gate APIs to retrieve information about your databases and can add or amend object descriptions and display all cross-database dependencies. The software exports documentation in HTML with or without frames, and can be accessed using command lines. A free, 14-day trial of SQL Doc is available at Red Gate's Web site. SQL Doc starts at $295 for the standard edition and $495 for the professional edition.

Make Your Mark on the IT Community!

Nominate yourself or a peer to become an "IT Pro of the Month." This is your chance to get the recognition you deserve! Winners will receive more than $600 in IT resources and be featured in Windows IT Pro magazine and the TechNet Flash email newsletter. It's easy to enter--accepting January nominations now for a limited time!

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.