|
|
Posted by Erland Sommarskog on 10/11/05 00:29
Ootyguy (npravin@hotmail.com) writes:
> Let me clarify that I am not joining 15 tables against 15 remote
> servers in one query, it is more like 6 linked tables and 2 local
> tables against each database that is linked and this same query is
> repeated against all the remote linked servers to gather data, create a
> text file and ftp the file to a different location.
>
> Since this is a job, it doesn't matter if it takes upto 45mins or an
> hour, the problem is it works ok when the tables joined on the linked
> servers are small, as more databases are added and the tables are large
> it takes forever to execute.
If you join the six remote tables alone, how much data you get? Could
you run that query through OPENQUERY? Or would that still be too much
data to bring over?
Hm, in your OPENQUERY you could actually join to your local tables, that
is:
SELECT * FROM
OPENQUERY(REMOTE, 'SELECT ...
FROM localtable ..
JOIN YOURSERVER.db.dbo.tbl ')
Of course, you need to convince the other DBAs to set up linked servers
to your servers. But if they are not willing to co-operate, you should
probably not pull this data at all. Or you should talk with a manager.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|