-- UPDATE dbo.tblRSPBooking
-- SET CancelDate = vwPartnerTransactions.TransactionDate,
-- IsCanceled = vwPartnerTransactions.IsCancel
select *
FROM SERVER1.VSAPI.dbo.vwPartnerTransactions
WHERE PartnerId = 1006
AND IsCancel = 1
AND TransactionDate >= @LastCancel
AND tblRSPBooking.MWTransactionID = ReversalTransactionID
I need to do a select on this one.
I get The multi-part identifier "tblRSPBooking.MWTransactionID" could not be bound.
on the AND tblRSPBooking.MWTransactionID = ReversalTransactionID
Is it safe to assume that in order to do a select i need to join MWTransactionID and ReversalTransactionID
like
select ......etc
from SERVER1.VSAPI.dbo.vwPartnerTransactions T
join tblRSPBooking R on T.MWTransactionID = R.ReversalTransactionID
?
Thanks.
Soemthing like this perhaps:
-- UPDATE R
-- SET CancelDate = vwPartnerTransactions.TransactionDate,
-- IsCanceled = vwPartnerTransactions.IsCancel
select *
from SERVER1.VSAPI.dbo.vwPartnerTransactions AS T
join dbo.tblRSPBooking AS R
on T.MWTransactionID = R.ReversalTransactionID
WHERE PartnerId = 1006
AND IsCancel = 1
AND TransactionDate <= @LastCancel
I strongly recommend that you do not use either 3-part of 4-part naming in your code - if the remote Server and/or DB name changes you will have to change everything all through your code ...
Set up a SYNONYM in your database and use that instead. Then if the Remote changes you can just drop the original synonym and create a new one (with the same name, pointing to the correct Server/DB)
Your other, likely, problem is that this may run like a dog ... we try to never join a local table with a remote server but instead pull the minimum amount of data from the remote server using OPENQUERY(SERVER1, "SELECT ... FROM ..."). Dunno how easy that would be in this case - if the list of ReversalTransactionID is modest you could construct them into an IN list perhaps. At the least the
WHERE PartnerId = 1006
AND IsCancel = 1
AND TransactionDate >= @LastCancel -- Syntax in OPENQUERY will be a bit different
can be included, and that will all DEFINITELY! be processed at the remote. With a straight join there is a risk that SQL pulls far more data than you would like to the Local and THEN does the Filter and Join, and when that happens it will be slow.
1 Like
Hi.
I've never used OPENQUERY.
We have sp's that use master.dbo.sp_addlinkedserver, master.dbo.sp_serveroption etc to connect
or using dynamic SQL with EXEC remote procedure.
Will mark this down , may be useful , as you have said, if we use linked server calls.
Thanks.
Probably the only thing you need to watch out for, t=with 4-part naming, is where you have a JOIN between Remote and Local servers' database tables.
A 4-part named query that ONLY includes tables from the Remote Server should be fine, as should be an EXEC of a remote SProc. All OPENQUERY does is to clearly identify the code which is a pass-through query to the Remote Server, so there is ZERO! chance of anything (inside that query) being executed locally.
What you want to avoid is where SQL pulls millions of rows and THEN makes the JOIN and THEN finds that it only needs a dozen of the million records it has just spent 10 minutes pulling locally.
For queries that DO need Remote and Local tables joining we create a replicated or enquiry-only-copy database locally, and then the whole query becomes "local" instead of part-local and part-remote.
1 Like
Actually this is very interesting.
I was struggling a couple of months ago with remove server calls and after hours or research I'm under the impression that remote SQL will retrieve all it's rows before making comparisons with local database.
I was trying to create a remote temp table and pull the rows from there to local. At least that was supposed to be a solution for faster retrieve but again you include all the rows in the temp table.
Is that the case?
Will see it Tuesday as we have 3 days of, for local celebrations in Greece.
Thanks.
Not always true and "it depends". If you have SUBSTR() functions in a JOIN statement between Remote and Local Servers then, yes, SQL will most probably pull-it-all-locally and then do the compare ... and it will take ... forever 
If you have
JOIN RemoteServer.RemoteDatabase.dbo.RemoteTable AS R
ON R.SomeColumn = @MyParameter
then, particularly if R.SomeColumn is indexed, then chances are good that that will all happen at the remote end.
If you use OPENQUERY it will definitely happen at the remote end.
Sometimes SQL Server will even do worse than pull every row across locally - sometimes it cannot figure out the process and ends up creating a cursor that reads one row from the remote system, processes it locally - reads the next row, etc....
This can take a query that would normally run in just a couple of minutes to a query that takes hours to complete.
With that said - if you need data from a remote system and it will be used in multiple queries then I recommend building a process to pull that data into a local table populated on a set schedule. Generally for reporting purposes you don't need current data - so a daily extract/load process will satisfy those requirements.
A lot of times I see multiple 'report' queries that pull the same data across the linked server every time the report is run...which just causes unnecessary usage of tempdb and network resources which delay the availability of the report.
As a current solution we are mostly using the addlinkedserver, we then put the data in an existing table at the remote server and retrieve data to the main server from there.
I'm not sure if this is optimal but the running times of the queries are hundred or thousand percent better than joining the linked server.