I am trying to call a lookup from my ActiveX script [SQL Server 2000].
Ideally, I want to call a SQL function I wrote and supply parameters
for each row I am importing from a CSV file to retrieve a custom value
that will be used in my script. However, it looks like the only way I
can do this is by writing a second function that returns a TABLE and
simply is a wrapper to my original function.

I can't determine how to supply parameter values to this function call.
For a general table lookup, I use:

SELECT column1
FROM mytable
WHERE (column2 = ?)

And then call it like this in my ActiveX script:

Dim myVariable
myVariable = DTSLookups("GetSomeValue").Execute(myInputValue)

However, when I use similar syntax for the function lookup:

SELECT function_column
FROM mytablefunction (?, ?, ?) mytablefunction

I get the helpful error message, "ADO error: Syntax error or access
violation." when I try to click OK in the Lookup dialog.

How do I write my lookup so that I can supply parameter values at
run-time from my ActiveX script?

TIA,

Ryan

I haven't tried to do this and the major reason why is that I much prefer to
stage the data in SQL Server first from a file and then use TSQL to move to
the real table. I know that it involves more steps but for me this is a
step worth taking.



"herlihyboy" <ryan.parmenter (AT) gmail (DOT) com> wrote

I figured out another way to handle this. I wrapped the function call
inside a stored procedure. Although you get a warning when you add a
new Lookup that calls a stored procedure, it still executes just fine.
For example:

sp_MyProcCallsAFunction ?, ?, ?

- Ryan