Setting up distributed transactions between Standard and Express?

Question

Setting up distributed transactions between Standard and Express?

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply

lgoodbar SSC Veteran Points: 225 More actions · Answer 1

It appears to have been a query issue. My working theory (so far proved correct) is that the in-memory tables @dtrs and @rawsweep were held only on the originating server. The linked server had to pass back every row to the originating server for criteria selection.

I created permanent tables archivedtrs and archiverawsweep, in which I populated from memory tables @dtrs and @rawsweep. Then the insert and delete queries work locally against the archive* tables. For the 1 row / 202 rows scenario it ran in 33 seconds.

Revised query:

/*

ONE TIME RUN

Migrate audit stand 1905 tables tbl_VBSRawSweep and

tblVBSSweepData from local stand to server.

Purge copied rows from stand.

Because the stand RawSweepID was reset to 1, and

RawSweepID is an identity column in tblVBSSweepData,

increment the sweep IDs by the maximum current sweep

ID. Correct data so sweep IDs are sequential with no

gaps.

This procedure will run on etswvsvsql003, and accesses

database [6f_module] on the local stand. The

stand has already been added to SQL Server's linked

server list.

etswvsvexttst\SQLEXPRESS1905

*/

/*

3 Sep 2008Loyd Goodbar

Initial programming.

Tested against restored copies of databases

stand_1905_test and server_1905_test

4 Sep 2008Loyd Goodbar

Updated script for new columns added per Ken

Poulard:

1905:

tbl_vbsrawsweep

DateTimedatetime

SSNnvarchar(50)

6 Sep 2008Loyd Goodbar

Change procedure to key off rawsweepid

from tblvbssweepdata. During extended

testing, discovered that the rawseepid

can become "off by 1" if updated

independently. Only use the tblvbssweepdata's

rawsweepid.

10 Sep 2008Loyd Goodbar

Cleanup and additional testing. Added more

conditions to copy and delete operations.

*/

print '----- Script start: ' + convert(char,getdate(),121)

/* Error variables for catch() block. */

declare @errmsg nvarchar(4000), @err_severity int

/*

In production, set cutoff date to leave 3-5

minutes of data in table. Hopefully this will not

copy a partial test sweep. Observed step numbers

for a test run to 201.

*/

print '----- Getting cutoff date/time: ' + convert(char,getdate(),121)

declare @cutoffdatetime as datetime

set @cutoffdatetime = dateadd(mi,-5,getdate())

select convert(char,@cutoffdatetime,21)

/*

Track maximum sweep ID from tables to convert incoming

sweep ID from 1905 to (maximum sweep ID + 1) on SQL003.

*/

print '----- Getting last data sweep raw id: ' + convert(char,getdate(),121)

declare @lastdatasweepid int

select @lastdatasweepid = max(rawsweepid)

from [etswvsvsql003].[server_1905_test].[dbo].[tblvbssweepdata]

select @lastdatasweepid as [last data sweep id] --, @lastrawsweepid as [last raw sweep id] -- verification

/*

Table tblVBSSweepData contains a date time and sweep ID.

Generate table with date time values in order to provide

clean cutoff. Generate table of raw sweep ID, test ID,

solenoid ID, and step number based on first table, to

narrow selection in tbl_VBSRawSweep.

Gather keys for data to be archived and purged.

Observed keys:

tblVBSSweepData

DateTime

RawSweepID

tbl_VBSRawSweep

RawSweepID

TestID

SolID

StepNumber

*/

print '----- Populate virtual table @dtrs: ' + convert(char,getdate(),121)

declare @dtrs table (dt datetime, rs int, tst int, sol int, crs2 int null)

insert into @dtrs (dt, rs, tst, sol)

select top 1 datetime, rawsweepid, testid, solid

from [etswvsvexttst\SQLEXPRESS].[6f_module].[dbo].[tblvbssweepdata]

where datetime < @cutoffdatetime

print '----- Populate virtual table @rawsweep: ' + convert(char,getdate(),121)

declare @rawsweep table

(rs int, tst int, sol int, step int, crs2 int null)

insert into @rawsweep (rs, tst, sol, step)

select rawsweepid, testid, solid, stepnumber

from [etswvsvexttst\SQLEXPRESS].[6f_module].[dbo].[tbl_vbsrawsweep] a

join @dtrs b on (a.rawsweepid = b.rs)

/*

We need to convert the incoming sweep IDs to follow the

existing IDs in SQL003 tables. Logic to convert the ID

without leaving gaps:

"maximum sweep ID from SQL003" - "minimum sweep ID from 1905" +

"current sweep ID value" + 1

Ex.

"maximum sweep ID from SQL003"106328-

"minimum sweep ID from 1905"78+

"current sweep ID value"78+

1=106329

Ex.

"maximum sweep ID from SQL003"106328-

"minimum sweep ID from 1905"78+

"current sweep ID value"350+

1=106601

*/

declare @mindtrssweep int

select @mindtrssweep = min(rs) from @dtrs

print '----- Update virtual tables with corrected raw sweep value: ' + convert(char,getdate(),121)

update @dtrs set crs2 = @lastdatasweepid - @mindtrssweep + rs + 1

update @rawsweep set crs2 = @lastdatasweepid - @mindtrssweep + rs + 1

print '----- Show what will be copied: ' + convert(char,getdate(),121)

select * from @dtrs order by 1,2 -- verification

select * from @rawsweep order by 1,2,3,4 -- verification

/*

Now variable tables @dtrs and @rawsweep contain

the data required for row copy and purge.

*/

print '----- Starting transaction: ' + convert(char,getdate(),121)

set xact_abort on -- Required by OLE DB to ignore "nested transaction" errors. See http://msdn.microsoft.com/en-us/library/ms188792.aspx

begin try

begin distributed transaction

/*

Table requirements on linked server:

archivedtrs, archiverawsweep

*/

print char(13) + '----- Purge 6f_module temp table archivedtrs: ' + convert(char,getdate(),121)

delete from [etswvsvexttst\sqlexpress].[6f_module].[dbo].[archivedtrs]

print char(13) + '----- Send @dtrs to linked server, archivedtrs: ' + convert(char,getdate(),121)

insert into [etswvsvexttst\sqlexpress].[6f_module].[dbo].[archivedtrs]

(dt, rs, tst, sol, crs2)

select dt, rs, tst, sol, crs2 from @dtrs

print char(13) + '----- Purge 6f_module temp table archiverawsweep: ' + convert(char,getdate(),121)

delete from [etswvsvexttst\sqlexpress].[6f_module].[dbo].[archiverawsweep]

print char(13) + '----- Send @dtrs to linked server, archiverawsweep: ' + convert(char,getdate(),121)

insert into [etswvsvexttst\sqlexpress].[6f_module].[dbo].[archiverawsweep]

(rs, tst, sol, step, crs2)

select rs, tst, sol, step, crs2 from @rawsweep

/* - - - - - - - - - - */

print char(13) + '----- Insert from 6f_module to server_1905_test, tblvbssweepdata: ' + convert(char,getdate(),121)

set identity_insert [server_1905_test].[dbo].[tblvbssweepdata] on

insert into [etswvsvsql003].[server_1905_test].[dbo].[tblvbssweepdata]

(TestID, SolID, RawSweepID, ModuleSN, SSN, Passed, [DateTime],

[Type], [Description], Operator, Comments, SolenoidType,

SweepTable, Kp, Ki, AdlerPeriod, StepsinWave,

SwitchingFrequency, DitherFrequency, PurgeTime, SweepTime,

FluidTemperature, AverageSupply, SupplyMinimum, SupplyMinimumat,

SupplyMaximum, SupplyMaximumat, SupplyRange, Flowat0, Flowat850,

MaximumFlow, MaximumFlowat, MaximumRippleinc, MaximumRippleincat,

MaximumRippledec, MaximumRippledecat, MaximumGain, MaximumGainat,

MaximumHysteresis, MaximumHysteresisat, RepeatAvgHysterisis,

GatePoint1lowcurrent, GatePoint1lowpressure, GatePoint1highcurrent,

GatePoint1highpressure, GatePoint2lowcurrent, GatePoint2lowpressure,

GatePoint2highcurrent, GatePoint2highpressure, MinimumPressure,

RawDataFileName, Step1Pressureinc, Step2Pressureinc, Step3Pressureinc,

Step4Pressureinc, Step5Pressureinc, Step6Pressureinc, Step7Pressureinc,

Step8Pressureinc, Step9Pressureinc, Step10Pressureinc,

Step1PressureAverage, Step2PressureAverage, Step3PressureAverage,

Step4PressureAverage, Step5PressureAverage, Step6PressureAverage,

Step7PressureAverage, Step8PressureAverage, Step9PressureAverage,

Step10PressureAverage, RecheckPressure1, RecheckPressure2,

RecheckPressure3, RecheckPressure4, RecheckPressure5,

RecheckPressure6, RecheckPressure7, RecheckPressure8,

RecheckPressure9, RecheckPressure10, RecheckAvePressure1,

RecheckAvePressure2, RecheckAvePressure3, RecheckAvePressure4,

RecheckAvePressure5, RecheckAvePressure6, RecheckAvePressure7,

RecheckAvePressure8, RecheckAvePressure9, RecheckAvePressure10,

[Gross Leakage], NHCurrentAt475, NHCurrentAt03, NLCurrentAt460,

NLCurrentAt03, FlowAt485, CurrentAt485)

select

TestID, SolID, crs2 /*RawSweepID*/, ModuleSN, SSN, Passed, [DateTime],