Post reply

JOIN to a remote table... easy but...

  • October 19, 2020 at 1:07 am

    #3798667

    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

    #3798736

    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