Re: ADO call to user defined function

From: Val Mazur (group51a_at_hotmail.com)
Date: 05/22/04 

Date: Fri, 21 May 2004 22:27:26 -0400

Hi,

This is actually not and ADO's issue. ADO works through OLEDB provider and
support all the features, which supported by OLEDB. OLEDB provider for SQL
Server was developed some time ago, when SQL Server did not have UDF's. This
is why ADO does not support them as well. Same issue when you try to call
any UDF, written in VB Script in a Access database. 

-- 
Val Mazur
Microsoft MVP
"SBrickey" <Me@SBrickey.net> wrote in message 
news:OdYGK4yPEHA.308@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Can you think of an effective method of creating a single SP which can be
> passed parameters, to dynamically call ANY UDF, also containing the
> parameters?
>
> I suppose i could have an SP with perhaps a dozen params, figuring every 
> UDF
> has < 12 params (figuring 1 param to specify which UDF i'm wanting to
> use).... doesn't seem like a great solution though.
>
> I'm kinda suprised that there's no direct method for ADO to call UDF's, 
> yet
> so much support for SP's... oh well
>
> Ideas?
>
> -Scott
>
> "Val Mazur" <group51a@hotmail.com> wrote in message
> news:uEAr#ntPEHA.3016@tk2msftngp13.phx.gbl...
>> Hi,
>>
>> Since you cannot call UDF from the code directly, what you could do is to
>> wrap them into the stored procedure and call SP
>>
>> --
>> Val Mazur
>> Microsoft MVP
>>
>>
>> "MS news" <Me@SBrickey.net> wrote in message
>> news:%23jCWxwdPEHA.4036@TK2MSFTNGP12.phx.gbl...
>> > hello,
>> >
>> > I recently decided that one of my stored procedures (which works fine)
>> > would
>> > be better fit as a (scalar) user defined function. I used QA to move 
>> > the
>> > code, and tested, and it works fine. Unfortanately ASP feels
> differently,
>> > as
>> > I get the error "Restricted data type attribute violation".
>> >
>> > here is a snippet of the code...
>> > AdoCom.CommandText = "GetPricingBranchProductCost"
>> > AdoCom.CommandType = AdCmdStoredProc
>> > AdoCom.Parameters.Append AdoCom.CreateParameter("@ProdID", AdBigInt,
>> > AdParamInput, , ProdID)
>> > AdoCom.Parameters.Append AdoCom.CreateParameter("@BrID", AdBigInt,
>> > AdParamInput, , BrID)
>> > Set AdoRS = AdoCom.Execute
>> >
>> > it blows up on the .Execute
>> > I was concerned that the CommandType would be incorrect, and tried 
>> > other
>> > values (&H0001 and &H0008, for text and unknown, seemed to work best;
> but
>> > complained about lacking parameters) but have had no success.
>> >
>> > from what little i've found about calling UDF's from within ADO,
>> > commandtype
>> > SHOULD work as stored proc (though this may be wrong)...
>> > but when the SQL code WAS a stored proc, i never got these errors.
>> >
>> > Any ideas appreciated.
>> > Thanks,
>> > -Scott
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: Using parameters with the low level ADO API, trying to avoid the memory leaks.
    ... MS ADO passes SQL directly to OLEDB provider. ...
    (borland.public.delphi.database.ado)
  • Re: What causes error -2147217887 (80040e21)
    ... That would have been nice to know in advance (no ADO support). ... your support personnel didn't mention anything about lack of ADO ... I have done some work with VB and SQL Express or SQL CE (what ever the ... If for example you query the CE database ...
    (microsoft.public.sqlserver.ce)
  • Re: How to use COLLATE to SORT ADO table by international sorting rules?
    ... ADO does not accept SQL at all and pass command text to OLEDB provider. ... documentation for your DBMS for SQL syntax - it is different for different ...
    (borland.public.delphi.database.ado)
  • Re: Tiger Stock
    ... a lot of money selling applications based on their own platform. ... larger players support more interfaces, ... Coming back to a point in the last paragraph: Lack of support for SQL ... So if we take that off the table and compare companies, ...
    (comp.databases.pick)
  • RE: SQL Set to System Account - Sharepoint Question
    ... Administration", not System Account) account. ... after the installation there isn't normally any reason to ... SQL, don't use an instance being used for anything else. ... the App Support people why SA is required. ...
    (microsoft.public.windows.server.sbs)
Loading