Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-15 : 07:25:02
|
| jason writes "We need to get data from a linked server via openquery.We would like to have this 'wrapped' in a udf so that we can call this function in a select statement from both business objects ( via method calls ) or within our reporting environment as either sp or simple select statements.The sql will be dynamic. Take in one or more parameters and build the sql statementSomething like thisDECLARE @statement nvarchar(1000)SELECT @statement = 'SELECT <fields> FROM OPENQUERY(Linkedserver, 'SELECT fieldsfrom table where id = ''fred''')'exec sp_executesql @statment and return the results as a table variablewhen we've tried this we get the following error message:Only functions and extended stored procedures can be executed from within a function.Any help appreciatedThanks." |
|
|
dsdeming
479 Posts |
Posted - 2003-05-15 : 07:50:41
|
quote:
Only functions and extended stored procedures can be executed from within a function.
The message really says it all. Dynamic SQL isn't happening in a udf. I think that if you want to use udfs for this task, you'll need two, and each will have to explicitly specify the linked server. |
 |
|
|
|
|
|
sp_execute within udf