InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 19:06:16 GMT20http://www.sqlservercentral.com/Forums/Topic1012560-1292-1.aspx[quote][b]Michael Valentine Jones (10/28/2010)[/b][hr]Something like this should do what you want:[code="sql"]select a.*from OPENQUERY ( RHSQL ,'Select Brook2.dbo.GetCurrentAdmissionDate(1) as AdminDate, Brook2.dbo.GetDOTFromDOA(1), Brook2.dbo.GetCurrentAdmissionDate(1) as DischargeDatefrom Brook2.dbo.Admission' ) a[/code]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[/quote]Thanks, this solved my problem today! :-)Wed, 07 Nov 2012 03:18:29 GMTVincent Vancalberghhttp://www.sqlservercentral.com/Forums/Topic1012560-1292-1.aspxYou're missing the database name in that. Needs to be between the server name and the schema name ("dbo").Fri, 29 Oct 2010 12:42:19 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1012560-1292-1.aspxHi 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 exampleSELECT p.PatientID, p.LastName, p.FirstName, '1' as ProgramIdFROM [BP-SQL\BPSQLSERVER].dbo.PATIENTS pI've also tried although I had a feeling these would not work[BP-SQL.BPSQLSERVER][BPSQLSERVER][BP-SQL]Please help.Fri, 29 Oct 2010 11:35:47 GMTMichael Lee-158245http://www.sqlservercentral.com/Forums/Topic1012560-1292-1.aspxSomething like this should do what you want:[code="sql"]select a.*from OPENQUERY ( RHSQL ,'Select Brook2.dbo.GetCurrentAdmissionDate(1) as AdminDate, Brook2.dbo.GetDOTFromDOA(1), Brook2.dbo.GetCurrentAdmissionDate(1) as DischargeDatefrom Brook2.dbo.Admission' ) a[/code]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. :-PThu, 28 Oct 2010 13:21:34 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1012560-1292-1.aspxI 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 DatetimeASBEGINDECLARE @out_date Datetimereturn (SELECT DischargeDate as DOT FROM [RH-SQL].Brook2.dbo.Admission WHERE AdmissionDate = @DOA and PatientId = @PatientId) endIt'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.Thu, 28 Oct 2010 13:04:22 GMTMichael Lee-158245http://www.sqlservercentral.com/Forums/Topic1012560-1292-1.aspx--snip edited wrong answer!--Thu, 28 Oct 2010 12:14:32 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1012560-1292-1.aspxI 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 thisSelect [RHSQL].Brook2.dbo.GetCurrentAdmissionDate(@PatientId) as AdminDate,[RHSQL].Brook2.dbo.GetDOTFromDOA(@PatientId, [RHSQL].Brook2.dbo.GetCurrentAdmissionDate(@PatientId)) as DischargeDatefrom [RHSQL].Brook2.dbo.AdmissionWhen 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 helpThanksMichaelThu, 28 Oct 2010 12:11:57 GMTMichael Lee-158245