AdventureWorks Sample Database

The AdventureWorksDB.msi contains the AdventureWorks database files. Run the MSI to extract the database files. 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 Express Manager 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.

SQL Server Samples

The SqlServerSamples.msi contains samples for Microsoft SQL Server 2005 CTP. 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.

Unless noted in the description, all the following samples will run on SQL Server Express. To execute the Transact-SQL scripts, 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.

Technology	Sample name	Description
ADO.NET	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.
ADO.NET	MARS	Demonstrates issuing multiple commands in parallel on the same connection.
Full-Text Search	ItemFinder	Demonstrates new features in Full-Text Search, and best practices for efficiently locating data, caching, paging, and adding large object (LOB) data to the database. Is not supported in SQL Server Express.
Adventure Works Cycles Integrated Samples	HRResume and Storefront E‑Commerce site	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. 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.
Notification Services	ProfitMargin	The ProfitMargin sample is an event-driven Notification Services application that produces notifications about profit margins for individual employees and sends this data to subscribed users. This sample uses the Analysis Services event provider to query an Analysis Services database and return the results as events to the Notification Services application. Is not supported in SQL Server Express.
Notification Services	Flight	The Flight sample is an event-driven Notification Services application that produces notifications about flight prices for subscribed users, according to their subscriptions. Is not supported in SQL Server Express.
Notification Services	Newsletter	The Newsletter sample shows how to create a simple Web-based subscription management application. The sample has an n-tier design that contains an HTML-based interface layer and a subscription management middle-tier layer. A Microsoft Visual Studio® Web project creates a Visual Web Developer Web Server when you run the Web project so that you can more easily use the sample. Is not supported in SQL Server Express.
Notification Services	Stock	The Stock sample is a Notification Services application that uses both event-driven and scheduled subscriptions to produce notifications based on stock market data. Subscribed users receive notifications when the stock price goes above a specified trigger value. Is not supported in SQL Server Express.
Replication	Merge	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.
Service Broker	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.
Service Broker	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.
Service Broker	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.
Service Broker	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.
Service Broker	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.
SQL Server Management Objects (SMO)	BackupRestore	Demonstrates how to back up and restore a database.
SQL Server Management Objects (SMO)	ManageDatabases	Demonstrates how to create, modify, and drop a database. Includes adding a new file group and log file.
SQL Server Management Objects (SMO)	CreateStoredProcs	Demonstrates how to create a SELECT stored procedure for each table in the selected database.
SQL Server Management Objects (SMO)	ManageTables	Demonstrates how to create, modify, and drop tables.
SQL Server Management Objects (SMO)	ManageDatabaseUsers	Demonstrates how to add, modify, and remove users.
SQL Server Management Objects (SMO)	LoadRegAssembly	Demonstrates how to load and register a .NET Framework assembly into an instance of SQL Server. Uses the UtilityConversion assembly.
SQL Server Management Objects (SMO)	ServerConnect	Demonstrates how to connect to an instance of SQL Server.
SQL Server Management Objects (SMO)	ServerInfo	Displays a list of server and connection properties for the selected instance of SQL Server.
SQL Server Management Objects (SMO)	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.
SQL Server Management Objects (SMO)	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.
SQL Server Management Objects (SMO)	UtilityConversion	Used with LoadRegAssembly. Contains demonstration CLR functions that can be used with SQL Server.
CLR Integration	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.
CLR Integration	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.
CLR Integration	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.
CLR Integration	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.
CLR Integration	CLR String Utility Sample	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.
CLR Integration	In-Process Data Access Sample	This sample contains several simple functions that demonstrate various features of the CLR Integration in-process data access provider.
CLR Integration	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.
CLR Integration	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.
Transact-SQL	AdventureWorks Scripts	Provides two alternatives to using the schemas in AdventureWorks. For more information, see "Schemas in AdventureWorks" in SQL Server Books Online.
Transact-SQL	Create DatabaseSnapshot	Creates a database snapshot of the AdventureWorks sample database. Is not supported in SQL Server Express.
Transact-SQL	Create FileGroups	Creates two new file groups for the AdventureWorks sample database. Is not supported in SQL Server Express.
Transact-SQL	Sliding Window Script	This sample demonstrates the ability to move partitions between tables by using the Transact-SQL ALTER TABLE SWITCH statement.
Transact-SQL	Table and Index Partitioning Script	Demonstrates table and index partitioning capabilities of SQL Server 2005. Is not supported in SQL Server Express.
XML	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.
XML	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.
XML	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.

Also, this MSI contains some samples for the following technology areas. These areas currently are not supported for SQL Server Express, but are included 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.
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.