SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to join a local table to a remote table by way of a linked server


How to join a local table to a remote table by way of a linked server

Author
Message
Stan Kulp-439977
Stan Kulp-439977
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4854 Visits: 1178
Comments posted to this topic are about the item How to join a local table to a remote table by way of a linked server
balajipalakkattu
balajipalakkattu
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 56
Thanks for the details. I am using linked server for accessing data from remote servers. But I wonder whether I need to handle the transactions manually, if I use linked servers for writing data.!! what you think ?
Nakul Vachhrajani
Nakul Vachhrajani
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4940 Visits: 2152
You may be interested in reading one of my posts, which explores the use of EXECUTE...AT clause for executing queries against linked servers: http://beyondrelational.com/blogs/nakul/archive/2011/01/10/executing-queries-against-linked-servers.aspx

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
henkvl
henkvl
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 144
Hi, any specific reason way you don't use the openquery command?
Nakul Vachhrajani
Nakul Vachhrajani
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4940 Visits: 2152
I believe the reason would be because OPEN queries require that Adhoc queries be enabled on the SQL Server, which, depending upon the environment and the situation, might be considered a security loop-hole.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
DavidBridgeTechnology.com
DavidBridgeTechnology.com
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 238
Linked serverd id quite a big subject with the security levels, ditributed transactions etc.and I think this post could have gone in to a little more detail without detracting from the subject matter.

I think Openquery was worth mentioning and I dont think it was avoided on account of security as the way this linked server example has been set up is highly inssecure anyway.

2 interesting points I think are worth making though.

As a general rule, don't do linked security in the way noted in the article as all connections will be executed remotely with this remote user regardless of the loacal user. Better to map all allowed local users to a remote login and best to use windows security locally if possible.

Secondly there isa gothcha thats worth noting. The linked server with inner join will not work woth XML columns. You would have to use openquery and convert the remote xml field to varchar and then convert it back again.

And finally I would like to mention that, touched on in the article, the copying of a remote table to a local temp table can often be a good idea as this avoids the need for a distributed transaction and therefore reduces overhead and complexity of the security system.

Otherwise I thought the article was a pretty good introduction.

Dave

David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
Ken Jensen
Ken Jensen
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 50
Copying the entire remote table into a local temp table is not an option when the remote table has millions of rows. Have a look at the REMOTE join hint. There are a couple of caveats with this hint, but if one can properly index the remote table, using this join hint provides very good performance.
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54194 Visits: 12424
I agree that it's best to map local logins to an account on the remote server, rather than have all logins access the linked server using a default security context. Otherwise, even an account with nothing but PUBLIC access will have equal access to the linked server.

Mapping local logins to remote accounts can be done even when the remote server doesn't have (or doesn't support) windows authentication. Here is how one my linked servers are configured where the remote server is Oracle. A windows domain account, MYCORP\ReportingUser, can access the linked server under the context of the remote Oracle account, RPT_USER.

Local server login to remote server login mappings:

Local Login: MYCORP\ReportingUser
Impersonate: No
Remote User: RPT_USER (account on remote Oracle server)
Remote Password: ########

For a login not defined in the above list, connections will:
- Not be made


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
sherifffruitfly
sherifffruitfly
Mr or Mrs. 500
Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)

Group: General Forum Members
Points: 578 Visits: 427
When I require live data, I almost always join against OPENQUERY resultsets - much, much, MUCH faster than directly joining to the remote server.

Or else an SSIS job.

-sff
benavraham50
benavraham50
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 29
Nice article. I find in actual use where the person creating the linked server is a company level DBA that providing scripts is a necessary component. Also be aware that you may need to add multiple users (we did). There is also some detail in regards to linking to non-SQL Server servers (Oracle?) that would be useful to see.

Joshua
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search