Microsoft SQL Server Product Samples: Scripts

New Post: Sql Scripts

edlehman — Fri, 13 Jun 2008 19:22:49 GMT

Hi Jaron,

This would be a good question to post on our TSQL Forum.

JARON wrote:
Hi all,
i'm updating a new 'commun management' software n i wanna update the database bcoz i added many extra tables.
i did write scripts for all the tables this way:

Create...........
Drop...........
Alter........

so our clients would be able to have access to it.
but i have been asked to write other ways script than using 'alter', becoz its like im having the database for the first time and our clients have the older version...
Any Ideas??

10x in advance

Source code checked in, #11622

ReedMe — Wed, 11 Jun 2008 05:42:45 GMT

Adding 'Katmai:June 2008 RC0'

Updated Wiki: SS2005!Readme_Snapshot

bonniefe — Wed, 11 Jun 2008 00:32:11 GMT

Readme_Snapshot

6/10/2008 4:38:07 PM

The purpose of the DatabaseSnapshot sample is to demonstrate the database snapshot capabilities of Microsoft SQL Server 2005. For information about this new feature, refer to the topic, "Database Snapshots" in SQL Server Books Online.

Scenario

A database snapshot is a read-only, static view of a database. This database is called the source database. Each database snapshot is transaction-consistent with the source database at the moment of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner. Multiple snapshots can exist on a database. This sample creates a new database snapshot of the AdventureWorks sample database.

Languages

Transact-SQL

Prerequisites

Before running either of the scripts included in this sample, make sure the following software is installed:

SQL Server 2005, including the following components:
Database Engine
SQL Server Management Studio
AdventureWorks (OLTP) sample database
SQL Server Engine samples

Running the Sample

Installing and Running the Sample

From SQL Server Management Studio, open the file CreateDatabaseSnapshot.sql script located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\DatabaseSnapshot\Scripts folder, and click Execute. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2008!Readme_SlidingWindow

bonniefe — Wed, 11 Jun 2008 00:31:41 GMT

Readme_SlidingWindow Sample

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The purpose of the SlidingWindow sample is to demonstrate the ability to move partitions between tables by using the Transact-SQL ALTER TABLE SWITCH statement. For information about this feature, see "Designing Partitions to Manage Subsets of Data" in SQL Server Books Online.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications should not be connected to or used with your production SQL Server database without the permission of the system administrator.

Scenarios

The sample moves the oldest month of data from the partitioned table TransactionHistory to the partitioned table TransactionHistoryArchive. Before running the partition switch, the partition function for TransactionHistoryArchive is modified to receive the new partition. Also a CHECK constraint is added to table TransactionHistory. Following the partition switch, both the TransactionHistory table and the TransactionHistoryArchive table are restored to a partitioning structure that would allow for a similar plan to run the following month.

Languages

Transact-SQL

Prerequisites

Before you run this sample, follow these steps.

Install SQL Server and make sure that you include the following components:
Database Engine
SQL Server Management Studio
Download the AdventureWorks (OLTP) sample database and SQL Server Database Engine samples available at the Microsoft SQL Server Samples and Community Projects Web site.

Running the Sample

The following procedure shows how to run the SlidingWindow sample.

To run the SlidingWindow sample

Run the PartitioningScript sample. For more information, see Readme_PartitioningScript Sample.
In SQL Server Management Studio, open the file Sliding.sql. The default installation directory is C:\Program Files\Microsoft SQL Server\100\Samples\Engine\\Administration\SlidingWindow\Scripts, and then click Execute. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2008!Readme_PartitioningScript

bonniefe — Wed, 11 Jun 2008 00:30:53 GMT

Readme_PartitioningScript Sample

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The purpose of the PartitioningScript sample is to demonstrate the table and index partitioning capabilities of SQL Server. For information about this feature, see "Partitioned Tables and Indexes" in SQL Server Books Online.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications should not be connected to or used with your production SQL Server database without the permission of the system administrator.

Scenario

In the AdventureWorks sample database, the TransactionHistory table is used to record each sales order, purchase order, and work order transaction. Therefore, this table is one of the largest and most heavily accessed tables in the database. The TransactionHistoryArchive table was created to store older transactions from the TransactionHistory table. Data is transferred to TransactionHistoryArchive on a monthly basis for additional analysis enabling TransactionHistory to stay reasonably small and maintain current data only.
To create the partitions for these tables, the sample performs these operations:

Creates partition function TransactionRangePF1 on the TransactionDate column of the TransactionHistory table, so that each partition contains one month of data.
Creates partition scheme TransactionsPS1 to map the partitions to filegroups. In this sample, all partitions reside on the same filegroup.
Drops and re-creates the TransactionHistory table specifying the partition scheme TransactionsPS1 as the location for the table.
Creates partition function TransactionRangePF2 on the TransactionDate column of the TransactionHistoryArchive table.
Creates partition scheme TransactionsPS2 to map the partitions to filegroups. In this sample, all partitions reside on the same filegroup.
Drops and re-creates the TransactionHistoryArchive table specifying the partition scheme TransactionsPS2 as the location for the table.
Bulk inserts data into the two tables. The transfer of monthly "chunks" of data between the tables is performed by switching partitions between the two tables. This typically takes just seconds, instead of minutes or hours in earlier releases, because it is a metadata operation only instead of a physical relocation of the data. Partition switching is demonstrated in the SlidingWindow sample.

Languages

Transact-SQL

Prerequisites

Before you run this sample, perform the following tasks:

Install SQL Server and make sure you include the following components:
Database Engine
SQL Server Management Studio
Download the AdventureWorks (OLTP) sample database and Database Engine samples available at the Microsoft SQL Server Samples and Community Projects Web site.

Running the Sample

The following procedure shows how to run the PartitioningScript sample.

To run the PartitioningScript sample

In SQL Server Management Studio, open the file PartitionAW.sql. The default installation directory is C:\Program Files\Microsoft SQL Server\100\Samples\Engine\\Administration\Partitioning\Scripts. Click Execute. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2008!Readme_FileGroups

bonniefe — Wed, 11 Jun 2008 00:29:39 GMT

Readme_FileGroups Sample

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The purpose of this sample is to demonstrate filegroup and file capabilities in SQL Server. For more information, see "Designing Files and Filegroups" in SQL Server Books Online.

Scenario

Filegroups allow files to be grouped together for administrative and data allocation/placement purposes. For example, filegroups allow placement of objects on specific physical disks, which can improve performance due to parallel disk I/O searching. Also, files and filegroups can be backed up and restored individually, which can increase the speed of recovery by allowing you to restore only damaged files without restoring the rest of the database.
This sample creates:

The WorkOrderGroup filegroup and the data file WorkOrders and then moves the Production.WorkOrder and Production.WorkOrderRouting tables to that filegroup in the AdventureWorks sample database.
The TransactionHistoryGroup filegroup and data file TransactionHistory and then moves the TransactionHistory and TransactionHistoryArchive tables to that filegroup in the AdventureWorks sample database.

Languages

Transact-SQL

Prerequisites

Before you run this sample, perform the following tasks:

Install SQL Server and make sure you include the following components:
The Database Engine
SQL Server Management Studio
Download the AdventureWorks (OLTP) sample database and Database Engine samples available at the Microsoft SQL Server Samples and Community Projects Web site.

Running the Sample

The following procedure shows how to run the CreateFileGroups.sql Transact-SQL script that creates files and filegroups in the AdventureWorks sample database.

Running the CreateFilegroups Sample

From SQL Server Management Studio, open the file CreateFileGroups.sql, located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\FileGroups folder, and click Execute. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2005!Readme_FileGroups

bonniefe — Wed, 11 Jun 2008 00:29:00 GMT

Readme_FileGroups

6/10/2008 4:38:07 PM

The purpose of this sample is to demonstrate filegroup and file capabilities in Microsoft SQL Server 2005. For information about this feature, refer to the topic, "Designing Files and Filegroups" in SQL Server Books Online.

Scenario

The WorkOrderGroup filegroup and the data file WorkOrders and then moves the Production.WorkOrder and Production.WorkOrderRouting tables to that filegroup in the AdventureWorks sample database.
The TransactionHistoryGroup filegroup and data file TransactionHistory and then moves the TransactionHistory and TransactionHistoryArchive tables to that filegroup in the AdventureWorks sample database.

Languages

Transact-SQL

Prerequisites

Before running the sample, make sure the following software is installed:

SQL Server 2005, including the following components:
Database Engine
SQL Server Management Studio
AdventureWorks (OLTP) sample database
SQL Server Engine samples

Running the Sample

The following procedure shows how to run the CreateFileGroups.sql Transact-SQL script that creates files and filegroups in the AdventureWorks sample database.

Running the CreateFilegroups Sample

From SQL Server Management Studio, open the file CreateFileGroups.sql script located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\FileGroups\Scripts folder, and click Execute. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2008!Readme_Alerts

bonniefe — Wed, 11 Jun 2008 00:28:31 GMT

Readme_Alerts Sample

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The purpose of the Alerts sample is to demonstrate the alert capabilities of SQL Server. For more information, see "Defining Alerts" in SQL Server Books Online.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications and assemblies should not be connected to or used with your production SQL Server database or your report server without the permission of the system administrator.

Important:

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications and assemblies should not be connected to or used with your production SQL Server database or your report server without the permission of the system administrator.

Scenario

This sample script file creates SQL Server Agent alerts in SQL Server.
After the execution of this script you will find new alerts added to the alerts collection. You can examine them in SQL Server Management Studio or by calling sp_help_alert stored procedure.

Languages

Transact-SQL

Prerequisites

Before you run this sample, perform the following tasks:

Install SQL Server and make sure you include the following components:
The Database Engine
SQL Server Management Studio
Download the AdventureWorks (OLTP) sample database and Database Engine samples available at the Microsoft SQL Server Samples and Community Projects Web site.

Running the Sample

The following procedure shows how to run the Alerts sample.

Running the Alerts Sample

From Management Studio, open the agentsamplealerts.sql script. The default installation directory is C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\Alerts\Scripts.
Click Execute. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2005!Readme_Alerts

bonniefe — Wed, 11 Jun 2008 00:27:56 GMT

Readme_Alerts

6/10/2008 4:38:07 PM

The purpose of the Alerts sample is to demonstrate the alert capabilities of Microsoft SQL Server 2005. For information about this new feature, refer to the topic, "Defining Alerts" in SQL Server Books Online.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications should not be connected to or used with your production SQL Server database without the permission of the system administrator.

Scenario

This sample script file creates SQL Agent alerts in Microsoft SQL Server 2005.
After the execution of this script you will find new alerts added to the alerts collection. You could examine them in Management Studio or by calling sphelpalert stored procedure.

Languages

Transact-SQL

Prerequisites

Before running this sample, make sure the following software is installed:

SQL Server 2005, including the following components:
Database Engine
SQL Server Management Studio
SQL Server Engine samples

Running the Sample

The following procedure shows how to run the Alerts sample.

Running the Alerts Sample

From SQL Server Management Studio, open the agentsamplealerts.sql script. The default installation directory is drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\Alerts\Scripts.
Click Execute.

Updated Wiki: SS2008!Readme_AdventureWorksScripts

bonniefe — Wed, 11 Jun 2008 00:27:36 GMT

Readme_AdventureWorksScripts Sample

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
In SQL Server, scripts are available in SQLServerEngineSamples.msi that provide two alternatives to using the schemas in the AdventureWorks sample database.

Scenario

In SQL Server, tables and other schema-scoped objects are contained in schemas, and the schemas are owned by users. In AdventureWorks, five schemas are used to contain schema-scoped objects that are based on business functionality. For example, customer and sales-related objects are contained in the Sales schema; employee-related objects are contained in the HumanResources schema, and so on. For more information, see "Schemas in AdventureWorks" in SQL Server Books Online.
The addition of schemas introduces changes to the way in which you access tables and other schema-scoped objects. To access objects in a schema other than the default schema (DB), at a minimum, a two-part identifier in the form schemaname.objectname must be specified. This is true for all DDL and DML statements that reference schema-scoped objects.
The scripts included in this sample provide alternatives to working with schema-scoped objects. One alternative transfers all schema-scoped objects to the dbo schema and the other creates synonyms for each schema-scoped object in the dbo schema.

Languages

Transact-SQL

Features

The AdventureWorks scripts use the following features of the Database Engine.

Application area	Features
Database Engine	Schema DDL
Database Engine	Synonyms

Prerequisites

Before you run this sample, perform the following tasks:

Install SQL Server and make sure you include the following components:
The Database Engine
SQL Server Management Studio
Download the AdventureWorks (OLTP) sample database and Database Engine samples available at the Microsoft SQL Server Samples and Community Projects Web site.

Transferring Objects to the dbo Schema

The AlterSchemaToDbo.sql script transfers every schema-scoped object in AdventureWorks to the dbo schema. After this script has been run, users who have a default schema of dbo will not have to use a two-part identifier when they reference these objects in DDL and DML statements.

Important:
The code examples and samples provided with SQL Server will not run after the AlterSchemaToDbo.sql is executed unless the schema names specified in the code are replaced with dbo or removed.

The AlterSchemaFromDbo.sql script transfers (returns) the objects from the dbo schema to the schemas they were in before running the AlterSchemaToDbo.sql script.

Running the Scripts to Transfer Objects to and from the dbo Schema

The following procedure shows how to run the Transact-SQL script that transfers AdventureWorks schema-scoped objects to the dbo schema.

To run the AlterSchemaToDbo script

In SQL Server Management Studio, open the file AlterSchemaToDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.The following procedure shows how to run the Transact-SQL script that returns AdventureWorks schema-scoped objects from the dbo schema to their original schemas.

To run the AlterSchemaFromDbo scripts

In SQL Server Management Studio, open the file AlterSchemaFromDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.

Using Synonyms

A synonym is an alternative name given to a schema-scoped object. The synonym is specified instead of the base object in DDL and DML statements.
The CreateSynonymsDbo.sql script creates a synonym for each schema-scoped object in AdventureWorks. The synonym name is the same as the base object name, but uses the dbo schema. For example, the synonym for *HumanResources.Department *is dbo.Department.
Using synonyms has the following advantages:

*
If dbo is the default schema, a two-part identifier is not needed to specify these objects in DDL and DML statements.

The code examples and samples provided with SQL Server can be used without modification. The DropSynonymsDbo.sql script drops the synonyms that are created by the CreateSynonymsDbo.sql script.

Running the Synonym Scripts

The following procedure shows how to run the Transact-SQL script that creates synonyms in the dbo schema for each AdventureWorks schema-scoped object.

To run the CreateSynonymsDbo script

In SQL Server Management Studio, open the file CreateSynonymsDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.The following procedure shows how to run the Transact-SQL script that removes the synonyms that are created by the CreateSynonymsDbo.sql script from the database.

To run the DropSynonymsDbo script

In SQL Server Management Studio, open the file DropSynonymsDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.

Removing the Scripts

Use the following procedure to remove one or more scripts that are included in this sample.

To remove the scripts

In Windows Explorer, locate the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, right-click the script name and click Delete. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2005!Readme_AdventureWorksScripts

bonniefe — Wed, 11 Jun 2008 00:26:57 GMT

Readme_AdventureWorksScripts

6/10/2008 4:38:07 PM

In Microsoft SQL Server 2005, scripts are available in SQLServerEngineSamples.msi that provide two alternatives to using the schemas in the AdventureWorks sample database.

Scenario

In SQL Server 2005, tables and other schema-scoped objects are contained in schemas, and the schemas are owned by users. In AdventureWorks, five schemas are used to contain schema-scoped objects that are based on business functionality. For example, customer and sales-related objects are contained in the Sales schema; employee-related objects are contained in the HumanResources schema, and so on. For more information, see "Schemas in AdventureWorks" in SQL Server Books Online.
The addition of schemas introduces changes to the way in which you access tables and other schema-scoped objects. To access objects in a schema other than the default schema (DB), at a minimum, a two-part identifier in the form schemaname.objectname must be specified. This is true for all DDL and DML statements that reference schema-scoped objects.
The scripts included in this sample provide alternatives to working with schema-scoped objects. One alternative transfers all schema-scoped objects to the dbo schema and the other creates synonyms for each schema-scoped object in the dbo schema.

Languages

Transact-SQL

Features

The AdventureWorks scripts use the following features of the SQL Server 2005 Database Engine.

Application area	Features
Database Engine	Schema DDL
Database Engine	Synonyms

Prerequisites

Before you run either of the scripts included in this sample, install SQL Server 2005 and make sure you include the following components:

Database Engine
SQL Server Management Studio
The AdventureWorks database which is included with SQL Server 2005, and is also available at the SQL Server DeveloperWeb site.
The SQL Server 2005 Database Engine samples. These samples are included with SQL Server 2005. You can download the latest version of the samples at the SQL Server Developer Web site.

Transferring Objects to the dbo Schema

Important:
The code examples and samples provided with SQL Server 2005 will not run after the AlterSchemaToDbo.sql is executed unless the schema names specified in the code are replaced with dbo or removed.

The AlterSchemaFromDbo.sql script transfers (returns) the objects from the dbo schema to the schemas they were in before running the AlterSchemaToDbo.sql script.

Running the Scripts to Transfer Objects to and from the dbo Schema

The following procedure shows how to run the Transact-SQL script that transfers AdventureWorks schema-scoped objects to the dbo schema.

To run the AlterSchemaToDbo script

In SQL Server Management Studio, open the file AlterSchemaToDbo.sql script located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.The following procedure shows how to run the Transact-SQL script that returns AdventureWorks schema-scoped objects from the dbo schema to their original schemas.

To run the AlterSchemaFromDbo scripts

In SQL Server Management Studio, open the file AlterSchemaFromDbo.sql script located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.

Using Synonyms

If dbo is the default schema, a two-part identifier is not needed to specify these objects in DDL and DML statements.
The code examples and samples provided with SQL Server 2005 can be used without modification. The DropSynonymsDbo.sql script drops the synonyms that are created by the CreateSynonymsDbo.sql script.

Running the Synonym Scripts

The following procedure shows how to run the Transact-SQL script that creates synonyms in the dbo schema for each AdventureWorks schema-scoped object.

To run the CreateSynonymsDbo script

In SQL Server Management Studio, open the file CreateSynonymsDbo.sql script located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.The following procedure shows how to run the Transact-SQL script that removes the synonyms that are created by the CreateSynonymsDbo.sql script from the database.

To run the DropSynonymsDbo script

In SQL Server Management Studio, open the file DropSynonymsDbo.sql script located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.

Removing the Scripts

Use the following procedure to remove one or more scripts that are included in this sample.

To remove the scripts

In Windows Explorer, locate the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\AdventureWorks\Scripts folder, right-click the script name and click Delete. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2008!Event Notifications Sample

bonniefe — Wed, 11 Jun 2008 00:26:18 GMT

Readme_Event Notifications Sample

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The event notification sample (EventNotificationSample.sql) provides an example of how event notifications can be used in an OLTP database application. For more information, see Event Notifications (Database Engine).

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications should not be connected to or used with your production SQL Server database without the permission of the system administrator.

Scenario

Event notifications can be used to monitor and respond to database activity. EventNotificationSample.sql monitors the following kinds of events:

CreateDatabaseNotification event notification monitors when databases are created and dropped on the server instance.
AuditLoginLogoutNotification event notification monitors when logins, logouts, and failed logins occur on the server instance. The event notifications send XML data about these events to the Microsoft Service Broker service specified in the sample. The sample also defines a queue to receive the messages and a route that specifies the service address; in this case, the address is the local database. A WAITFOR clause, which is enclosed in commenting characters, instructs the server to wait 60 seconds until the service receives the message and casts it as XML. Additional scripts, which are also enclosed in commenting characters, test the event notifications by causing the events on which they are created to occur on the server instance.

Languages

Transact-SQL

Prerequisites

Before you run this sample, follow these steps:

Install SQL Server and make sure that you include the following components:
Database Engine
SQL Server Management Studio
Download the AdventureWorks (OLTP) sample database and Database Engine samples available at the Microsoft SQL Server Samples and Community Projects Web site.

Running the Sample

The following procedure shows how to install and run the event notification sample.

To run the event notification sample

From SQL Server Management Studio, open EventNotificationSample.sql. The default installation directory is C:\Program Files\Microsoft SQL Server\100\Samples\Engine\\Administration\EventNotifications\Scripts\.

Updated Wiki: SS2005!Event Notifications Sample

bonniefe — Wed, 11 Jun 2008 00:25:43 GMT

Event Notifications Sample

6/10/2008 4:38:07 PM

The event notification sample (EventNotificationSample.sql) provides an example of how event notifications can be used in an OLTP database application. For more information, see Event Notifications (Database Engine).

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications should not be connected to or used with your production SQL Server database without the permission of the system administrator.

Scenario

Event notifications can be used to monitor and respond to database activity. EventNotificationSample.sql monitors the following kinds of events:

CreateDatabaseNotification event notification monitors when databases are created and dropped on the server instance.
AuditLoginLogoutNotification event notification monitors when logins, logouts, and failed logins occur on the server instance. The event notifications send XML data about these events to the Microsoft SQL Server 2005 Service Broker service specified in the sample. The sample also defines a queue to receive the messages and a route that specifies the service address; in this case, the address is the local database. A WAITFOR clause, which is enclosed in commenting characters, instructs the server to wait 60 seconds until the service receives the message and casts it as xml. Additional scripts, which are also enclosed in commenting characters, test the event notifications by causing the events on which they are created to occur on the server instance.

Languages

Transact-SQL

Prerequisites

Before running this sample, make sure the following software is installed:

Microsoft SQL Server 2005 with the following components:
SQL Server 2005 Database Engine
SQL Server Management Studio
AdventureWorks (OLTP) sample database which is included with SQL Server 2005, and is also available at the Microsoft SQL Server Developer Center.

*

SQL Server Database Engine samples (SQLServerSample.msi). These samples are included with SQL Server 2005. You can download the latest version of the samples at the Microsoft SQL Server Developer Center.

Running the Sample

The following procedure shows how to install and run the event notification sample.

To run the event notification sample

From SQL Server Management Studio, open EventNotificationSample.sql. The default installation directory is C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\EventNotifications\Scripts\.
Click Execute.

Updated Wiki: Home

bonniefe — Wed, 11 Jun 2008 00:25:14 GMT

SQL Server Samples Portal Page > SQL Server Scripts Product Samples

This project contains Scripts samples released with Microsoft SQL Server product. Below is the list of these samples. If you are interested in contributing Scripts samples, please let us know by posting in the developers' forum.

To download these samples click on the Releases tab (or click here), optionally select another (older) release, then click on the Windows Installer package (MSI file) which matches the architecture of the installation computer.

For information about how to use CodePlex to access SQL Server samples, click here.
To download samples databases, click here and then click the appropriate MSI file.
For release notes and brief descriptions of all the sample databases and code, including restrictions on SQL Express and Vista, click here.

Sample	SQL Server 2005	SQL Server 2008
Event Notifications Sample	SS2005	SS2008
AdventureWorksScripts	SS2005	SS2008
Alerts	SS2005	SS2008
FileGroups	SS2005	SS2008
PartitioningScript	SS2005	SS2008
SlidingWindow	SS2005	SS2008
Snapshot	SS2005	SS2008

Updated Wiki: Home

ReedMe — Tue, 10 Jun 2008 23:52:05 GMT

SQL Server Samples Portal Page > SQL Server Scripts Product Samples

SQL Server 2008 RC0 product samples are now available.

This project contains Scripts samples released with Microsoft SQL Server product. Below is the list of these samples. If you are interested in contributing Scripts samples, please let us know by posting in the developers' forum.

To download these samples click on the Releases tab (or click here), optionally select another (older) release, then click on the Windows Installer package (MSI file) which matches the architecture of the installation computer.

For information about how to use CodePlex to access SQL Server samples, click here.
To download samples databases, click here and then click the appropriate MSI file.
For release notes and brief descriptions of all the sample databases and code, including restrictions on SQL Express and Vista, click here.

Sample	SQL Server 2005	SQL Server 2008
Event Notifications Sample	SS2005	SS2008
AdventureWorksScripts	SS2005	SS2008
Alerts	SS2005	SS2008
FileGroups	SS2005	SS2008
PartitioningScript	SS2005	SS2008
SlidingWindow	SS2005	SS2008
Snapshot	SS2005	SS2008

UPDATED RELEASE: SQL Server 2008 RC0 (Jun 10, 2008)

Tue, 10 Jun 2008 23:35:45 GMT

This release contains the SQL Server 2008 RC0 product samples for T-SQL scripting.

RELEASED: SQL Server 2008 RC0 (Jun 10, 2008)

Tue, 10 Jun 2008 23:35:45 GMT

This release contains the SQL Server 2008 RC0 product samples for T-SQL scripting.

NEW POST: Sql Scripts

JARON — Thu, 05 Jun 2008 08:51:01 GMT

Hi all,
i'm updating a new 'commun management' software n i wanna update the database bcoz i added many extra tables.
i did write scripts for all the tables this way:

Create...........
Drop...........
Alter........

so our clients would be able to have access to it.
but i have been asked to write other ways script than using 'alter', becoz its like im having the database for the first time and our clients have the older version...
Any Ideas??

10x in advance

UPDATED WIKI: SS2008!Readme_Snapshot

bonniefe — Wed, 20 Feb 2008 07:27:25 GMT

Readme_Snapshot Sample

2/19/2008 9:32:55 PM
[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The purpose of the DatabaseSnapshot sample is to demonstrate the database snapshot capabilities of SQL Server. For more information, see "Database Snapshots" in SQL Server Books Online.

Scenario

Languages

Transact-SQL

Prerequisites

Before you run this sample, perform the following tasks:

Install SQL Server and make sure you include the following components:
The Database Engine
SQL Server Management Studio
Download the AdventureWorks (OLTP) sample database and SQL Server Database Engine samples available at the Microsoft SQL Server Samples and Community Projects Web site.

Running the Sample

Installing and Running the Sample

From Management Studio, open the file CreateDatabaseSnapshot.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\DatabaseSnapshot\Scripts folder, and click Execute. © 2008 Microsoft Corporation. All rights reserved.

UPDATED WIKI: SS2005!Readme_Snapshot

bonniefe — Wed, 20 Feb 2008 07:27:09 GMT

Readme_Snapshot

2/19/2008 11:18:35 PM

The purpose of the DatabaseSnapshot sample is to demonstrate the database snapshot capabilities of Microsoft SQL Server 2005. For information about this new feature, refer to the topic, "Database Snapshots" in SQL Server Books Online.

Scenario

Languages

Transact-SQL

Prerequisites

Before running either of the scripts included in this sample, make sure the following software is installed:

SQL Server 2005, including the following components:
Database Engine
SQL Server Management Studio
AdventureWorks (OLTP) sample database
SQL Server Engine samples

Running the Sample

Installing and Running the Sample

From SQL Server Management Studio, open the file CreateDatabaseSnapshot.sql script located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\DatabaseSnapshot\Scripts folder, and click Execute. © 2008 Microsoft Corporation. All rights reserved.

Microsoft SQL Server Product Samples: Scripts

New Post: Sql Scripts

Source code checked in, #11622

Updated Wiki: SS2005!Readme_Snapshot

Readme_Snapshot

Scenario

Languages

Prerequisites

Running the Sample

Installing and Running the Sample

Updated Wiki: SS2008!Readme_SlidingWindow

Readme_SlidingWindow Sample

Scenarios

Languages

Prerequisites

Running the Sample

To run the SlidingWindow sample

Updated Wiki: SS2008!Readme_PartitioningScript

Readme_PartitioningScript Sample

Scenario

Languages

Prerequisites

Running the Sample

To run the PartitioningScript sample

Updated Wiki: SS2008!Readme_FileGroups

Readme_FileGroups Sample

Scenario

Languages

Prerequisites

Running the Sample

Running the CreateFilegroups Sample

Updated Wiki: SS2005!Readme_FileGroups

Readme_FileGroups

Scenario

Languages

Prerequisites

Running the Sample

Running the CreateFilegroups Sample

Updated Wiki: SS2008!Readme_Alerts

Readme_Alerts Sample

Scenario

Languages

Prerequisites

Running the Sample

Running the Alerts Sample

Updated Wiki: SS2005!Readme_Alerts

Readme_Alerts

Scenario

Languages

Prerequisites

Running the Sample

Running the Alerts Sample

See Also

Tasks

Help and Information

Updated Wiki: SS2008!Readme_AdventureWorksScripts

Readme_AdventureWorksScripts Sample

Scenario

Languages

Features

Prerequisites

Transferring Objects to the dbo Schema

Running the Scripts to Transfer Objects to and from the dbo Schema

To run the AlterSchemaToDbo script

To run the AlterSchemaFromDbo scripts

Using Synonyms

Running the Synonym Scripts

To run the CreateSynonymsDbo script

To run the DropSynonymsDbo script

Removing the Scripts

To remove the scripts

Updated Wiki: SS2005!Readme_AdventureWorksScripts

Readme_AdventureWorksScripts

Scenario

Languages

Features

Prerequisites

Transferring Objects to the dbo Schema

Running the Scripts to Transfer Objects to and from the dbo Schema

To run the AlterSchemaToDbo script