SMOTableList Sample

  • Article

The SmoTableList sample illustrates how to enumerate the user tables in the AdventureWorks database. A Script task retrieves the schema and table names and puts them into two variables. Next, a Transact-SQL task executes a DBCC CHECKCONSTRAINTS command using the two variables. This sample is not supported on IA64. This sample requires SQL Server 2005 SP1.

For more information, see the topic, "Foreach Loop Container", in SQL Server 2005 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.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section Testing the Sample, and read about how to configure and run the code sample.

Prerequisites

Running this sample package requires the following:

  • .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See the Books Online topic, Installing the .NET Framework SDK.
  • Microsoft SQL Server 2005 Integration Services
  • The sample package and data files that it uses must be installed on the local hard disk drive.
  • You must have attached and have administrative permissions on the AdventureWorks OLTP database.

For more information about how to install samples, see the topic, "Installing Sample Integration Services Packages", in SQL Server 2005 Books Online.

Location

If the code sample was installed to the default location, the sample is located in the following directory:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\\Programming Samples\Control Flow\SmoTableList\

The C# solution for the code sample is located in the CS directory, and the Visual Basic solution is located in the VB directory.

For information about the two-step process required to install the samples, see Installing Samples. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Building the Sample

If you have not already generated a strong name key file, use the following procedure to generate this key file.

To generate a strong name key file

  1. To 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 -

    To 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.

  2. At the command prompt, use the change directory (CD) command to change the current folder of the command prompt window to the Samples folder. The key file that you create in this folder will be used by all SQL Server 2005 code samples.

Note

To determine the folder where samples are located, click Start, 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.

  1. At the command prompt, run the following command to generate the key file:
    sn -k SampleKey.snk

Important

For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

To build the sample

  1. Open Microsoft Visual Studio 2005.

  2. From the File | Open menu, click Project and open the solution (.sln) for the desired sample, which is SMOTableList.sln from the CS folder or from the VB folders, depending on the language you want.

  3. Click Build / Build SmoTableList on the menu.

Testing the Sample

The package that the sample creates is saved to the following location:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\\Programming Samples\Control Flow\SmoTableList\<language>\SmoTablelist\bin\debug\ SmoTableSamplePackage.dtsx.

To view the package in the designer, create a new Integration Services project and add the existing .dtsx item to the project. For more information, see the topic, "How to: Add an Item to an Integration Services Project", in SQL Server 2005 Books Online.

Change History

Release History

17 July 2006
Changed content:
  • Added requirement for SP1.

14 April 2006
Changed content:
  • Removed the instructions on copying the Smo.dll, SmoEnum.dll, and ConnectionInfo.dll to the %windir%\Microsoft.NET\Framework\v2.0.xxxxx as this step is no longer required.