- The CommandText must be set to the Stored Procedure Name.
- The CommandType must be set to CommandType.StoredProcedure.
- 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.
- 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 http://downloads.teradata.com/download/connectivity/ dot-net-data-provider
- You are mixing the ".NET Data Provider for SQL Server" types (i.e. SqlDbType) with the ".NET Data Provider for OLE DB" types (OleDbType).
- 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;
cm.ExecuteNonQuery();
See the .NET Data Provider developers Guide: http://developer.teradata.com/doc/connectivity/tdnetdp/13.11/webhelp/StoredProcedureOverview.html
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?
Hi,
I am new to using Teradata and am trying to use it with vb.net to build a user app.
Can anyone please tell me how to call a teradata sp from vb.net (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)
BEGIN
SELECT COUNT(ITEM) INTO outputnumber
FROM dbname.tablename
WHERE ITEM = inputnumber;
END
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
cn.Open()
ResultSet = GetArrayList(cmd.ExecuteReader, True)
End Using