MS SQL Server 2000 user defined function

dataservices job design
1

Hi,

I have searched the forum, but didn’t found any help about this.
Can someone please tell me how I can call a user defined function (from my MS SQL 2000 Server) in Data Integrator ?

Example :
I have a table with all Company names. With the database user defined function (namefilter) I filter these names. The filtered name is also stored in the table.
So I tried to map this column as follows, but it doesn’t work, I always get an error of incorrect syntax.
Here is the code I try to use in the mapping of the query :

sql('target_db','namefilter(Company_Name,\'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789\'');

Can someone please tell me what I am doing wrong, or how I can tell DI that I try to call a user defined function from my database ?

Thanx in advance

woeters :belgium: (BOB member since 2005-12-14)

2

Is there a reason you did not go to the datastore of the sql server, said “import by name”, “function”, “namefilter”?

Werner Daehn :de: (BOB member since 2004-12-17)

3

Yes, and the reason is quite simple : I did not know I had to do it like this :oops:

Thank you wdaehn, now I know how to use it :smiley:

One more question : how do you call a stored procedure ? And can you use stored procedures that have one or more return parameters ?

Thanks

woeters :belgium: (BOB member since 2005-12-14)

4

no problem.

Go to any query, the output schema and right click. In the popup menu, you can add new columns, modify properties or add a “New Function Call”. In the wizard shown next, select the datastore and its stored procedure.

Werner Daehn :de: (BOB member since 2004-12-17)

5

You can also call imported stored procedures directly in script, if for instance you need the values put into global variables. Thus:

TEST.TEST.sp_Test(‘InputValue1’, ‘InputValue2’, $Out1, $Out2, $Out3);

  • E

eepjr24 :us: (BOB member since 2005-09-16)

6

I haven’t checked with XI, but with 6.5, I don’t think DI properly pushes down the SQL Server user-defined function to the database, and instead calls it individually, separately, for each incoming row.

The way to verify this would be to look at the generated SQL for the dataflow and see if it’s doing a

select blah, blah, NAMEFILTER(blah)
from YOUR_TABLE

dnewton :us: (BOB member since 2004-01-30)

7

I just checked with XIR2 and it appears that the same feature (bug?) exists in this version as well.

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

8

I can see why this might be: If it is a stored procedure, you can’t use it inline with SQL. And when you import a stored procedure or a SQL function, DI doesn’t seem to know the difference between the two. So it treats everything like a stored procedure.

Maybe the enhancement would be: after importing the function into DI, if you could tick a checkbox that said “allow inline pushdown”…

dnewton :us: (BOB member since 2004-01-30)

9

That would be very useful.

  • E

eepjr24 :us: (BOB member since 2005-09-16)