UDF TableData Get Problem

  • Thread starter Thread starter localhost
  • Start date Start date
L

localhost

I am trying to get table data from a MSSQL Function/UDF using the
OleDb provider.
The error message I get is

"The request for procedure 'FuncOne' failed because 'FuncOne' is a
function object."

How can I get a Table-value return result from MSSQL UDF using OleDb?

Thanks.


try
{
// ... this.connection is open ...
OleDbCommand cmd = new OleDbCommand( "FuncOne" ,
this.connection );
cmd.CommandType = CommandType.StoredProcedure;

OleDbParameter pA = funcCommand.Parameters.Add( "@pA",
OleDbType.VarChar , 8000 );
pA.Value = "A";
pA.Direction = ParameterDirection.Input;
cmd.Parameters.Add( pA );
OleDbParameter pB = funcCommand.Parameters.Add( "@pB",
OleDbType.VarChar , 8000 );
pB.Value = "B";
pB.Direction = ParameterDirection.Input;
cmd.Parameters.Add( pB );
}
this.ResultSet.Tables.Add( new DataTable("test") );
OleDbDataAdapter odda = new OleDbDataAdapter( cmd );
odda.Fill( this.ResultSet.Tables[functionname] );
}
catch( OleDbException xxxOle )
{
string bang = xxxOle.ToString();
}
catch( Exception xxx )
{
string help = xxx.ToString();
}
finally
{
}
 
Selecting from a table valued function takes pretty much the same syntax as
selecting from a table. You'll need to replace the CommandText of your
command object with "SELECT * FROM dbo.FuncOne(?, ?)" (if the function is
owned by a user other than dbo, replace "dbo" with the owner name ). You
can then proceed to add your parameters and set their values.

[If the code below is a direct copy from your app, you'll need to make sure
the command object name is the same when you declare/initialize it and when
you add your parameters. Also make sure all of the "try" code is contained
in the try block. There are also a couple of other possible changes but if
your code doesn't work and you can't figure out the problems yourself you
can always post the problem.]

HTH
 
Thanks. The code snippet was extracted from a method that otherwise
runs fine, I just tried to graft the relevant pseudocode into the
usenet post.

I have successfully called MSSQL Functions that return strings and
integers with CommandType.StoredProcedure. Doing so results in
generally faster response time under load. However, I have not been
able to do the same when the Function returns a table variable. So I
am stuck, I still need to use CommandType.StoredProcedure on a
function when it returns a table variable.

Help?
 
Back
Top