Remote user defind functions

Question

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

Interesting...

It doesn't work with a param unless you use dynamic SQL, but here's something to start with...

select * from openquery(Server, 'Select * From db.dbo.fnMyFunctionName()' ) T1

Once you understand the BITs, all the pieces come together

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

Seems 4 part UDF naming conventions are NOT supported in SQL2K.

Once you understand the BITs, all the pieces come together

Mithrandir SSCrazy Points: 2628 More actions · Answer 3

Arrrghh.

That's ugly. I need the result table to perform joins on local tables.

I think I will duplicate the function on local DB using remote tables. Seems to be more elegant.

Thanks for your help Thomas.

ThomasH SSCertifiable Points: 7437 More actions · Answer 4

You can always insert into a #Temp table using either OpenQuery or Server.DB.dbo.sp_Execute ...,

then JOIN the #Temp table. This may be better for the optimizer to deal with anyway.

Once you understand the BITs, all the pieces come together

Mithrandir SSCrazy Points: 2628 More actions · Answer 5

Thanks, I'll consider it.

Now I have another problem with this UDF. I'm trying to put an optional parameter inside this UDF, but doesn't seem to be accepted.

I've tryed a similar case in one SP and there worked fine.

I'm searching in Internet and BOL, but I can't find anything.

Do you know somethig about it?

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

Off the top, I think both SPs and UDF can have default values, but UDFs require at least a NULL in each params.

Once you understand the BITs, all the pieces come together

Mithrandir SSCrazy Points: 2628 More actions · Answer 7

Maybe I don't understand your response, but if I need at least a NULL for @param2, isn't optional. I'm right?

I have:

create function dbo.MyFn(@param1 varchar(10), @param2 varchar(10) = 'No input') as
...

And try to execute it:

select * from dbo.MyFn('value1')

I get:

"Server: Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function dbo.MyFn."

Thanks

ThomasH SSCertifiable Points: 7437 More actions · Answer 8

Yes, in my exper. also... you need to...

select * from dbo.MyFn('value1', NULL) --- at the very least

Oh well, SQL's 1st time with UDFs, so I guess we get a lot of what we want, just not everything we would want.

Once you understand the BITs, all the pieces come together

Mithrandir SSCrazy Points: 2628 More actions · Answer 9

SSCrazy

Points: 2628

March 8, 2004 at 11:29 am

Jejeje, 🙂

Thanks