TSQL min_active_rowversion() across linked sever

Hi,

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):
use linkedserver1.database
go
select min_active_rowversion() <-- Error, Database 'linkedserver1' does not exist

Open in new window


select min_active_rowversion() from linkedserver1.database <-- this returns the rowversion from Processor, not linkedserver1

Open in new window


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

Open in new window


to fetch it, because I'd have to ask every table and then find the highest of the lot.
everardsbreweryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan McCauleyData and Analytics ManagerCommented:
You'd probably want to use the undocumented stored procedure, sp_msforeachtable, which will run a command on every table in a database in one fell swoop. Basically, something like this:

exec sp_msforeachtable 'use FileBackup select ''?'', min_active_rowversion() from ?'

Open in new window


The ? is replaced with the table name and the command is executed once for every table. Since you're doing it over a linked server, maybe something like this:

select *
  from openquery(linkedserver1, 'exec sp_msforeachtable ''use FileBackup select ''''?'''', min_active_rowversion() from ?')

Open in new window


That should give the answer for each table - you'd have to run it once for each database and for each linked server, but at least you don't have to manually iterate the tables.
0
everardsbreweryAuthor Commented:
Thanks for your reply.

I'm getting an error when I try to run that last code:
select *
  from openquery(linked1, 'exec sp_msforeachtable ''use SomeDB select ''''?'''', min_active_rowversion() from ?')

OLE DB provider "SQLNCLI10" for linked server "linked1" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'use SomeDB select '?', min_active_rowversion() from ?'.

Open in new window


Where linked1 is the linked server & SomeDB is an actual database on linked1.

Running the sp_msforeachtable on its own on linked1:
 exec sp_msforeachtable 'use SomeDB select ''?'', min_active_rowversion() from ?'

Open in new window


Gives me back lots of individual tables with multiple rows listing the table name & a timestamp (which is always the same, as expected).

Doesn't seem to want to do it over the linked server though.

SQL versions are:
Processor: 10.50.1600
linked1: 9.0.3054

Looking at the code, there seems to be a mismatch in the quote marks - but i can't seem to fix it.  I tried:
select *
  from openquery(linked1, 'exec sp_msforeachtable ''use SomeDB select ''''?'''', min_active_rowversion() from ?''')

Msg 208, Level 16, State 1, Procedure sp_MSforeach_worker, Line 102
Invalid object name '#qtemp'.

Open in new window

0
everardsbreweryAuthor Commented:
On another note, assuming we can get this to work, how would I pull just the timestamp out of the returned data?

Results come back in the results window like this:

unnamed table
  DB1, 0x000000000000A510
  DB1,0x000000000000A510
  DB1,0x000000000000A510

unnamed table
  DB2,0x000000000000A510
  DB2,0x000000000000A510

unnamed table
  DB3,0x000000000000A510

etc etc...

I'm just using "unnamed table" to distinguish the table data on this forum, the results window does not give the tables any kind of identifier.

Just for an example, one of the databases I will be runnig this on contains ~3000 tables! Only 3 of which I am actually pulling any data out of.

I'm not sure how to go about getting just one timestamp out of that lot.... (maybe this would be another question?)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Ryan McCauleyData and Analytics ManagerCommented:
The error you're getting over the linked server is internal to the query itself, so that just essentially means it won't work in that fashion. If you've got a list of tables and columns you're interested in the MIN value of, can you just query each one, maybe using a cursor to work your way through the list?
0
everardsbreweryAuthor Commented:
I suppose I could query each table for the max timestamp value.

What I'm trying to do currently is:
After a "first run" to get historical data & seed the tables, do this kind of query:

Select [columns I am interested in]
from [table]
where [timestamp] > [previous highest timestamp]
and [timestamp] <= [current highest timestamp]

Open in new window


and use those results to update a remote table.

So what I would like to do is first off fetch, for every database I will be later querying, the current highest timestamp value (which is what I assume min_active_rowversion() will do) and store them.

Then, for each query I run, on all the various databases on all the various servers, I will use that value to limit my results so that I know that I have got all updates done to that table.

The processing job may take a long time to run in total, and updates may be made to the tables after the process has started  - I do not wish to include these updates in the current batch.  I'm after essentially "snapshotting" the database changes.

If I can't use the min_active_rowversion() function cross-sever, I expect I will need to do a select top 1 order by [timestamp] on every table I will later query in each database, and work out the highest value & use that.

That is not a problem currently, as I will only be looking in perhaps 10's of tables.  I was merely thinking that if there is abuilt-in shortcut I can use to get me this value without looking in all the tables then it would make sense to use it.

When you said above
The error you're getting over the linked server is internal to the query itself, so that just essentially means it won't work in that fashion.
Do you know how to fix it so it will work, or is it a non-fixable error?
0
everardsbreweryAuthor Commented:
Well, I have found a partial solution at least:

On each remote database create a sproc like so:
CREATE PROCEDURE [dbo].[usp_TimeStampTest] 
	@ts binary(8) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
        SELECT @ts=min_active_rowversion()
END

Open in new window


and then on the processing server do:
declare @ts binary(8)
exec [linked1].[Database].[dbo].[usp_TimeStampTest] @ts output
select @ts

Open in new window


Which works fine.  My only concern is that it requires the linked server to have RPC_Out=True - I am unsure if this will pose any problems?

This is my second-best option I reckon, I would prefer to use the min_active_rowversion() function, but if it is going to be an issue then...
0
Ryan McCauleyData and Analytics ManagerCommented:
The RPC_Out won't cause any issues - it just requires the server to enroll in a remote transaction, which means the MSDTC (a windows component that handles that) has to be enabled. Since you're able to run it, you're fine to do that on each server. It does require a linked server, which is a bit of a pain, but it's required to do the kind of query you're looking for.

You can look into creating and dropping linked servers programmatically, so you can consume them and then dispose the link when you're done. See these two articles:

http://msdn.microsoft.com/en-us/library/ms190479.aspx (Creating the linked server)
http://msdn.microsoft.com/en-us/library/ms178532.aspx (Modifying the properties, so you can see "RPC_Out" to true)

Also, since you're not passing a table name into the min_active_rowversion() function, it seems to be returning just a single value for the database. This may be what you're intending to fetch, but it doesn't appear to be table-specific. I've never used Timestamp values, so I can't weigh in on the specific usage.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
everardsbreweryAuthor Commented:
Also, since you're not passing a table name into the min_active_rowversion() function, it seems to be returning just a single value for the database. This may be what you're intending to fetch, but it doesn't appear to be table-specific. I've never used Timestamp values, so I can't weigh in on the specific usage.

I looked (read:googled) into T-SQL's timestamp data type, it is Database specific, not Table specific as far as I can make out.  This is borne out in tests as well on two tables in the same DB, each with a timestamp column.

This is fine (actually, preferred) for my purposes, as I'm just using it to restrict what rows are returned.  If it were table specific, I'd need an extra query per Table instead of asking the Database once.

Thanks for the links to the msdn articles, I'll have a read through them.
I'm accepting your answer as the solution (I know it's not /quite/ what I asked for, but this discussion has helped me come up with a workable solution in any case).

Many thanks for your help.
0
everardsbreweryAuthor Commented:
Very helpful & insightful comments, many thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.