Hi,
I am trying to pre-fetch parameter info for a stored procedure in a remote DB over a dblink. When I execute the DeriveParameter statement I get an exception message: "The stored procedure" {name} doesn't exist".
However, if I add the parameter manually and call ExecuteNonQuery, the call succeeds.
Info:
.NET v1.1
Oracle 9i client driver
private synonym for SP on local DB (DBINST1)
SP resides on DBINST2
public DBLink
below is the code that fails:
string ConnectionString = "Provider=MSDAORA;Data Source=DBINST1;User ID=user;Password=...";
System.Data.OleDb.OleDbConnection con = new OleDbConnection(ConnectionString);
try
{
con.Open();
OleDbTransaction tx = con.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(_spName,con, tx);
cmd.CommandType = CommandType.StoredProcedure;
/* THE OFFENDING CODE THAT THROWS THE EXCEPTION */
if( _callDeriveParam )
{
System.Data.OleDb.OleDbCommandBuilder.DeriveParameters (
(System.Data.OleDb.OleDbCommand) cmd );
}
else
cmd.Parameters.Add("@PID", OracleType.Number).Value = 102;
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Call to stored procedure using MS OLEDB succeeded");
}
catch ( Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message + '\n' + ex.StackTrace, "Call to OleDBCommandBuilder.DeriveParameters failed!",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Any help would be appreciated.
-Arif