SQL Server Express Samples Overview

Updated: December 2005

AdventureWorks Database

The AdventureWorksDB.msi contains the AdventureWorks OLTP database files. Completely remove previous installations of the database and then run the MSI to extract the database files.

To remove a previous download of an AdventureWorks database

1. Drop the AdventureWorks database.

2. From  Add or Remove Programs, select AdventureWorksDB and click Remove.

 

To remove an AdventureWorks database installed by using Microsoft® SQL Server 2005 setup

1. Drop the AdventureWorks database.

2. From Add or Remove Programs, select Microsoft SQL Server 2005 and click Change.

3. From Component Selection, select Workstation Components and then click Next.
4. From Welcome to the SQL Server Installation Wizard, click Next.
5. From System Configuration Check, click Next.
6. From Change or Remove Instance, click Change Installed Components.
7. From Feature Selection, expand the Documentation, Samples, and Sample Databases node.
8. Select Sample Code and Applications.
9. Expand Sample Databases, select the sample database to be removed and select
Entire feature will be unavailable. Click Next.

10. Click Install and finish the installation wizard.

If you are using more than one Microsoft SQL Server instance on the machine where you want to install the database you may need to change the directory where the contents of this MSI is installed in the installation wizard to match the directory where the master database file is located. To determine where the master database file is located connect either Express Manager or SQL Server Management Studio to the instance where you want to install the AdventureWorks database. Then execute the following query:

select physical_name from sys.database_files where name = 'master'

To use this sample database and the following samples, you must attach the files to an instance of either Microsoft SQL Server 2005 Express Edition or Microsoft SQL Server 2005. In either SQLCMD or SQL Server Management Studio, execute a script similar to the following:

exec sp_attach_db @dbname=N'AdventureWorks', @filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', @filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'

If you have installed these files to a different drive or directory, you will have to revise the paths appropriately before you execute the sp_attach_db stored procedure.

Note that you must detach the database in order to uninstall it.

 

SQL Server 2005 Samples

The SqlServerSamples.msi contains samples for Microsoft SQL Server 2005 and Microsoft SQL Server 2005 Express Edition. Most of the samples have been developed in both Microsoft Visual C#® and Microsoft Visual Basic® .NET.

Run the MSI to extract the samples. Unless you specify otherwise, the samples are installed in [drive:]\Program Files\Microsoft SQL Server 2005\90\Samples.

Before using the samples, please read the Ten Things You Need To Know To Get Started With Samples.

Samples demonstrating the following technologies will work on SQL Server Express unless noted in the description:

·          Engine:

o         ADO.NET

o         Common Language Runtime (CLR) Integration

o         Server Management Objects (SMO)

o         Service Broker

o         Transact-SQL

o         XML

·          Integrated Samples

·          Replication

To execute the Transact-SQL scripts on SQL Server Express, use the following parameters with SQLCmd:

SQLCmd –S servername\instancename –E –I –i filename.sql

Note: To run the SMOPing sample, make sure you pass in the server name and instance with no space, for example: SMOPing.exe –Sservername\instancename.

The following is a complete list of the samples with their descriptions, organized by technology. 

New samples added for the December 2005 web refresh include

·          Common Language Runtime (CLR) Integration:

o         Oracle TVF

o         Send DataSet

o         System.Transactions

·          Server Management Objects (SMO):

o         CheckIdentityValues

o         DatabaseDefrag

o         DatabaseSpace

o         IndexSizes

o         RebuildAllIndexes

o         ServiceBrokerConfiguration

o         SmoScripter

·          Replication

o         ActiveX Control Samples

 

ADO.NET

Sample name

Description

Reading and Writing Large Binary Data

Programmatically reads binary data from a file into a database. Also retrieves binary data stored in the database and writes the contents to a file.

MARS

Demonstrates issuing multiple commands in parallel on the same connection.

 

Common Language Runtime (CLR) Integration

Sample name

Description

AdventureWorks CLR Layer

The AdventureWorks CLR Integration Layer sample for Microsoft SQL Server 2005 provides some useful utilities that form an extra layer of functionality on top of the base AdventureWorks sample database.

The first utility creates contact records for various types of people involved in the AdventureWorks database. The contact information is specified by using XML and is passed to a C#-based stored procedure.

The second utility defines a Currency user-defined data type by using C#. This user-defined data type encapsulates both an amount and a culture, which helps determine the correct way to render the amount as a currency value in that culture.

The third utility provides a currency conversion function that returns an instance of the Currency user-defined type. If the AdventureWorks database has a conversion rate from USD to the correct currency associated with the specified culture, the conversion function returns a Currency user-defined type that has the converted rate and a culture that matches the requested culture. Otherwise, a Currency user-defined type is returned with the original amount (which should be in USD) with the en-us culture.

The utilities also demonstrate how to unregister and register common language runtime (CLR) methods and assemblies by using Transact-SQL.

ArrayParameter

Demonstrates how to pass an array of information from a client to a CLR integration stored procedure on the server using a CLR integration user-defined data type.

AssemblyCleanup

The AssemblyCleanup sample contains a .NET Stored Procedure, written in C#, that cleans-up unused assemblies in the current database by querying the metadata catalogs.

Calendar-Aware Date/Time UDTs

The CADatetime sample defines two user-defined data types (CADatetime and CADate) which provide calendar-aware handling of dates and times.

Hello World

The Hello World sample demonstrates the basic operations that are involved in creating, deploying, and testing a simple CLR integration-based stored procedure. This sample also demonstrates how to return data via an output parameter and via a record, which is dynamically constructed by the stored procedure and returned to the client.

Hello World Ready Sample

The Hello World Ready sample demonstrates the basic operations that are involved in creating, deploying, and testing a simple world ready CLR integration-based stored procedure. A world ready component can be easily localized into different languages for different markets around the world without changing the component's source code. This sample also demonstrates how to return data through an output parameter and through a record, which is dynamically constructed by the stored procedure and returned to the client.

Handling LOB using CLR

Demonstrates using CLR stored procedures to transfer large binary objects between a SQL Server 2005 database and files accessible to the server. It also demonstrates registering and dropping CLR stored procedures and assemblies, invoking CLR stored procedures, performing data access from CLR stored procedures, invoking Transact-SQL stored procedures from CLR stored procedures, and using a file to log errors during processing of server-side code.

Impersonation Sample

The Impersonation sample demonstrates how to use impersonation to use the credentials passed from the client to access operating system protected resources, such as files, when you are using integrated security.

In-Process Data Access Sample

This sample contains several simple functions that demonstrate various features of the CLR Integration in-process data access provider.

Oracle TVF (December 2005)

Demonstrates how to invoke the managed code interface to Oracle to expose the results of any Oracle query as a table-valued function.

Result Set Sample

This sample demonstrates how to use server-side cursors to get around absence of Multiple Active Result Set (MARS) support for server side programming.

Send DataSet (December 2005)

Demonstrates how to return an ADO.NET based DataSet within a server side CLR-based stored procedure as a result set to the client.

Spatial

This sample demonstrates how to use SQL Server 2005's CLR based Table Valued Functions to implement spatial indexing which enables complex high performance spatial queries. This same technique may be used to index many other kinds of data which is not natively indexed by SQL Server for many different types of applications.

String Manipulate

This sample shows the implementation of five Transact-SQL string functions that provide the same string-manipulate functions as built-in functions, but with the additional surrogate-aware capability to handle both Unicode and surrogate strings. The five functions are: len_s(), left_s(), right_s(), sub_s(), and replace_s(). Using these functions is the same as using LEN(), LEFT(), RIGHT(), SUBSTRING(), and REPLACE() in string type functions.

String Manipulation (UTF8)

This sample demonstrates the implementation of a user-defined data type. This sample shows the implementation of a UTF-8 user-defined data type that extends the type system of the database to provide storage for UTF-8 encoded values. This type also implements code to convert Unicode strings to and from UTF-8.

String Split Table-Valued Function

This sample contains a streaming table-valued function, written in C# that splits a comma-separated string into a table with one column. It also contains an aggregate function that converts a string column to a comma-separated string.

System.Transactions (December 2005)

Demonstrates controlling transactions by using the managed APIs located in the System.Transactions namespace.

User-defined Type Sample

This sample shows creating and using a simple user-defined data type from both a Transact-SQL and a client application by using System.Data.SqlClient.

User-defined Type Utility Sample

This sample contains several utility functions that include:

·          Functions to expose assembly metadata to Transact-SQL.

·          Sample streaming table-valued functions to return the types in an assembly as a table, and also to return the fields, methods, and properties in a user-defined type.

This sample demonstrates the following technologies: streaming table valued functions, reflection APIs, and invoking table-valued functions from Transact-SQL.

UTF8 String User-Defined Data Type (UDT)

The UTF8String sample for SQL Server 2005 demonstrates the implementation of a user-defined data type. This sample shows the implementation of a UTF8 user-defined data type that extends the type system of the database to provide storage for UTF8-encoded values. This type also implements code to convert Unicode strings to and from UTF8.

 

Server Management Objects (SMO)

Sample name

Description

BackupRestore

Demonstrates how to back up and restore a database.

CheckIdentityValues (December 2005)

Performs identity checks on each table in the selected database.

CreateStoredProcs

Demonstrates how to create a SELECT stored procedure for each table in the selected database.

DatabaseDefrag (December 2005)

Demonstrates techniques for implementing database defragmentation capabilities in database applications.

DatabaseSpace (December 2005)

Demonstrates techniques for implementing database space monitoring applications.

DependencyExplorer

Demonstrates techniques for implementing an object explorer to view object dependencies within a database.

IndexSizes (December 2005)

Demonstrates techniques for implementing database index space monitoring applications.

ManageTables

Demonstrates how to create, modify, and drop tables.

LoadRegAssembly

Demonstrates how to load and register a .NET Framework assembly into an instance of SQL Server. Uses the UtilityConversion assembly.

ManageDatabases

Demonstrates how to create, modify, and drop a database. Includes adding a new file group and log file.

ManageDatabaseUsers

Demonstrates how to add, modify, and remove users.

RebuildAllIndexes (December 2005)

Rebuilds all the indexes in the selected database.

ScriptTable

This sample lets users script the creation or deletion of tables in the selected database.

ServerConnect

Demonstrates how to connect to an instance of SQL Server.

ServerInfo

Displays a list of server and connection properties for the selected instance of SQL Server.

ServiceBrokerConfiguration (December 2005)

Demonstrates how to create Service Broker objects by using SMO.

SmoBrowser

This sample allows browsing hierarchy of SMO objects using .NET reflection. It shows how exactly SMO represent database objects with all their collections and properties.

SmoCompare

This sample demonstrates techniques for implementing a comparison of two database objects.

SmoEvents

This sample demonstrates techniques for displaying SQL Server events in a console database application.

SMOPing

A console application that connects to the selected instance of SQL Server and displays selected properties. Can be used to check the SQL connection.

SmoScripter (December 2005)

Demonstrates scripting and retrieving database object dependencies.

SqlServerList

This sample demonstrates two techniques for retrieving a list of computers that are running SQL Server on the network to the list boxes.

SQLService

Displays the SQL Server services that are available to start, stop, pause, and resume. This sample uses the WMI provider. The WMI provider is not supported in SQL Server Express.

Tracer

This sample demonstrates techniques for implementing profile trace from the local instance of SQL Server to the output console.

UtilityConversion

Used with LoadRegAssembly. Contains demonstration CLR functions that can be used with SQL Server.

 

Service Broker

Note: Only the Service Broker client is included in SQL Server Express.

Sample name

Description

EventLogging

This sample shows how to use Event Notifications to log events in SQL Server. The sample creates a service that receives event notifications and a service program that receives the event notification messages and logs the information in the messages. The service program demonstrates two different ways to log event notification messages:

·          One approach extracts important information from the event notification message and saves the key information and the original message in a log table.

·         The other approach extracts all of the information from the event notification message, saves this information in a log table, and discards the original message.

Only the Service Broker client is included in SQL Server Express.

HelloWorld

This sample provides a small example that sends and receives a message by using Service Broker. The sample creates two services and sends a message from one service to the other. The sample includes a script that receives and displays the message. Only the Service Broker client is included in SQL Server Express.

HelloWorld_CLR

This sample provides a small example that sends and receives a message by using the object-oriented interface to Service Broker that is defined in the ServiceBrokerInterface sample. Only the Service Broker client is included in SQL Server Express.

ServiceBrokerInterface

This sample provides a CLR-based object oriented interface for using Service Broker. Only the Service Broker client is included in SQL Server Express.

ShoppingCart

This sample uses the conversation group identifier to maintain state for a simple shopping cart application. This sample uses the ServiceBrokerInterface sample. Only the Service Broker client is included in SQL Server Express.

 

Transact-SQL

Sample name

Description

AdventureWorks Scripts

Provides two alternatives to using the schemas in AdventureWorks. For more information, see "Schemas in AdventureWorks" in SQL Server Books Online.

Create DatabaseSnapshot

Creates a database snapshot of the AdventureWorks sample database. Is not supported in SQL Server Express.

Create FileGroups

Creates two new file groups for the AdventureWorks sample database. Is not supported in SQL Server Express.

Sliding Window Script

This sample demonstrates the ability to move partitions between tables by using the Transact-SQL ALTER TABLE SWITCH statement.

Table and Index Partitioning Script

Demonstrates table and index partitioning capabilities of SQL Server 2005. Is not supported in SQL Server Express.

 

XML

Sample name

Description

On-line Manufacturing Instructions

Retrieves manufacturing instructions, XML documents and XML illustration diagrams, and applies XSL transformation. The formatted HTML document is then shown in the browser. This sample uses CREATE ENDPOINT, which is not supported in SQL Server Express.

On-line Product Catalog

Retrieves catalog-description XML documents and product photo images, and applies XSL transformation. The formatted HTML document is shown in the browser. This sample uses CREATE ENDPOINT, which is not supported in SQL Server Express.

On-line Store Survey

Conducts an on-line reseller survey and stores the survey in the Demographics column of the Store table. This sample uses CREATE ENDPOINT, which is not supported in SQL Server Express.

 

Integrated Samples

Sample name

Description

HRResume

The HRResume sample allows the user to search for and display XML resumes by using relational and full-text techniques in a specific language. Only basic functionality is provided in this release. Additional features may be added in future releases.

Storefront E‑Commerce site

The Storefront sample provides a traditional e-commerce shopping experience for the goods sold by the fictitious Adventure Works Cycle manufacturing, wholesale, and retail organization. This sample demonstrates several different SQL Server 2005 technologies. These technologies include CLR Integration, Service Broker, business intelligence, recursive queries, and various Microsoft .NET Framework 2.0 technologies, including ASP.NET 2.0. Is not supported in SQL Server Express.

 

Replication

Sample name

Description

ActiveX Control Samples (December 2005)

This sample demonstrates how to use the legacy Replication ActiveX Controls to synchronize a subscription from a native code application.

Sales Order Sample for Merge Replication

This sample highlights how a merge replication topology can be implemented to deliver data to mobile users, and it also demonstrates the programmability features of merge replication in SQL Server 2005. The sample is a Windows Forms-based application that uses standard Microsoft data access technologies and merge replication to enable a salesperson to maintain local data while periodically synchronizing with the home office. Is not supported in SQL Server Express.

Subscriber Monitor Utility Sample for Merge Replication

The Subscriber Monitor Utility sample is a Windows application that demonstrates how the Subscriber-side monitoring functionality provided by Replication Management Objects (RMO) is used to monitor merge subscriptions at the Subscriber.

 

 

 

Also, this MSI contains some samples for the following technology areas. These areas currently are not supported for SQL Server Express, but are included for customers of other editions of SQL Server and to demonstrate the capabilities of the other editions of SQL Server.

 

Technology

Description

Analysis Services

Microsoft SQL Server 2005 Analysis Services uses both server and client components to supply online analytical processing (OLAP) and data mining functionality for business intelligence applications.

Full-Text Search

Full-Text Search is the functionality you need to issue full-text queries against plain character-based data in Microsoft SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.

Integration Services

Microsoft SQL Server 2005 Integration Services (SSIS) is an environment for building high-performance data-integration solutions that include the extraction, transformation, and load (ETL) packages for data warehousing.

Notification Services

Microsoft SQL Server Notification Services is a platform for developing and deploying applications that generate and send notifications to subscribers. The notifications generated are personalized, timely messages that can be sent to a wide range of devices, and that reflect the preferences of the subscriber.

Reporting Services

Microsoft SQL Server 2005 Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources.

 

 

Ten Things You Need To Know To Get Started With Samples

1 – The Microsoft SQL 2005 Samples MSI installs over 100 samples in 6 major categories: Analysis Services, Engine (includes Administration, Data Access, Full Text Search, Common Language Runtime Integration, Server Management Objects, Service Broker, and XML), Integration Services, Notification Services, Replication, and Reporting Services.  There is also a directory “Integrated Samples” for the two multiple feature samples, HRResume and Storefront.  Each individual sample has its own readme file, which is also included in the Microsoft SQL Server 2005 Books Online.  Most samples contain a directory structure similar to the following, with the C# files in a CS folder, the Transact-SQL files in a Scripts folder, and the Visual Basic files in a VB folder:

            SampleName (usually includes the readme only)

                        CS (includes the solution file)

                                    ProjectFolder1

                                    ProjectFolderN

                        Scripts

                        VB (includes the solution file)

                                    ProjectFolder1

                                    ProjectFolderN

2 – Make sure you read the readme file in the sample directory.  It will tell you the prerequisites for the sample as well as the steps to build and run the sample.

3 – To install the samples and sample databases from the SQL Server 2005 Setup, you need to click the Advanced button on the Components to Install page, then expand Books Online and Samples and select Samples.  This only installs the Samples MSI on your machine.  To complete the installation, you need to run the Samples MSI.  You can do this from the Start menu: click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.

4 – Most samples assume that you installed them in the default location (drive:\Program Files\Microsoft SQL Server\90\Samples).  If you choose a different location, you’ll likely have to change the code in order for a sample to work.

5 – Many samples require the AdventureWorks sample databases.  You can install these during SQL 2005 Setup, or you can download them from the SQL Server Developer Center at http://msdn.microsoft.com/sql/downloads/samples/

6 – Many samples require .NET Framework 2.0.  If you don’t have Visual Studio 2005, you can download the .NET Framework SDK 2.0 at http://msdn.microsoft.com/netframework/downloads/

7 – Some samples use the CLR integration feature, which is off by default.  Use the SQL Surface Area Configuration tool to enable CLR integration.  This can be found in the Start menu: click All Programs, click Microsoft SQL Server 2005, click Configuration Tools, and then click SQL Server Surface Area Configuration.

8 – Some samples will not work in the SQL Express edition because certain product features, including Analysis Services and Integration Services, are not available.  You will need to purchase the Standard or Enterprise editions to run these samples.

9 – Many samples require you to create a sample key file before they can be successfully compiled.  See the sample’s readme file for exact instructions.  For most samples in the RTM release, you will need to do the following:

 a. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.

                   -- or --

Open a Microsoft .NET Framework command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

b. Use the change directory (CD) command to change the current directory of the command prompt window to the sample folder, such as <system_drive>:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\HelloWorld

c. At the command prompt, run the following command to generate the key file:

                   sn –k keypair.snk

For samples updated after RTM, most of the samples use the same key file. To generate a strong name key file:

 a. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.

                   -- or --

Open a Microsoft .NET Framework command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

b. Use the change directory (CD) command to change the current directory of the command prompt window to the folder where the samples are installed.

Note: To determine the folder where samples are located, click the Start button, point to All Programs, point to Microsoft SQL Server 2005, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\90\Samples.

          c. At the command prompt, run the following command to generate the key file:

                   sn –k SampleKey.snk

10 – Samples are not intended to be used in a production environment; however, you are free to re-use sample code in your applications.