2

I am querying a remote server for which I only have read permissions. I set up a local server with a UDF (since I cannot create one on the remote) that I would like to utilize for the query. When I try executing the query, I receive the error

Msg 344, Level 16, State 1
Remote function reference 'localhost\SQLDEV.db.dbo.udf_StripHTML' is not allowed, and the column name 'localhost\SQLDEV' could not be found or is ambiguous.

I tried looking for solutions, but nothing seemed remotely (haha) close to my situation as all other solutions were for executing a remote function against a local query, or trying to pass variables with OPENQUERY, etc. Is there a simple way to achieve this query with only read access to the remote, master db?

SELECT [localhost\SQLDEV].[db].[dbo].[udf_StripHTML](notes.note_text)
FROM event_log AS el
INNER JOIN dbo.progress_note as notes 
on notes.event_log_id = el.event_log_id
Improve this question
1
  • Thank you for the edits; I figured the whole query was overkill but wasn't sure how much context is usually preferred when posting questions. Commented May 14, 2015 at 18:44

2 Answers 2

Reset to default
2

Seems (based on the name, anyway) like a pretty basic scalar UDF that just massages strings, won't be subject to a lot of changes over time, and doesn't rely on any data. So, I suggest you make a copy of the function on the source server, and fight whatever rule is preventing you from doing so (it shouldn't be hard if you can justify the use of the function, and describe what kind of hack you're trying to use as a workaround to the rule). Note that the function doesn't have to live in the same database, it just needs to be on the same server.

(Even if the function does rely on data, and it's clear in your case it doesn't, the local copy of the function could reference the data in the other server, using 4-part name(s), without referencing any functions.)

DRY is not always worth pursuing.

Improve this answer
1
  • Unfortunately the remote server is hosted by a completely different agency, and such a change request would either be denied completely or take days/weeks of cutting through red tape. Thank you for the perspective though - I agree that sometimes it's easier to just keep pushing back until you get your way! Commented May 14, 2015 at 18:36
1

I was able to solve this problem by flipping it around and executing the query from my local server, setting up the remote as a linked server and using 4-part names on the joins. SSMS gave me the error underlines but the query worked as expected.

Improve this answer
1
  • That works for you to execute the query, but what about anyone else who has to run anything similar on the remote server? Commented May 14, 2015 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.