Using functions on remote/Linked server

Question

Post reply

Using functions on remote/Linked server

Michael Lee-158245

SSC Eights!

Points: 980
More actions
October 28, 2010 at 12:11 pm

#242264

I have a linked server that I need to run stored procs that contains functions in the query on the remote server. How can I execute a SP that contains a function.
For example I'd like to run this
Select [RHSQL].Brook2.dbo.GetCurrentAdmissionDate(@PatientId) as AdminDate,
[RHSQL].Brook2.dbo.GetDOTFromDOA(@PatientId, [RHSQL].Brook2.dbo.GetCurrentAdmissionDate(@PatientId)) as DischargeDate
from [RHSQL].Brook2.dbo.Admission
When this run I get the following error message:
Remote function reference 'RHSQL.Brook2.dbo.GetCurrentAdmissionDate' is not allowed, and the column name 'RHSQL' could not be found or is ambiguous.
Please help
Thanks
Michael

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

--snip edited wrong answer!--

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Lee-158245 SSC Eights! Points: 980 More actions · Answer 2

I have found a way around this issue, but it is NOT the best solution. Any functions on the linked server that needs to be called, I've had to duplicate the functions on the main server with the proper linkage to the linked server in that function.

for example here's a function on the Freedom server (main server):

ALTER FUNCTION [dbo].[Get_RH_DOTFromDOA]

(

@PatientId varchar(20),

@DOA datetime

)

Returns Datetime

AS

BEGIN

DECLARE @out_date Datetime

return (SELECT DischargeDate as DOT

FROM [RH-SQL].Brook2.dbo.Admission

WHERE AdmissionDate = @DOA and PatientId = @PatientId)

end

It's a pain to do it this way because it does duplicate work. I still don't understand WHY we can't call functions on the linked server.

If anyone can help. I hope someone has a better way.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Something like this should do what you want:

select

a.*

from

OPENQUERY ( RHSQL ,

'

Select

Brook2.dbo.GetCurrentAdmissionDate(1) as AdminDate,

Brook2.dbo.GetDOTFromDOA(1),

Brook2.dbo.GetCurrentAdmissionDate(1) as DischargeDate

from

Brook2.dbo.Admission

' ) a

Note that you will have to build the whole query dynamically to pass the value of @PatientId.

Not hard to do, but I just don't feel like doing it for you. 😛

Michael Lee-158245 SSC Eights! Points: 980 More actions · Answer 4

Hi Again,

I've just added another server named "BP-SQL\BPSQLSERVER" and I'm having issues executng a query from the main server. Here is an example

SELECT p.PatientID, p.LastName, p.FirstName, '1' as ProgramId

FROM [BP-SQL\BPSQLSERVER].dbo.PATIENTS p

I've also tried although I had a feeling these would not work

[BP-SQL.BPSQLSERVER]

[BPSQLSERVER]

[BP-SQL]

Please help.

GSquared SSC Guru Points: 260824 More actions · Answer 5

You're missing the database name in that. Needs to be between the server name and the schema name ("dbo").

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Vincent Vancalbergh SSC Enthusiast Points: 120 More actions · Answer 6

Michael Valentine Jones (10/28/2010)
Something like this should do what you want:
select
a.*
from
OPENQUERY ( RHSQL ,
'
Select
Brook2.dbo.GetCurrentAdmissionDate(1) as AdminDate,
Brook2.dbo.GetDOTFromDOA(1),
Brook2.dbo.GetCurrentAdmissionDate(1) as DischargeDate
from
Brook2.dbo.Admission
' ) a
Note that you will have to build the whole query dynamically to pass the value of @PatientId.
Not hard to do, but I just don't feel like doing it for you. 😛

Thanks, this solved my problem today! 🙂