Microsoft SQL Server Time Series Sample – Time Series Library

Updated: 13 October 2008

‘Time Series Sample’ showcases the integration of CLR into Microsoft SQL Server. The sample contains a set of stored procedures and functions that are used in general Time Series analysis, collectively called as ‘TimeSeries Library’. These routines are implemented in C#. This folder consists of a solution (Timeseries.sln) containing one project, TimeSeriesCS.csproj. Please refer the TimeSeries - User Documentation for detail functionality of the library.

 

Note:

When you compile common language runtime (CLR) integration samples, the path of the version of the Microsoft .NET Framework compiler that was distributed with SQL Server must be the first Framework directory in the PATH environment variable. The location of the compiler that is included with SQL Server is either drive:\WINNT\Microsoft.NET\Framework\v2.0.xxxxx or drive:\Windows\Microsoft.NET\Framework\v2.0.xxxxx, where drive is the installation drive and xxxxx is the version of the .NET Framework that is included with SQL Server.

 

The default installation directory is drive:\Program Files\Microsoft SQL Server\100\Samples\Engine\Programmability\CLR\TimeSeries\Library

 

Scenario

·          An analyst want to examine the historical stock market data to identify the trend represented by the sequence of observations and make some future prediction based on the data.

 

Languages

Visual C#

 

Prerequisites

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

·          SQL Server 2005 (or later) or SQL Server 2005 (or later) Express Edition (SQL Server Express). You can obtain SQL Server Express free of charge from the SQL Server 2005 Express Edition Documentation and Samples Web site.

·          The CLR for SQL Server 2005 or SQL Server 2005 Express Edition must be enabled. Execute <ApplicationFolder>\Scripts\EnableCLR.sql script to enable the CLR.

·          Microsoft .NET Framework version 2.0 SDK 2.0 or Microsoft Visual Studio 2005

 

Building the sample

To build the TimeSeries Library (VB .NET and Visual C#), do the following:

1.       Open the <ApplicationFolder>\Scripts\EnableCLR.sql file in SQL Server Management Studio and execute the script contained in the file or execute a command similar to the following at a command prompt:

sqlcmd -E -I -i <ApplicationFolder>\Scripts\EnableCLR.sql

This script enables SQL Server to execute common language runtime (CLR) code.

2.       Compile the sample by using Visual Studio 2005 and the provided Visual Studio solution or by using MSBUILD, that is included in the .NET Framework SDK 2.0, by executing a command similar to the following at a command prompt:

msbuild /nologo /verbosity:quiet /property:Configuration=Debug <ApplicationFolder>\Library\TimeSeries.sln

3.       If you did not install the SQL Server Database Engine samples in the default location, modify the path in the CREATE ASSEMBLY part of the script in <ApplicationFolder>\Scripts\Setup.sql to refer to the location where the samples were installed.

E.g. If sample is installed in D:\Samples, then remove following SELECT query in the <ApplicationFolder>\Scripts\Setup.sql file,

SELECT @SamplesPath = replace(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\100\Samples\Engine\Programmability\CLR\TimeSeries\') FROM master.sys.database_files  WHERE name = 'master';

and add following statement.

SELECT @SamplesPath = 'D:\Samples\';

4.       Ensure that you have administrative access for the SQL Server instance you are using.

5.       Open the <ApplicationFolder>\Scripts\Setup.sql file in SQL Server Management Studio and execute the script contained in the file or execute a command similar to the following at a command prompt:

sqlcmd -E -I -i <ApplicationFolder>\Scripts\Setup.sql

This file contains the script needed to create a sample database, DemoDB, remove the existing metadata objects (if any) and recreates them.

 

Running the sample

To run the sample, do the following:

1.       Open the <ApplicationFolder>\Scripts\test.sql file in the SQL Server Management Studio and execute the script contained in the file or execute the following command at a command prompt:

sqlcmd -E -I -i <ApplicationFolder>\Scripts\test.sql

This script shows typical ways to use the sample. Please refer the TimeSeries - User Documentation for the description and the usage of these routines.

 

Removing the sample

To remove the CLR samples, do the following:

1.       Open the <ApplicationFolder>\Scripts\cleanup.sql file in the SQL Server Management Studio and execute the script contained in the file or execute the following command at a command prompt:

sqlcmd -E -I -i <ApplicationFolder>\Scripts\cleanup.sql

This file contains the script needed to remove the Time Series Library along with the metadata tables.

 

Comments

The CLR for SQL Server 2005 or SQL Server 2005 Express Edition must be enabled for this sample to work correctly. Also ensure that Visual Studio Tools for the Microsoft Office System is installed on your machine.

 

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 ssNoVersion database or your report server without the permission of the system administrator. 

 

© 2005 Microsoft Corporation. All rights reserved.