Hfrhyu

How much damage would a cupful of neutron star matter do to the Earth?

Delete free apps from library

How can I prevent/balance waiting and turtling as a response to cooldown mechanics

Monty Hall Problem-Probability Paradox

Why complex landing gears are used instead of simple,reliability and light weight muscle wire or shape memory alloys?

Are the endpoints of the domain of a function counted as critical points?

Why is a lens darker than other ones when applying the same settings?

Flight departed from the gate 5 min before scheduled departure time. Refund options

Tips to organize LaTeX presentations for a semester

What is the chair depicted in Cesare Maccari's 1889 painting "Cicerone denuncia Catilina"?

Can an iPhone 7 be made to function as a NFC Tag?

Is there hard evidence that the grant peer review system performs significantly better than random?

Is it dangerous to install hacking tools on my private linux machine?

What initially awakened the Balrog?

Putting class ranking in CV, but against dept guidelines

A term for a woman complaining about things/begging in a cute/childish way

Why are vacuum tubes still used in amateur radios?

Differences to CCompactSize and CVarInt

How to write capital alpha?

Google .dev domain strangely redirects to https

Is CEO the "profession" with the most psychopaths?

"klopfte jemand" or "jemand klopfte"?

Can two person see the same photon?

The test team as an enemy of development? And how can this be avoided?

Using `min_active_rowversion` for global temporary tables

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

7

I use global temporary tables for easy integration testing of my SQL Server backed application.

But the function min_active_rowversion does not seem to include global temporary tables, like seen here:

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))

 connection.Open();

 connection.Execute("create table ##mytable ( Id int, rv rowversion )");

 var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

 var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

 var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

I can't see this limitation documented anywhere. Can this be made to work? And if so, how?

sql-server sql-server-2016 temporary-tables

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

add a comment | 

7

I use global temporary tables for easy integration testing of my SQL Server backed application.

But the function min_active_rowversion does not seem to include global temporary tables, like seen here:

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))

 connection.Open();

 connection.Execute("create table ##mytable ( Id int, rv rowversion )");

 var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

 var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

 var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

I can't see this limitation documented anywhere. Can this be made to work? And if so, how?

sql-server sql-server-2016 temporary-tables

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

add a comment | 

I use global temporary tables for easy integration testing of my SQL Server backed application.

But the function min_active_rowversion does not seem to include global temporary tables, like seen here:

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))

 connection.Open();

 connection.Execute("create table ##mytable ( Id int, rv rowversion )");

 var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

 var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

 var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

I can't see this limitation documented anywhere. Can this be made to work? And if so, how?

sql-server sql-server-2016 temporary-tables

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))

 connection.Open();

 connection.Execute("create table ##mytable ( Id int, rv rowversion )");

 var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

 var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

 var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

1 Answer
1

active

oldest

votes

9

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234637%2fusing-min-active-rowversion-for-global-temporary-tables%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

9

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

add a comment | 

9

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

add a comment | 

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826