There are some behavioral differences related to Stored Procedures when the TdConnectionStringBuilder.SessionCharacterSet is set to UTF8 versus when the session character set is ASCII. These differences in behavior are driven by the Advanced SQL Engine.

The following sections are guidelines related to Stored Procedures when the session character set is UTF8:

Do not exceed the maximum length of a VarChar column

A TdException will be thrown indicating that "a column or character expression is larger than the max size" under the following conditions:

1. The Connectiong String Attribute Session Character Set is set to UTF8
2. The stored procedure has one or more parameters declared as VARCHAR(64000) CHARACTER SET LATIN.
3. The TdParameter.DbType property is set to DbType.String or DbType.AnsiString.
4. The TdParameter.Size is set to 32,001.

This error occurs because the maximum size of a VarChar literal in a Unicode session character set is 32,000 characters. There are several ways to avoid this problem:

1. Specify a Session Character Set that has a fixed-byte number of bytes per character of 1, if possible. An example of such a character set is ASCII.
2. Use a data type that supports a larger size. For example, set the TdParameter.DbType property to TdType.Clob. It is not necessary to change the stored procedure definition for an input parameter, but it may be necessary to change both, the data type and the stored procedure definition for an output parameter.

The following is an example of a program that will throw an exception when the stored procedure is executed:

public void StoredProcedureExample()
{
    TdConnection conn = new TdConnection("Data Source=tdat;User Id=user1;Password=pass;Session Character Set=utf8");
    conn.Open();

    TdCommand cmd = conn.CreateCommand();

    //The signature of the stored procedure is
    // spExample(p1 VARCHAR(64000) CHARACTER SET LATIN)

    cmd.CommandText = "spExample";
    cmd.CommandType = CommandType.StoredProcedure;

    TdParameter param = new TdParameter();
    param.DbType = DbType.String;
    for (Int32 i = 0; i < 3200; i++)
    {
        param.Value += "1234567890";
    }
    param.Value += "1";
    param.Size = 32001;
    cmd.Parameters.Add(param);

    try
    {
        // When the stored procedure is executed, a TdException will be thrown
        // indicating that a column or character expression is larger than the max size.

        cmd.ExecuteNonQuery();
    }
    catch (TdException t)
    {
        Console.WriteLine(t.Message);
    }
}

Problem When Using DbType To Describe Interval Type Parameters

When a stored procedure contains Interval type parameters and DbTypes are used to describe the data type of an Interval parameter, DbType.AnsiString must be specified.

If DbType.String is used to describe the data type of an Interval parameter a TdException will be thrown indicating an "interval field overflow" when the stored procedure is executed under the following conditions:

1. The Session Character Set is UTF8.
2. The stored procedure that is to be executed contains a in/inout parameter that is an interval type.
3. TdParameter.DbType property of the corresponding interval parameter is set to DbType.String.
4. TdParameter.Size is set to the number of characters contained in the string representation of the interval.

This error occurs because the TdParameter.DbType is set to DbType.String, the TdParameter.Size represents the number of characters in the string. The provider must specify the maximum number of bytes contained in the string when the data is sent to Teradata. The provider will calculate the maximum number of bytes by multiplying the number of characters specified in Size by the maximum number bytes per a character for the specified character set (for UTF8 the maximum number of byte per a character is 3). For variable-byte character sets the maximum number of bytes will be greater than the number of characters in the string representation of the interval. When Teradata receives the data and corresponding meta-data, it is unable to implicitly convert the string into an interval type and will throw an "interval field overflow" error. The following code example will cause this error to be thrown:

public void StoredProcedureExample()
{
    TdConnection conn = new TdConnection("Data Source=tdat;User Id=user1;Password=pass;Session Character Set=utf8");
    conn.Open();

    TdCommand cmd = conn.CreateCommand();

    //The signature of the stored procedure is
    // spExample(p1 INTERVAL YEAR(4) TO MONTH)

    cmd.CommandText = "spExample";
    cmd.CommandType = CommandType.StoredProcedure;

    TdParameter param = new TdParameter();
    param.DbType = DbType.String;
    param.Value = "-4049-11";

    // Size represents the number of characters in the string.
    // The maximum number of bytes that will be specified to
    // Teradata will be 24 (8 * 3 which is the max number of
    // bytes per a character for UTF8).
    param.Size = 8;
    cmd.Parameters.Add(param);

    try
    {
        // When the stored procedure is executed, a TdException will be thrown
        // indicating that there was an interval field overflow.

        cmd.ExecuteNonQuery();
    }
    catch (TdException t)
    {
        Console.WriteLine(t.Message);
    }
}

To avoid this problem, the TdParameter.DbType property must be set to DbType.AnsiString. When DbType.AnsiString is specified, the value in TdParameter.Size represents the number of bytes of the string. The TdParameter.Size property is set to the number of characters contained in the string representation of the interval. The following code example will successfully execute:

public void StoredProcedureExample()
{
    TdConnection conn = new TdConnection("Data Source=tdat;User Id=user1;Password=pass;Session Character Set=utf8");
    conn.Open();

    TdCommand cmd = conn.CreateCommand();

    //The signature of the stored procedure is
    // spExample(p1 INTERVAL YEAR(4) TO MONTH)

    cmd.CommandText = "spExample";
    cmd.CommandType = CommandType.StoredProcedure;

    TdParameter param = new TdParameter();
    param.DbType = DbType.AnsiString;
    param.Value = "-4049-11";

    // Size represents the number of bytes in the string.
    // In order for the stored procedure to execute successfully,
    // Size is set to the number of characters contained in the
    // string representation of the interval.
    param.Size = 8;

    cmd.Parameters.Add(param);

    // The stored procedure will be successfully executed
    cmd.ExecuteNonQuery();
}

See Also

Stored Procedure General Guidelines

Provider Specific Types Overview

Enabling Provider Specific Types

TdParameter.Size

TdParameter.Value

TdParameter.ProviderSpecificValue