Solved

how to call or update data through linked server

Posted on 2007-11-27
6
948 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 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.
0
 
LVL 11

Accepted Solution

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

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

Author Comment

by:motioneye
ID: 20358609
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 143

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]
or

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

Author Comment

by:motioneye
ID: 20359632
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 143

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...
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

830 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