How to call a UDF from a linked server inside and select query

Question

Post reply

How to call a UDF from a linked server inside and select query

dndaughtery

SSChampion

Points: 11286
More actions
July 8, 2011 at 12:14 pm

#243865

I have been trying the following to do an insert into a table using a UDF which is located on a linked server but I keep getting an error that states
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'exec'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Can someone help me with this please?
INSERT INTO [ToolKit_Prod].[dbo].[MedicalLicense]
([ProviderID]
,[MedicalLicense]
,[ProviderLicense_K]
,[State]
,[CPR_ProviderID]
,[LastModDate]
,[LastModUser])
SELECT pic.providerID, cr.[TX License]
,(exec MedCredent.VisualCactus.dbo.sp_executesql N'SELECT VisualCactus.dbo.fnConCatLicense(@input)',N'@input char(10)',@input=pic.ProviderID)
, 'TX', tp.ProviderID, GETDATE(), 'Initial Load'
FROM [ToolKit_Prod].[dbo].[Combined_Roster] cr
INNER JOIN Cactus.dbo.Provider_Info_Complete pic ON cr.NPI = pic.NPI
INNER JOIN ToolKit_Prod.dbo.Providers tp ON tp.[CactusProv_ID] = pic.ProviderID
WHERE [Entity Status] Like '%THPG%'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

not certain but possibly cross apply (http://msdn.microsoft.com/en-us/library/ms175156.aspx) might work.

something like this

INSERT INTO [ToolKit_Prod].[dbo].[MedicalLicense]

([ProviderID]

,[MedicalLicense]

,[ProviderLicense_K]

,[State]

,[CPR_ProviderID]

,[LastModDate]

,[LastModUser])

SELECT pic.providerID, cr.[TX License]

,a.MedicalLicense

, 'TX', tp.ProviderID, GETDATE(), 'Initial Load'

FROM [ToolKit_Prod].[dbo].[Combined_Roster] cr

INNER JOIN Cactus.dbo.Provider_Info_Complete pic ON cr.NPI = pic.NPI

INNER JOIN ToolKit_Prod.dbo.Providers tp ON tp.[CactusProv_ID] = pic.ProviderID

CROSS APPLY VisualCactus.dbo.fnConCatLicense(pic.ProviderID) as a

WHERE [Entity Status] Like '%THPG%'

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 2

Doesn't look like you can do it that way.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills