Setting up distributed transactions between Standard and Express?

Question

Post reply

Setting up distributed transactions between Standard and Express?

lgoodbar

SSC Veteran

Points: 225
More actions
September 22, 2008 at 8:24 am

#212912

If this is the wrong area for the question, please let me know.
We have an application running on some computers under 2005 Express. Due to the 4GB limit in Express, the users want to archive data to a server running 2005 Standard.
I coded a script to copy and delete data from the computer to the server but ran into an error.
Here is a partial script:
begin try
begin distributed transaction
set identity insert [mydatabase].[dbo].[table1] on
insert into [server].[mydatabase].[dbo].[table1]
(list_of_columns)
select list_of_columns
from [computer].[mydatabase].[dbo].[table1]
where conditions
set identity insert [mydatabase].[dbo].[table1] off
delete from [computer].[mydatabase].[dbo].[table1]
where conditions
commit
end try
begin catch
if @@trancount > 0 rollback
select @errmsg = error_message(), @err_severity = error_severity()
raiserror(@errmsg, @err_severity, 1)
end catch
The error messages I received was:
OLE DB provider "SQLNCLI" for linked server "10.121.18.50\SQLEXPRESS" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 50000, Level 16, State 1, Line 255
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "10.121.18.50\SQLEXPRESS" was unable to begin a distributed transaction.
I performed the script testing using test databases on the same server and it worked fine. Changing the server names to the real ones made the script fail.
The computer I'm copying and deleting from is running 2005 Express. Is that an issue?
Is starting distributed transactions as simple as enabling the Distributed Transaction Coordinator on the client computer?
Is replication an option? Replicating the computer database to the server, and let the server do an updating replication? Logically it would be on the "same server". Or are there issues with Express here as well?
TIA,
Loyd

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jason Crider SSCertifiable Points: 6019 More actions · Answer 1

1st question:

are you going from x64 to x86 at any point?

2nd question:

Have you verified that the linked servers are configured properly and working?

3rd question:

Go to the server and do Start->Programs->Administrative tools->Component Services. Right click on My Computer and select properties. Under the MSDTC tab click on Security Configuration. Is the Network DTC Access box checked? What about the Transaction Manager Communication?

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

lgoodbar SSC Veteran Points: 225 More actions · Answer 2

are you going from x64 to x86 at any point?

No, all are x86-32bit.

Have you verified that the linked servers are configured properly and working?

As far as I can tell, yes. Have a linked server to "10.121.18.50\SQLEXPRESS". Can expand to Catalogs and see correct databases inside. From the server, can run a query such as "select * from [10.121.18.50\SQLEXPRESS].[6f_module].[dbo].[tblvbssweepdata]" and it returns results.

Go to the server and do Start->Programs->Administrative tools->Component Services. Right click on My Computer and select properties. Under the MSDTC tab click on Security Configuration. Is the Network DTC Access box checked? What about the Transaction Manager Communication?

On the server itself - nothing checked under Security Configuration. Should it be, as the transaction originator? The error messages indicated the remote (clients) had the error. I can change this if needed.

On the desktops - these are desktop (client) machines running SQL Server Express. Will need to look at them directly (no remote screen access) and post back.

--Loyd

Jason Crider SSCertifiable Points: 6019 More actions · Answer 3

Regarding #3 then.

I have had a similar experience where it fixed it:

Look here: http://support.microsoft.com/kb/839279

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

lgoodbar SSC Veteran Points: 225 More actions · Answer 4

Look here: http://support.microsoft.com/kb/839279%5B/quote%5D

Thanks, will review this as well.

lgoodbar SSC Veteran Points: 225 More actions · Answer 5

I must be running into a performance issue here.

I set up the DTC on two servers: the original running 2005 standard, acting as the "server". Installed 2005 express on another server to act as the "desktop". Backed up and restored the relevant database to the "desktop" server.

To test the transaction, I'm pulling the minimum amount of data: 1 (one) row from a table, and 202 rows from another. They are linked together by "rawsweepid".

The first table is tblvbssweepdata, with one row to copy, has a length of 823 bytes. The other table with 202 rows is tbl_vbsrawsweep, has a length of 184. Based on adding together the lengths from syscolumns.

use server_1905_test

select sum(length) from syscolumns where id=(select id from sysobjects where name='tbl_vbsrawsweep')

For tblvbssweepdata, the data size is 823 bytes (plus overhead, etc.)

For tbl_vbsrawsweep, the data size is (184*202) = 37,168 bytes.

As I type, the query has run for 15 minutes. Both servers are Windows 2003 server, connected over gigabit ethernet. According to task manager, the network connections are using 4-6% of a 1 Gbps link. It's been a steady 4-6% for the whole 15+ minutes.

I wonder if the query is really running, or if DTC is hung on a process? I can cancel the query just fine. It's just perplexing to me that such a small amount of data requires so much overhead to process.

Here is the script in its entireity. I have changed it to pull only 1 rawsweepid to its temp table for processing. Each 1 row from tblvbssweepdata is linked to 201 or 202 rows in tbl_vbsrawsweep.

/*

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

*/

/* 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.

*/

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.

*/

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

*/

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

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

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

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

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.

*/

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

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

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],