October 19, 2020 at 1:07 am
So I have a linked server that works fine. I then created a join to the remote table as part of my query. No matter what I did it would put the red squiggles under the server.database.schema.table. I tried putting it all in square brackets, scripting out a select to ensure I had it correctly named. But nothing worked. In desperation I just hit execute on my alter SP code and only then did it give me some helpful info!!
"Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object" So I google this and it turns out this can't be done if the remote table has Geography/Geometry columns - it does. The workaround is to do an openquery but then how do I create a join to an open query?
select A.field1, A.Field2, B.field from localTable A inner join [server].[db].[dbo].
B ON A.id = B.id
This is what I'm trying to get at. It is columns for a SSRS report
October 19, 2020 at 7:49 am
Joining a remote table is not a good move.
it has to be fully loaded to the local server, and then every row of it should be compared to every row in local table. Locking it for the whole duration of that, potentially lengthy, process.
since the table is copied anyway, you may create a temp table with necessary fields, populate it with openquery and then use it in the join.
or you may have a replicated copy of the remote table, but you must make sure that the replication schedule matches the schedule of your report.
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply