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