SSMS Call scalar function from linked server

Question

I want to call a scalar function from my linked server [SQLCRM]

select 
   o.EstimatedValue [EstimatedSum],
   (select * from OPENQUERY([SQLCRM], 'convert(date, [DBNAME].dbo.fn_UTCToTzSpecificLocalTime(o.tisa_registrationdate, 
                       us.TimeZoneBias,
                       us.TimeZoneDaylightBias,
                       us.TimeZoneDaylightYear,
                       us.TimeZoneDaylightMonth,
                       us.TimeZoneDaylightDay,
                       us.TimeZoneDaylightHour,
                       us.TimeZoneDaylightMinute,
                       us.TimeZoneDaylightSecond,
                       0,
                       us.TimeZoneDaylightDayOfWeek,
                       us.TimeZoneStandardBias,
                       us.TimeZoneStandardYear,
                       us.TimeZoneStandardMonth,
                       us.TimeZoneStandardDay,
                       us.TimeZoneStandardHour,
                       us.TimeZoneStandardMinute,
                       us.TimeZoneStandardSecond,
                       0,
                       us.TimeZoneStandardDayOfWeek),103)')) as DateKey

   from [SQLCRM].[DBNAME].dbo.Opportunity o left join
            [SQLCRM].[DBNAME].dbo.tisa_Article a on o.OpportunityId  = a.tisa_OpportunitySalesId left join
            [SQLCRM].[DBNAME].dbo.SystemUserBase u on u.SystemUserId = 'DB159B57-4A3D-E311-93EC-00155D46BF05' left join
            [SQLCRM].[DBNAME].dbo.UserSettingsBase us on us.SystemUserId = u.SystemUserId

After run this query i'm getting the following error:

OLE DB provider "SQLNCLI11" for linked server "SQLCRM" returned message "Deferred prepare could not be completed.".

Which is the correct format to call the function? Thank you

Already asked on here, try this solution. https://stackoverflow.com/questions/4125820/sql-server-how-to-call-a-user-defined-function-udf-on-linked-server — JonTout, Dec 18 '18 at 11:41

score 0 · Answer 1 · edited Dec 18 '18 at 12:46

0

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
OPENROWSET('SQLNCLI', 
'Server=ipHere\ExtentionNameifHas;Database=DBName;Uid=userName;PWD=Password;'
,'SET FMTONLY OFF;SET NOCOUNT ON;Paste the query over here'

edited Dec 18 '18 at 12:46

kalehmann

4,821
6
26
36

answered Dec 18 '18 at 11:30

Chidambar

1
1

SSMS Call scalar function from linked server

1 Answers1