All Forums Connectivity
Kazzie 2 posts Joined 11/11
09 Nov 2011
Calling Teradata sp from .Net


I am new to using Teradata and am trying to use it with to build a user app.

Can anyone please tell me how to call a teradata sp from (Visual Studio 2005)

I have a simple sp with input and output parameters, something like:

REPLACE PROCEDURE dbname.spname (IN inputnumber Integer, OUT outputnumber Integer)



FROM dbname.tablename

WHERE ITEM = inputnumber;


In teradta sql assitant I can call this using: call dbname.spname ('10',outputnum);

However in .Net I have tried different ways to call this sp but keep getting errors such as:

[Teradata Database] Invalid session mode for procedure execution.

Here is a sample function similar to what I am using, am I doing something wrong here?

Public Function TestFunction(ByVal TestNum) As Boolean

Dim Sql As String

Dim ResultSet As New ArrayList

Sql = "CALL dbname.spname(?,?);"

Using cn As OleDbConnection = New OleDbConnection(TeradataApp.ConnectionString)

Dim cmd As OleDbCommand = New OleDbCommand(Sql, cn)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue("inputnumber", OleDbType.Integer).Value = TestNum

cmd.Parameters.AddWithValue("outputnumber", SqlDbType.Int)

cmd.Parameters("outputnumber").Direction = ParameterDirection.Output


 ResultSet = GetArrayList(cmd.ExecuteReader, True)

End Using


NetFx 346 posts Joined 09/06
14 Nov 2011
  1. The CommandText must be set to the Stored Procedure Name.
  2. The CommandType must be set to CommandType.StoredProcedure.
  3. You must use the same Session Mode. That is the Session Mode you used to create the Stored Procedure must match the Session Mode you use to invoke the Stored Procdure.
  4. Use the .NET Data Provider for Teradata instead of "Microsoft .NET Data Provider for OLE DB"; you can download the .NET Data Provider for Teradata from dot-net-data-provider
  5. You are mixing the ".NET Data Provider for SQL Server" types (i.e. SqlDbType) with the ".NET Data Provider for OLE DB" types (OleDbType).
  6. Use ExecuteNonQuery given that SP does not return Dynamic Result Set.


cmd.CommandText = "SPNAME";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("P1", OleDbType.Integer);
cmd.Parameters.Add("P2", OleDbType.Integer);
cmd.Parameters["P2"].Direction = ParameterDirection.Output;
cmd.Parameters["P1"].value = X;

 See the .NET Data Provider developers Guide:




Kazzie 2 posts Joined 11/11
12 Dec 2011

Thank you very much NetFx...

I have downloaded the Terdata .Net provider and it is working for me now thanks to your help.

I wonder, could you advise on using cursors in stored procedures in Teradata.

I have read that cursors are not good to use however, from what I have read this seems to be the only way to retrieve a result set from Teradata?


You must sign in to leave a comment.