Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


how to call or update data through linked server

Posted on 2007-11-27
Medium Priority
Last Modified: 2010-08-05
I'm interested to know what other query that I can use to select or update data through a linkedserver, at of now I'm using below query through my linked server, but not so sure whether is good or bad way to select data

SELECT * FROM OPENQUERY(kiptox,'SELECT top 20000 * FROM northwind..Index_views_Custom')
Question by:motioneye
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20357624
there is nothing "wrong" with using openquery. good or bad... well, what are your alternatives? if you have no alternative, there is no "discussion" about it, only possibly if it is usable or not.
LVL 11

Accepted Solution

Otana earned 750 total points
ID: 20357660
You can also do it this way:

SELECT * FROM [SERVERNAME].[northwind].[Index_views_Custom]

Author Comment

ID: 20358609
Yes how about this one as per advised by Otana??

SELECT * FROM [SERVERNAME].[northwind].[Index_views_Custom]

as comparisons to use openquery with a normal select statement??
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 750 total points
ID: 20358728
you need the 4 partname, then:
SELECT * FROM [SERVERNAME].[northwind]..[Index_views_Custom]

SELECT * FROM [SERVERNAME].[northwind].dbo.[Index_views_Custom]

Author Comment

ID: 20359632
the reason why I asking what the benefit between two is bcoz when I anylze the query through estimated execution plan, a query with T-sql openquery performing better since the subtree cost are lower compare to the normal select.

Using openquery subtree cost =3.36333

StmtText                                                                                             EstimateCPU   AvgRowSize  TotalSubtreeCost
--------------------------------------------------------------------------------------------------   ------------- ----------- ----------------
SELECT * FROM OPENQUERY(ktopix,'SELECT top 20000 * FROM northwind..Index_views_Custom')              NULL          NULL        3.363333        
  |--Remote Scan(SOURCE:(Ktopix), OBJECT:(SELECT top 20000 * FROM northwind..Index_views_Custom))    3.363333      90          3.363333        

(2 row(s) affected)

another query with normal T-sql TotalSubtreeCost

StmtText                                                                                                                                                                                                          EstimateCPU   AvgRowSize  TotalSubtreeCost=6.676667
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ------------- ----------- ----------------

SELECT top 20000 * FROM [KYAHYA].[northwind].[DBO].[Index_views_Custom]                                                                                                                                           1 NULL        NULL      6.676667         N
  |--Remote Query(SOURCE:(KYAHYA), QUERY:(SELECT TOP 20000 "Tbl1002"."CustomID" "Col1004","Tbl1002"."CustomName" "Col1005","Tbl1002"."Phone" "Col1006" FROM "northwind"."DBO"."Index_views_Custom" "Tbl1002"))    6.676667      90          6.676667        

(2 row(s) affected)

any idea why it behave this such way?
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 750 total points
ID: 20360183
I regulary see also that openquery performs better than the other code, and I guess this is because sql server will try to "pass-through" as much as possible to the remote server...

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question