Remote function reference is not allowed - MS SQL Server 2016

Hi All

I'm hoping to get support on Microsoft SQL 2016 here, I'm trying to execute the alterations I did to Stored Procedure called Siriusware_Update_StaffPassBalance. The error message I get when it faiils to modify the Stored Procedure is below

Msg 344, Level 16, State 1, Procedure Siriusware_Update_StaffPassBalance, Line 106 [Batch Start Line 9]
Remote function reference 'sv.Sirius.dbo.siriusfn_LogChangesToXML' is not allowed, and the column name 'sv' could not be found or is ambiguous.

SV is a LinkedServer I setup from our end, this LinkedServer works well as it is being used in several Stored Procedures and Views that talk with the databases on the remote server from our end. Additionally, the below information is useful

DB Name: TunnelCosmos, this sits on our end in our local SQL instance
DB Name: Sirius, this sits in the remote server identified as SV

Below is the query in the Siriusware_Update_StaffPassBalance stored procedure

USE [TunnelCosmos]
GO

/****** Object:  StoredProcedure [dbo].[Siriusware_Update_StaffPassBalance]    Script Date: 12/04/2018 10:29:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Siriusware_Update_StaffPassBalance]

DECLARE @FY_Start DATETIME
SET @FY_Start = CASE WHEN MONTH(GETDATE()) <=6 THEN CAST((CAST(YEAR(GETDATE())-1 AS VARCHAR(4))+'-07-01') AS DATETIME) ELSE CAST((CAST(YEAR(GETDATE()) AS VARCHAR(4))+'-07-01') AS DATETIME) END

SELECT
	 *
	,staff_mins * sm_accrue AS sm_max_accrue
	,CASE WHEN points1 + staff_mins > (staff_mins * sm_accrue) THEN (staff_mins * sm_accrue) ELSE points1 + staff_mins END  as points_adjusted
	,0 AS Processed
INTO #StaffPasses
FROM (
	SELECT
		 g.guest_no
		,g.first_name
		,g.last_name
		,g.e_mail
		,CAST(dbo.Decrypt_base64((CAST(g.memo_2 as XML)).query('//Mins').value('.','varchar(200)')) AS INT) AS staff_mins
		,CAST(dbo.Decrypt_base64((CAST(g.memo_2 as XML)).query('//Accrue').value('.','varchar(200)')) AS INT) AS sm_accrue
		,gp.pass_no
		,gp.points1
		,gp.item
		--v1.1
		,ISNULL((SELECT CAST(ROUND(SUM(minutes_calc),0) AS INT) AS Mins_YTD 
				FROM Siriusware_Flights WHERE ISNULL(flyer_guest_no,purchaser_guest_no) = g.guest_no AND flight_item = 'STAFFDEV1' 
				AND session_date >= @FY_Start ),0) AS Mins_YTD
	FROM sv.Sirius.dbo.guests g
	INNER JOIN sv.Sirius.dbo.gst_pass gp ON gp.guest_no = g.guest_no AND gp.department = 'PASSES' and gp.category = 'STAFF'
	WHERE gp.expires > GETDATE()
	) X


----remove passes which dont need to change
--DELETE FROM #StaffPasses WHERE points1 = points_adjusted

--variable dec
DECLARE @pass_no NUMERIC(17,0)
DECLARE @guest_no NUMERIC(17,0)
DECLARE @NextNumber NUMERIC(17,0)
DECLARE @xmlInserted VARCHAR(MAX)
DECLARE @xmlDeleted VARCHAR(MAX)
DECLARE @xmlChanges VARCHAR(MAX)
DECLARE @xmlCurrentState VARCHAR(MAX)
DECLARE @TagLine VARCHAR(80)
DECLARE @DateTime DATETIME
DECLARE @Email_Body VARCHAR(MAX)
DECLARE @Email_Subject VARCHAR(200)
DECLARE @Email_Recipient VARCHAR(200)

SET @Email_Subject = 'Your Staff Development Time Minutes Balance Update'

--loop
WHILE (SELECT COUNT(*) FROM #StaffPasses WHERE Processed = 0) > 0
BEGIN
	
	SELECT TOP 1 @pass_no = pass_no, @guest_no = guest_no FROM #StaffPasses WHERE Processed = 0

	SET @Email_Body = ''

	IF (SELECT (points1 - points_adjusted) as test FROM #StaffPasses WHERE pass_no = @pass_no) <> 0
	BEGIN  

		SET @DateTime = GETDATE()

		--Get the next number for gst_actv
		UPDATE sv.Sirius.dbo.sequence SET @NextNumber = CAST(next_val as varchar(10))+'000007', next_val = next_val +1  WHERE name = 'K_GST_ACTV'

		--select old data
		SET @xmlDeleted = (SELECT TOP 1
			gp.points1
		FROM sv.Sirius.dbo.gst_pass gp
		WHERE gp.pass_no = @pass_no
		FOR xml PATH (''), ELEMENTS ABSENT)

		--do the update
		UPDATE gp
		SET points1 = sp.points_adjusted
		FROM sv.Sirius.dbo.gst_pass gp
		INNER JOIN #StaffPasses sp ON sp.pass_no = gp.pass_no

		--select new changed
		SET @xmlInserted = (SELECT TOP 1
			gp.points1
		FROM sv.Sirius.dbo.gst_pass gp
		WHERE gp.pass_no = @pass_no
		FOR xml PATH (''), ELEMENTS ABSENT)


		set @xmlChanges = sv.Sirius.dbo.siriusfn_LogChangesToXML(@xmlInserted, @xmlDeleted, '', 'gst_pass')
		SET @xmlCurrentState = sv.Sirius.dbo.siriusfn_LogCurrentStateToXML(@xmlInserted, 'gst_pass');
		set @xmlCurrentState = @xmlChanges + @xmlCurrentState

		SET @TagLine = 'Modify Pass #' + CAST(@pass_no AS varchar(20)) + ' - Staff Update Automation'

		EXEC sv.Sirius.dbo.siriussp_CreateGuestActivityRecord	@ActivID = @NextNumber,
																		@SiteNo = 7,
																		@ActivType = 101,
																		@TagLine = @TagLine,
																		@GuestNo = @guest_no,
																		@RefNo = @pass_no,
																		@Details = @xmlCurrentState,
																		@Dt = @DateTime,
																		@Person = 'STAFFINT',
																		@Location = 'STAFFINT';

	END

	IF (SELECT item from #StaffPasses WHERE pass_no = @pass_no) NOT IN ('DIRECTOR')
	BEGIN

		--Email balance & usage
		SELECT
			 @Email_Recipient = e_mail 
			,@Email_Body='<font color="Black" style="font-family: Lucida Sans Unicode, Lucida Grande, Sans-Serif;"><p>Current Staff Development Time Balance: '+CAST(ISNULL(points_adjusted,0) AS VARCHAR(10))+' Minutes <br><font size=2>*This balance is inclusive of your allocation for the new staff training week.</font></p>
			 <p>Staff Development Time Used this Financial Year: '+CAST(ISNULL(Mins_YTD,0) AS VARCHAR(10))+' Minutes</p>'
		FROM #StaffPasses sp WHERE pass_no = @pass_no
	
		--Send email
		EXEC msdb.dbo.sp_send_dbmail @recipients=@Email_Recipient,
		@subject = @Email_Subject,
		@body = @Email_Body,
		@body_format = 'HTML',
		@profile_name = 'Reports' ;

	END

	--Flag processed
	UPDATE #StaffPasses SET Processed = 1 WHERE pass_no = @pass_no

	WAITFOR DELAY '00:00:02';

END

--Clean Up
DROP TABLE #StaffPasses
GO

Open in new window


I'm hoping to get this working, any help is greatly appreciated.
Stevie ZakhourAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You can call remote scalar function only using EXECUTE. E.g. this should work:

EXECUTE @xmlChanges = sv.Sirius.dbo.siriusfn_LogChangesToXML(@xmlInserted, @xmlDeleted, '', 'gst_pass');

Open in new window

Anything else needs to be done using OPENQUERY().
0
Stevie ZakhourAuthor Commented:
Thanks Ste5an, below is the changes

Before Changes

set @xmlChanges = sv.Sirius.dbo.siriusfn_LogChangesToXML(@xmlInserted, @xmlDeleted, '', 'gst_pass')
SET @xmlCurrentState = sv.Sirius.dbo.siriusfn_LogCurrentStateToXML(@xmlInserted, 'gst_pass');

Open in new window


After Changes

EXECUTE @xmlChanges = SV.Sirius.dbo.siriusfn_LogChangesToXML(@xmlInserted, @xmlDeleted, '', 'gst_pass')
EXECUTE @xmlCurrentState = SV.Sirius.dbo.siriusfn_LogCurrentStateToXML(@xmlInserted, 'gst_pass');

Open in new window


When I execute, SQL spits out the below error

Msg 102, Level 15, State 1, Procedure Siriusware_Update_StaffPassBalance, Line 106 [Batch Start Line 9]
Incorrect syntax near '@xmlInserted'.
Msg 102, Level 15, State 1, Procedure Siriusware_Update_StaffPassBalance, Line 107 [Batch Start Line 9]
Incorrect syntax near '@xmlInserted'.

I would assume in this case OPENQUERY() needs to be used, I never used it in the past, how can it be used?

Thanks again Ste5an!
0
ste5anSenior DeveloperCommented:
Remove the parentheses. They are a copy'n'paste artefact.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stevie ZakhourAuthor Commented:
Thank you Ste5an, that worked brilliantly!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Stored Proc

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.