SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using functions on remote/Linked server


Using functions on remote/Linked server

Author
Message
Michael Lee-158245
Michael Lee-158245
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 120
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
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27936 Visits: 39921
--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
Michael Lee-158245
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 120
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
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5680 Visits: 11771
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. :-P
Michael Lee-158245
Michael Lee-158245
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 120
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
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23273 Visits: 9730
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
Vincent Vancalbergh
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 43
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. :-P



Thanks, this solved my problem today! :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search