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.
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
XML
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:
·
Server Management Objects (SMO):
·
Replication
Sample name |
Description |
|
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. |
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 |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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
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
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.