Using functions on remote/Linked server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Using functions on remote/Linked server

Using functions on remote/Linked server

Rate Topic

Display Mode

Topic Options

Author

Message

Michael Lee-158245

Posted Thursday, October 28, 2010 12:11 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 7:32 AM
Points: 36, 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

Post #1012560

Lowell

Posted Thursday, October 28, 2010 12:14 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 11,792, Visits: 28,078

--snip edited wrong answer!--

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1012561

Michael Lee-158245

Posted Thursday, October 28, 2010 1:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 7:32 AM
Points: 36, 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.

Post #1012595

Michael Valentine Jones

Posted Thursday, October 28, 2010 1:21 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:35 PM
Points: 2,969, Visits: 10,620

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. Tongue

Post #1012603

Michael Lee-158245

Posted Friday, October 29, 2010 11:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 7:32 AM
Points: 36, 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.

Post #1013243

GSquared

Posted Friday, October 29, 2010 12:42 PM

SSCoach

Group: General Forum Members
Last Login: Monday, June 17, 2013 1:45 PM
Points: 15,442, Visits: 9,572

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

Post #1013279

Vincent Vancalbergh

Posted Wednesday, November 07, 2012 3:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 3:17 AM
Points: 30, 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. Tongue

Thanks, this solved my problem today! Smile

Post #1381859

« Prev Topic | Next Topic »

Permissions