Multipart identifier could not be bound with cross apply to linked server function with OPENQUERY

Question

I am trying to make a remote call to a function over a linked server which is not allowed directly ([linkedserver].[database].[dbo].fnGet()] so I'm having to use OPENQUERY to perform that function call. This works great when I hard code in a value as my function parameter, but I need this function call to be dynamic and use a column from a joined (via CROSS APPLY) table.

    SELECT 
    sc.FirstName
    ,sc.LastName
    ,sc.StudentId
    ,p.EnrollId
FROM 
    StudentClasses AS sc
CROSS APPLY
    (
        SELECT * FROM OPENQUERY
        (
            [LinkedServer],'SELECT database.dbo.fnGet(sc.StudentId) AS EnrollId' 
        ) 
    )AS p

unfortunately it keeps failing saying that the multi-part identifier 'sc.StudentId' could not be bound.

I've found a few resources that show how to correct this for local function calls, but nothing for a remote call with OPENQUERY.

Any help would be greatly appreciated.

Steve · Answer 1 · 2016-02-09T18:55:20.607

0

Your OPENQUERY is a string passed to the server in a separate session from your current session. It knows nothing about your StudentClasses AS sc in the main query. You can either rewrite with that in mind (I don't know enough about your data to know if that is possible in your case) or you can change your OPENQUERY so you pass the value into the string. This will be slow, but it will work.

SELECT 
 sc.FirstName
 ,sc.LastName
 ,sc.StudentId
 ,p.EnrollId
FROM 
    StudentClasses AS sc
CROSS APPLY
(
    SELECT * FROM OPENQUERY
    (
        [LinkedServer],'SELECT database.dbo.fnGet(' + Convert(varchar(11),sc.StudentId) + ') AS EnrollId' 
    ) 
)AS p

EDIT:

Something else you could try would be to avoid the OPENQUERY all together:

SELECT 
 sc.FirstName
 ,sc.LastName
 ,sc.StudentId
 ,p.EnrollId
 ,[LinkedServer].database.dbo.fnGet(sc.StudentId) AS EnrollId
FROM 
    StudentClasses AS sc

edited Feb 09 '16 at 18:55

answered Feb 09 '16 at 18:42

Steve

5,585
2
18
32

how would I obtain the value from the table's column (StudentId) to write that into the string I pass to OPENQUERY? When I try the sample code you posted, it dies with the plus sign (+) when trying to concatenate the column value – Blair Holmes Feb 09 '16 at 18:45
Sometimes SQL Server does not like it when you inject strings, this must be one of those times. Typically, when I hit a linked server, I don't use `OPENQUERY` which avoids issue like this. You wouldn't even need the `CROSS APPLY`. Of coarse, I have never done that with a scalar function so SQL Server probably would fail there too. :-) See edit... – Steve Feb 09 '16 at 18:53
as mentioned in my OP, I originally tried what you posted in your edit. You can't call UDFs over a linked server directly thus the need for the OPENQUERY. – Blair Holmes Feb 09 '16 at 18:57
1

Uggg. I have tried everything. The only thing I can come up with is to loop through your data and create/execute some dynamic SQL, which includes the OPENQUERY. Put the results of each into a REAL table and after your loops, query that table. Ugly but possible. – Steve Feb 09 '16 at 19:17

Multipart identifier could not be bound with cross apply to linked server function with OPENQUERY

1 Answers1