February 24, 2016 at 9:22 am
I get the following error when running a query with this udf, [BRO-DR01].testfdb.dbo.fnInsurance(SourceID, VisitID, 1). So, the function is on server, BRO-DR01, but I'm running the query on server, SH-SQL01. Any thoughts?
Msg 207, Level 16, State 1, Procedure spDischargeFollowUp_61, Line 55
Invalid column name 'BRO-DR01'.
February 24, 2016 at 9:43 am
How straightforward is your function 'dbo.fnInsurance'? Is it possible to create a function from SH-SQL01 utilising the linked server?
I think one option is to use OpenQuery: https://msdn.microsoft.com/en-gb/library/ms188427(v=sql.100).aspx
E.g
SELECT *
FROM OPENQUERY(BRO-DR01,'testfdb.dbo.fnInsurance(1, 1, 1)')
There's drawbacks though - you can't use parameters in OpenQuery so would need dynamic sql which opens up another can of worms. I've not used it in practice so couldn't recommend it, but if needed you can research it as an option (or someone here can advise). Not sure what performance implications are either.
February 24, 2016 at 9:54 am
Yuck. In my case the function eliminates a table join so, maybe I'll just do the join.
February 24, 2016 at 10:24 am
You would get a better answer if you posted the query that is giving you the error. Without that all you will get is a shot in the dark.
February 24, 2016 at 11:03 am
I understand. I'm going to by pass the function and join the tables to get the data.
February 24, 2016 at 12:56 pm
Are you still pulling data across the linked server?
February 25, 2016 at 4:02 am
Yes.
February 25, 2016 at 9:13 am
NineIron (2/25/2016)
Yes.
Please post the original query and the query you are using now without the function.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply