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. stackoverflow.com/questions/4125820/… — JonTout
– JonTout, Commented Dec 18, 2018 at 11:41

kalehmann · Accepted Answer · 2018-12-18 12:46:26Z

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, 2018 at 12:46

kalehmann

5,0896 gold badges29 silver badges37 bronze badges

answered Dec 18, 2018 at 11:30

Chidambar

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSMS Call scalar function from linked server

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related