Functions

[Mirth1314]

Hi,,

I'm having a problem with calling a function from an activex script
within a data transformation. the function takes 6 inputs and returns
a single output. My problem is that after trying all of the stuff on
BOL I still can't get it to work. It's on the same database and I'm
running sql 2000.

when I try to call it I get an error message saying "object required
functionname" If I put dbo in front of it I get "object required dbo".

Can anyone shed any light on how i call this function and assign the
output value returned to a variable name.

thanks.

[Erland Sommarskog]

Re: Functions

Mirth1314 (not@ahope.net) writes:[color=blue]
> I'm having a problem with calling a function from an activex script
> within a data transformation. the function takes 6 inputs and returns
> a single output. My problem is that after trying all of the stuff on
> BOL I still can't get it to work. It's on the same database and I'm
> running sql 2000.
>
> when I try to call it I get an error message saying "object required
> functionname" If I put dbo in front of it I get "object required dbo".
>
> Can anyone shed any light on how i call this function and assign the
> output value returned to a variable name.[/color]

Please post the code you are using. Both the code for the UDF and
the Active-X code you use to call it.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Mirth1314]

Re: Functions

Hi Erland,

To be honest I can't really do that but as i say I was on books online
and I assumed I could just declare a variable and assign it to the
value returned from the function.

So i have been trying....

variable = functionName(va l1, val2, val3, val4, val5, val6)

The function is declared as...

create function owner.functionN ame(@val1 datatype, @val 2datetype ....
@val6 datatype) returns datatype
begin
......
return @returnValName
end

I hope this helps as I can't really expand any further. My help was
more of a guideline or an example of how to do this as opposed to
specific help.

Thanks.

On Sun, 14 Sep 2003 18:13:34 +0000 (UTC), Erland Sommarskog
<sommar@algonet .se> wrote:
[color=blue]
>Mirth1314 (not@ahope.net) writes:[color=green]
>> I'm having a problem with calling a function from an activex script
>> within a data transformation. the function takes 6 inputs and returns
>> a single output. My problem is that after trying all of the stuff on
>> BOL I still can't get it to work. It's on the same database and I'm
>> running sql 2000.
>>
>> when I try to call it I get an error message saying "object required
>> functionname" If I put dbo in front of it I get "object required dbo".
>>
>> Can anyone shed any light on how i call this function and assign the
>> output value returned to a variable name.[/color]
>
>Please post the code you are using. Both the code for the UDF and
>the Active-X code you use to call it.[/color]

[Erland Sommarskog]

Re: Functions

Mirth1314 (not@ahope.net) writes:[color=blue]
> To be honest I can't really do that but as i say I was on books online
> and I assumed I could just declare a variable and assign it to the
> value returned from the function.[/color]

If you don't post your code, your chances to get help are reduced.
You will have to excuse, but guessing you might be doing wrong is
not that thrilling.

You don't have to post your actual code, but some sample, and which
demonstrates the same problem as your original code.
[color=blue]
> So i have been trying....
>
> variable = functionName(va l1, val2, val3, val4, val5, val6)[/color]

Don't know if this is supposed to be Active-X or T-SQL. In T-SQL
the syntax is

EXEC @variable = dbo.fun(@par1, @par2, ...)

In Active-X I don't know, as I don't really know what Active-X is. (See
know why I need a real code sample?) I suppose it involves ADO (after
Active-X is what the A stands for), and honestly I don't know if you can
call UDFs directly from ADO. Again, that's why I want a sample to work
from.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[DMAC]

Re: Functions

CREATE FUNCTION fnTEST
(@param1 int, @param2 int)
RETURNS int
AS
BEGIN
DECLARE @sum AS int
SELECT @sum = @param1 + @param2
RETURN @sum
END

go

declare @ReturnVariable int
select @ReturnVariable =dbo.fntest(1,1 )
select @ReturnVariable

Not surprisingly this will disply 2 in query analyser, but it does
serve the point of displaying how to assign a functions return value
to a variable.

[Mirth1314]

Re: Functions

Ok thanks guys. I appreciate how difficult this is for you without
having the code but you are helping.

I can now get the code to work in query analyser using DMAC's stuff
below.

However i need to call this function as part of a DTS package inside a
Transform Data Task, that's where the activex package comes in Erland.
Written in VBScript.

When I try to run/execute/call it I get error code:0; vbscript runtime
error; Type Mismatch: functionanme.

Now my impression was that it has something to do with datatypes. So I
explicitly set the date fields using cdate and the varchar fields
using cstr but I still get the error.

I'm still trying to call it by using...

variable name = functionname(va r1, var2 ...var6)

Am I missing the boat here, can it be done and if not can someone shed
some light on the best way to use a UDF like this inside a data
transformation task.

Thanks again guys.

On 15 Sep 2003 16:03:47 -0700, drmcl@drmcl.fre e-online.co.uk (DMAC)
wrote:
[color=blue]
>CREATE FUNCTION fnTEST
> (@param1 int, @param2 int)
>RETURNS int
>AS
>BEGIN
> DECLARE @sum AS int
> SELECT @sum = @param1 + @param2
> RETURN @sum
>END
>
>go
>
>declare @ReturnVariable int
>select @ReturnVariable =dbo.fntest(1,1 )
>select @ReturnVariable
>
>Not surprisingly this will disply 2 in query analyser, but it does
>serve the point of displaying how to assign a functions return value
>to a variable.[/color]

[Erland Sommarskog]

Re: Functions

Mirth1314 (not@ahope.net) writes:[color=blue]
> However i need to call this function as part of a DTS package inside a
> Transform Data Task, that's where the activex package comes in Erland.
> Written in VBScript.[/color]

I have no experience of VBscript, and I don't use DTS. I think that
maybe you should jog over to microsoft.publi c.sqlserver.dts . The
people over there, might understand better what you are doing.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Mirth1314]

Re: Functions

Thanks Erland, I'll have a look over there. Although I did think that
what I was doing here was fairly straight forward in sqlserver world.
I can't possibly imagine I'm the only person who has ever tried to
call or use a UDF from a data transformation task.

On Tue, 16 Sep 2003 20:52:31 +0000 (UTC), Erland Sommarskog
<sommar@algonet .se> wrote:
[color=blue]
>Mirth1314 (not@ahope.net) writes:[color=green]
>> However i need to call this function as part of a DTS package inside a
>> Transform Data Task, that's where the activex package comes in Erland.
>> Written in VBScript.[/color]
>
>I have no experience of VBscript, and I don't use DTS. I think that
>maybe you should jog over to microsoft.publi c.sqlserver.dts . The
>people over there, might understand better what you are doing.[/color]