All Forums Connectivity
marcmc 112 posts Joined 12/05
10 Jan 2007
vb.Net call to Teradata stored procedure

Hey,The below function won't build for me at the two statements below attempting to add parameters to a dynamic stored procedure call. It doesn't like the 2nd parameter in each statement. I just cannot find the correct syntax using Teradata. I usually call these in BTEQ like call sp_MISRE_ChkClaimCount('Marc', 10, v_return_code);"My procedure uses 3 parameters: (IN v_pWho VARCHAR(20), IN v_i_run_job_id INT, OUT v_return_code INT))Error: "Too many arguments to 'Public MustOverride Function Add(value As Object) As Integer'." looks like dbCommand (from .NET Data Provider for Teradata - Teradata.Client.Provider) can only support 1 parameter. Any ideas how to fix. This worked fine in SQLServer using VS 2003 and SqlClient.SqlCommand. cmd.Parameters.Add("v_pWho", UNameRoutine) cmd.Parameters(0).Direction = ParameterDirection.Input cmd.Parameters.Add("v_return_code", SqlDbType.Int) cmd.Parameters(1).Direction = ParameterDirection.Output-- EXAMPLE CODE'Vars declarations '.NET Data Provider for Teradata - Teradata.Client.Provider Dim cn As DbConnection Dim cmd As DbCommand Dim dtr, dtr1 As DbDataReader Dim strSQL As String Dim cmdSelect As DbCommand Dim cnString As String Dim pf As DbProviderFactory = DbProviderFactories.GetFactory(" Teradata.Client.Provider")' Function Code Private Function RunExecRoutines() As Boolean Try cnString = "Data Source=" & Server.Text & ";User ID=" & UserName.Text & ";Password=" + Password.Text cn = pf.CreateConnection cn.ConnectionString = cnString cn.Open() Catch ex As Exception sqlCnError = ("Error: Could not establish database connection") End Try cmd = pf.CreateCommand cmd.Connection = cn cmd.CommandText = ProcToExec cmd.CommandTimeout = 9000 cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("v_pWho", UNameRoutine) cmd.Parameters(0).Direction = ParameterDirection.Input cmd.Parameters.Add("v_return_code", SqlDbType.Int) cmd.Parameters(1).Direction = ParameterDirection.Output Try cmd.ExecuteNonQuery() Catch ex As Exception sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedure") MessageBox.Show(sqlCnError, "MIS RECON Engine", MessageBoxButtons.OK, _ MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) End Try cmd.Dispose() cn.Close() cn.Dispose()End Function

NetFx 346 posts Joined 09/06
10 Jan 2007

Refer to DbParameterCollection Members for information on DbParameterCollection.DbParameterCollection class has one and only one Add method. It simply takes an Object.TdParameterCollection class has 7 differnt Add methods.Try the following if you prefer to use DbParameter and DbParameterCollection.cmd.Parameters.Add(pf.CreateParameter)cmd.Parameters(0).Direction = ParameterDirection.Inputcmd.Parameters.Add(pf.CreateParameter)cmd.Parameters(1).Direction = ParameterDirection.Inputcmd.Parameters(1).DbType = DbType.Int32

marcmc 112 posts Joined 12/05
15 Jan 2007

Thanks Shaw, I am still confused though.I tried your suggestion as shown in code at the bottom of this thread by passing 3 parameters as per the code snippet you recommended. However, where exactly do you assign the parameters to be used? i.e. UNameRoutine is = 'Marc' (the logon to the application) etc. My sProc is defined like this REPLACE PROCEDURE sp_MISRE_Pivot ( IN v_pWho VARCHAR(20), IN v_i_run_job_id INT, OUT v_return_code INT)Also when I run the below as is, I get the message 'Teradata.Client.Provider.TdException: [Teradata Database [5495] Stored Procedure 'DEV_DATA_DIMS.DEV_DATA_V.sp_MISRE_Pivot' does not exist at Teradata.Client.Provider.TdCommand.ExecuteNonQuery'.My sProc exists on DEV_DATA_V but it attaches the default DEV_DATA_DIMS onto it first. When I log into Queryman with no default database set, it will logon to DEV_DATA_DIMS, maybe this is it but how can i change that (i didn't set up the databases). cmd.Parameters.Add(pf.CreateParameter) cmd.Parameters(0).Direction = ParameterDirection.Input cmd.Parameters(0).DbType = DbType.String cmd.Parameters.Add(pf.CreateParameter) cmd.Parameters(1).Direction = ParameterDirection.Input cmd.Parameters(1).DbType = DbType.Int32 cmd.Parameters.Add(pf.CreateParameter) cmd.Parameters(2).Direction = ParameterDirection.Output cmd.Parameters(2).DbType = DbType.Int32 MessageBox.Show(ProcToExec) MessageBox.Show(cmd.CommandText.ToString) MessageBox.Show(cmd.ToString) Try cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show(ex.ToString) sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedure") MessageBox.Show(sqlCnError, "MIS RECON Engine", MessageBoxButtons.OK, _ MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) End Try

marcmc 112 posts Joined 12/05
15 Jan 2007

I've gotten a bit further in that i have the sProc running but the default database it picks up (DEV_DATA_DIMS) when running a sProc is causing issue. The sProc resides on DEV_DATA_V, I have madea copy on DEV_DATA_DIMS until i resolve the issue there. Any idea how this is being picked up as I am not setting it or can you advise how I can get it to choose the database i want it to? note: (it's not doing this on normal DML or Select Statements).

NetFx 346 posts Joined 09/06
15 Jan 2007

To change your default database DbCommand cmd = cn.CreateCommand(); cmd.CommandText = "Database DEV_DATA_V"; cmd.ExecuteNonQuery(); cmd.CommanText = "sp_MISRE_Pivot"; cmd.CommandType = CommandType.StoredProcedure; // Set Parameters cmd.ExecuteNonQuery();

marcmc 112 posts Joined 12/05
16 Jan 2007

which is fine but the name could and will in my case exceed 30 charaters.I have used the tools-modify user-default database tool instead. I don't think this will have any issue in dev.thanks for the help.

You must sign in to leave a comment.