can you call a linked server within a function

Question

Post reply

can you call a linked server within a function

Denise McMillan

SSCertifiable

Points: 6216
More actions
June 25, 2014 at 9:09 am

#309476

I'm trying to create a function that will select using a linked server. Am getting error "remote table-value function calls not allowed"
But I'm not calling a funtion with the linked server. Is is possible to use a link within a function? This is in SQL 2008R2.
Thanks very much.
Like:
CREATE FUNCTION [dbo].[GetLocationCodeJOE]
( @IntCode as integer )
RETURNS char(20)
AS
BEGIN
--declare variable we will return
DECLARE @ExtCode as char(20)
SET @ExtCode = ''
SET @ExtCode = (SELECT DISTINCT [loc_ext_id] FROM [linkedservername].[databasename].[dbo].[tablename](NOLOCK) WHERE [loc_int_id] = @IntCode)
-- Return the result of the function
RETURN @ExtCode
END

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

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Try adding "WITH" prior to (NOLOCK) i.e. WITH (NOLOCK)

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 2

Denise McMillan

SSCertifiable

Points: 6216

June 25, 2014 at 1:22 pm

#1724782

Thanks very much, that worked.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Denise McMillan (6/25/2014)
I'm trying to create a function that will select using a linked server. Am getting error "remote table-value function calls not allowed"
But I'm not calling a funtion with the linked server. Is is possible to use a link within a function? This is in SQL 2008R2.
Thanks very much.
Like:
CREATE FUNCTION [dbo].[GetLocationCodeJOE]
( @IntCode as integer )
RETURNS char(20)
AS
BEGIN
--declare variable we will return
DECLARE @ExtCode as char(20)
SET @ExtCode = ''
SET @ExtCode = (SELECT DISTINCT [loc_ext_id] FROM [linkedservername].[databasename].[dbo].[tablename](NOLOCK) WHERE [loc_int_id] = @IntCode)
-- Return the result of the function
RETURN @ExtCode
END

Why a scalar function? Could you create an inline table valued function instead? From what you posted it would be an easy modification and a pretty decent performance boost.

Why the NOLOCK hint? Do you know all the details of what the hint brings to the table?

What happens when you get more than 1 row in your result? Do you know which row will be assigned to your variable? It will be the last one in result set, however you have no way of knowing what that is.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/