Solved

TSQL min_active_rowversion() across linked sever

Posted on 2012-04-12
9
917 Views
Last Modified: 2012-04-17
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.
0
Comment
Question by:everardsbrewery
  • 6
  • 3
9 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
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
 

Author Comment

by:everardsbrewery
Comment Utility
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
 

Author Comment

by:everardsbrewery
Comment Utility
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
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:everardsbrewery
Comment Utility
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
 

Author Comment

by:everardsbrewery
Comment Utility
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
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 100 total points
Comment Utility
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
 

Author Comment

by:everardsbrewery
Comment Utility
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
 

Author Closing Comment

by:everardsbrewery
Comment Utility
Very helpful & insightful comments, many thanks.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now