Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using functions on remote/Linked server Expand / Collapse
Author
Message
Posted Thursday, October 28, 2010 12:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, October 28, 2010 12:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 12,962, Visits: 32,502
--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
Posted Thursday, October 28, 2010 1:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, October 28, 2010 1:21 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 3,113, Visits: 11,541
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.







Post #1012603
Posted Friday, October 29, 2010 11:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Friday, October 29, 2010 12:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Wednesday, November 7, 2012 3:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 7, 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.



Thanks, this solved my problem today!
Post #1381859
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse