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
  • 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. – user3140622 May 14 '15 at 18:44
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.

  • 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! – user3140622 May 14 '15 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.

  • That works for you to execute the query, but what about anyone else who has to run anything similar on the remote server? – Aaron Bertrand May 14 '15 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.