Solved

TSQL min_active_rowversion() across linked sever

Posted on 2012-04-12
9
948 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37841270
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
ID: 37841771
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
ID: 37841811
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37845753
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
 

Author Comment

by:everardsbrewery
ID: 37850523
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
ID: 37850660
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
ID: 37852544
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
ID: 37856265
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
ID: 37856272
Very helpful & insightful comments, many thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms sql + get number in list out of total 7 45
sql how to count case when 4 29
Substring works but need to tweak it 14 31
point in time restore in SQL server 26 44
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …

749 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