How to run user defined function on sql server 2005

Dear all

I wants to run sql server user defined function when linked two server.

I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?

Regards

Jerminxxx

You can call the function with [ServerName] .[DatabaseName].[User/Schema Name].[FunctionName].

|||

Just use the select ServerName.DatabaseName.OwnerName.Functionname

i.e Like select IFoundd10.Employee.dbo.getenc()

__________________________________________________________

Don't forget to click "Mark as Answer" on the post that helped you.

|||

Thanks for ur reply.

when i try to use this technique i got some error like .

"contains more than the maximum number of prefixes."

Can u help me

|||

Which version of SQL Server are you using ? Can you post some of the details of the 2 servers that you've linked, the function and from where are you trying to execute the function ?

|||

I'm using sql server 2005.

For Example:

We have two sql server named as server1 and server2. These two servers are already linked.

On server1 we have some user defined function.Amoung the function one function called getencf().

What i want .I wants to run function getencf() on server 2 environment.

When i try to execute like servername.Databasename.dbo.functionname.I got some error.

CAn you help me

Regards

Jerminxxx

|||

jerminxxx:

When i try to execute like servername.Databasename.dbo.functionname.I got some error.

Are you getting the same error ("contains more than the maximum number of prefixes.") which you posted in one of your posts ?

Are you able to run the function from server1 ?

|||

it will e hard due to prefixes number, if your function returns single value try to replace it( or add extra ) stored procedure on the server which will call you function. If your function returns results set try to use OPENQuery to execute it on remote server.

How to return a varchar from sproc on remote Linked Server?

The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.

DECLARE@.OutParamVARCHAR(5)

EXEC('{Call getnextpin(1)}')ATALTTEST

GetNextPin takes a single input parameter.

I need to modify the above code snippet to return the next PIN number into @.OutParam.

Any ideas?

Any help appreciated.

Regards,

Paul.

Hi wadep,

Try the following code and see if it solves your problem ( i suppose your table has a primary key named "number_index" and your table is ordered well. If not, you need to use cursor which will be a little complicated)

create procedure getnextpin@.indexint,@.numberint outputasselect top 1 @.number=pin_number from your_table_namewhere number_index>@.indexreturn

-------- call proceduredeclare @.indexint, @.numberintset @.index=whatever_you_wantexecute getnextpin @.index,@.number outputprint @.number -- see if your number can be printed here

Hope my suggestion helps