473,326 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Functions

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.
Jul 20 '05 #1
7 6675
Mirth1314 (no*@ahope.net) writes:
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.


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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(val1, val2, val3, val4, val5, val6)

The function is declared as...

create function owner.functionName(@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
<so****@algonet.se> wrote:
Mirth1314 (no*@ahope.net) writes:
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.


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


Jul 20 '05 #3
Mirth1314 (no*@ahope.net) writes:
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.
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.
So i have been trying....

variable = functionName(val1, val2, val3, val4, val5, val6)


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
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.
Jul 20 '05 #5
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(var1, 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, dr***@drmcl.free-online.co.uk (DMAC)
wrote:
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.


Jul 20 '05 #6
Mirth1314 (no*@ahope.net) writes:
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.


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

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7
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
<so****@algonet.se> wrote:
Mirth1314 (no*@ahope.net) writes:
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.


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


Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: hokiegal99 | last post by:
A few questions about the following code. How would I "wrap" this in a function, and do I need to? Also, how can I make the code smart enough to realize that when a file has 2 or more bad...
99
by: David MacQuigg | last post by:
I'm not getting any feedback on the most important benefit in my proposed "Ideas for Python 3" thread - the unification of methods and functions. Perhaps it was buried among too many other less...
21
by: Rubén Campos | last post by:
I haven't found any previous message related to what I'm going to ask here, but accept my anticipated excuses if I'm wrong. I want to ask about the real usefulness of the 'inline' keyword. I've...
17
by: cwdjrxyz | last post by:
Javascript has a very small math function list. However there is no reason that this list can not be extended greatly. Speed is not an issue, unless you nest complicated calculations several levels...
2
by: Bryan Olson | last post by:
The current Python standard library provides two cryptographic hash functions: MD5 and SHA-1 . The authors of MD5 originally stated: It is conjectured that it is computationally infeasible to...
7
by: Tim ffitch | last post by:
Hi I have created a VB dll file that contains common functions I use across various projects in VB, Access and Excel. Rather than have to code the functions in each I decided to use the dll...
23
by: Timothy Madden | last post by:
Hello all. I program C++ since a lot of time now and I still don't know this simple thing: what's the problem with local functions so they are not part of C++ ? There surely are many people...
14
by: v4vijayakumar | last post by:
Why we need "virtual private member functions"? Why it is not an (compile time) error?
7
by: Immortal Nephi | last post by:
My project grows large when I put too many member functions into one class. The header file and source code file will have approximately 50,000 lines when one class contains thousand member...
6
KevinADC
by: KevinADC | last post by:
This snippet of code provides several examples of programming techniques that can be applied to most programs. using hashes to create unique results static variable recursive function...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.