how to call or update data through linked server

Posted on 2007-11-27
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
  • 3
  • 2
LVL 142

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 250 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??
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

861 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

Need Help in Real-Time?

Connect with top rated Experts

30 Experts available now in Live!

Get 1:1 Help Now