Intro: I want to upload data from our local MS-SQL servers into a "cloud-based" DB for a customer portal thingy.
I only want to upload any changes/additions that have been made since I last did an update, so I have added a timestamp/rowversion column to all the tables I'm interested in, so I can ask for any changed/added rows since I last looked.
The upload processing will run on one SQL server (called "Processor") which will be fetching data from two other servers (I have added linked servers for each on Processor)
Is there a way that I can, using a stored procedure running on Processor, get the min_active_rowversion() from the other two (linked) servers?
I have tried (on Processor):
select min_active_rowversion() <-- Error, Database 'linkedserver1' does not exist
select min_active_rowversion() from linkedserver1.database <-- this returns the rowversion from Processor, not linkedserver1
Note: There are multiple tables on the two linked servers that I will be querying, so I don't really want to do:
select top 1 [timestamp] from linkedserver1.database.table order by [timestamp] desc
to fetch it, because I'd have to ask every table and then find the highest of the lot.