Solved

how to call or update data through linked server

Posted on 2007-11-27
6
921 Views
Last Modified: 2010-08-05
Hi,
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')
0
Comment
Question by:motioneye
  • 3
  • 2
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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.
0
 
LVL 11

Accepted Solution

by:
Otana earned 250 total points
Comment Utility
You can also do it this way:

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

Author Comment

by:motioneye
Comment Utility
Hi
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??
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
you need the 4 partname, then:
SELECT * FROM [SERVERNAME].[northwind]..[Index_views_Custom]
or

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

Author Comment

by:motioneye
Comment Utility
Hi,
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?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
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...
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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

7 Experts available now in Live!

Get 1:1 Help Now