473,215 Members | 1,262 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,215 software developers and data experts.

cross server update problem

The system has worked for two years.

SQL Server 2000 runs on Windows 2000 Server "A".

Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.

The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".

After the move from Server "B" old to Server "B" new, suddenly
the cross server updates fail, but only when they are conducted within
a Begin and Commit Transaction batch.

The system works when I remove the Begin and Commit Transaction, and no
longer have them run in a batch.

Also, I had a cross server update, having "A" update records in
"B", from a delete trigger residing on "A" table. Deletes
against the "A" table fail so long as the trigger remains. Once I
remove the delete trigger from the "A" table, I can now delete
records on the "A" table.

Thanks. Dotnet Fellow

Sep 11 '06 #1
9 2451
(do**********@yahoo.com) writes:
The system has worked for two years.

SQL Server 2000 runs on Windows 2000 Server "A".

Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.

The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".

After the move from Server "B" old to Server "B" new, suddenly
the cross server updates fail, but only when they are conducted within
a Begin and Commit Transaction batch.

The system works when I remove the Begin and Commit Transaction, and no
longer have them run in a batch.
Any error message you could share with us?

Since it works without a transaction, it sounds as if DTC is not running
on the new server B.
Also, I had a cross server update, having "A" update records in
"B", from a delete trigger residing on "A" table. Deletes
against the "A" table fail so long as the trigger remains. Once I
remove the delete trigger from the "A" table, I can now delete
records on the "A" table.
A trigger always operates in a transaction defined by the statement that
fired it, so this is consistent with the above.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 11 '06 #2
DTC is running on both computers.

Sep 11 '06 #3
There is no error message returned. Just hangs for hours until a
timeout is encountered. Looking at it through Profiler Trace it
appears that no activity is occurring on either server once the
transaction is begun. Or, in the case of the delete triggers, it
appears that everything comes to a complete standstill once the delete
statement is executed.

Sep 11 '06 #4
(do**********@yahoo.com) writes:
There is no error message returned. Just hangs for hours until a
timeout is encountered. Looking at it through Profiler Trace it
appears that no activity is occurring on either server once the
transaction is begun. Or, in the case of the delete triggers, it
appears that everything comes to a complete standstill once the delete
statement is executed.
Let me guess: the source server runs Windows 2000, and the target server
runs Windows 2003? Where as the server you replaced ran Windows 2000?

No, I don't know anything. But I think I've seen this scenario at work. As
that has only been with development and test servers I have not dug into it.

But chance had it that one of our customer ran into precisely this
problem today. They have a brand-new server (which I assume runs Win 2003),
and in one place our gateway database that runs on another server (which
I suspect runs Win 2000) tries to use INSERT-EXEC to the linked server.
Worked find Friday. Today it just hung. SQL 2000 on both machines.

We will probably open a case with Microsoft on this. I suspect it's one
of these RTFM things, but I just don't know which manual. My gut feeling
is that this is a DTC problem. Maybe as simple, as opening the correct
port in Windows firewall. Whatever, I believe it's more a Windows problem
than an SQL Server problem per se.

If we open a case, and I hear about the resolution, I'll try to post
back.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 11 '06 #5
All servers involved, old and new, have a default collation of
SQL_Latin1_General_CP1_CI_AS.

My Cross Server update that I execute on Server "A" is below, where

@p_str_source_workspace VARCHAR(50) = 'vit16.ais70.dbo.', and
@p_str_destination_workspace VARCHAR(50) = 'vit2.opus.dbo.'

Again, it doesn't work when I uncomment the BEGIN TRANSACTION

---------

/*
Update the source database, ensure that the archived flag is accurately
marked as 'N' if the record does not yet exist in Opus.
*/

SELECT @l_str_update = ' UPDATE ' + @p_str_source_workspace +
'pr_history SET archived = ''N'' FROM ' + @p_str_source_workspace +
'pr_history as pr0 WHERE archived = ''Y'' AND pr0.pr_hist_search NOT IN
(SELECT pr_hist_search FROM ' + @p_str_destination_workspace +
'pr_history)'

--BEGIN TRANSACTION

EXEC (@l_str_update)

---------

Thanks for keeping me in mind Erland.

Sep 12 '06 #6
(do**********@yahoo.com) writes:
All servers involved, old and new, have a default collation of
SQL_Latin1_General_CP1_CI_AS.
I would not expect collations to have anything to do with it. In fact, as I
said, my feeling is that it is not really an SQL Server issue, but a Windows
issue.

You did not confirm my guesses about the operating system. By the way,
when you replaced B, did you give the same IP address to the new B
server? There is another potential source for problem, some reference
to the old machine lingering around.

I spoke to my colleague who has been in touch with Microsoft. Apparently
they did not just say "did you check that...", but instead they asked tim
to send logs and similar information.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 13 '06 #7
Here is an update:

In our case, it turned out that the machine from which we made the
connection is running SQL 2000 SP2 and Windows 2000 SP2. The Microsoft
engineer suggested that the SQL Server should be upgraded to SQL 2000 SP4
and Windows 2000 SP4. Whether this alone will resolve the issue, I don't
know yet. The simple reason the Support Professional wanted us to upgrade
is that they don't support neither SQL 2000 SP2 nor Win 2000 SP2.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 13 '06 #8
It seems that we now have a resolution. As I suspected it's a DTC issue.
Windows 2003 adds improved security which gets in the way.

On the server you are connecting to, open Component Services and select
properties for My Computer. Go to the MS DTC tab. To the lower left, there
is a button Security Configuration. This opens a new dialogue. In this
dialogue, enable 1) Network DTC Access. 2) Allow Inbound. 3) No
Authentication Required.

Really what the implication of these settings are from the security side
point of view, I don't know.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 14 '06 #9
This is some advice that we got from the support professional on our case:
Windows 2003 R2 has new DTC protection measures built-in and enabled
by default, since MSDTC is used for SQL Server purposes and this
machines don't have internet access there are no problem in set no
authentication required. In future if you upgrade your Windows 2000
machine to Windows 2003 SP1 or Windows 2003 R2 and they are in same
domain (this is a requirement) you can activate mutual authentication
in MSDTC and will not have any problem.
These steps are not documented but I let you some recommendations to
troubleshooting MSDTC problems.
1) Put security setting at the minimum level

2) Enable MSDTC logging and verify if anything appears on log file

3) Use DTCPing utility to avoid firewall problems

4) Verify if RPC is started before MSDTC and SQL Server is started
after MSDTC and RPC services.

5) Verify in SQL Server error log that MSDTC started with success

6) Please verify if you have COM/COM+ components enabled and with all
hot fixes installed

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 14 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Angus SC2 | last post by:
Hi I am trying to set up cross domain cookies on a server I have running that currently has 2 domains running on it (on different IPs). Basically I have a forums site (using UBB threads) and I...
8
by: Pieter | last post by:
Hi, I'm having some weird problem using the BackGroundWorker in an Outlook (2003) Add-In, with VB.NET 2005: I'm using the BackGroundWorker to get the info of some mailitems, and after each item...
3
by: Pieter Coucke | last post by:
Hi, In my VB.NET 2005 application I'm generating and sending emails using the outlook-object model (2003). When a mail is Send (MailObject_Send), I raise an event in a global class, that is...
11
by: chettiar | last post by:
I have a SQL Server code which goes like this: UPDATE ms SET c1 = ms.c1 + ur.c1 FROM test ms CROSS JOIN test ur WHERE ms.c2 = 'a' AND ur.c2 = 'b'
10
by: Daniel | last post by:
Hi guys I have a form with my gui on, just some list boxes. And a class that handles incoming data. i want to, on receiving data, update my gui. However even though i have an instance...
15
by: dani kotlar | last post by:
I am trying to make a client-server application to run, but I am running into this problem: in the server Form the call InitializeComponent(); creates a text-box. Later the calls: readThread =...
16
by: Stevo | last post by:
I'm guessing this is a laughably obvious answer to many here, but it's not to me (and I don't have a server or any knowledge of PHP to be able to try it). It's not strictly a PHP question, but...
3
by: jonceramic | last post by:
Hi All, I need to know the best way to set up a datawarehouse/materialized view for doing statistics/graphs in Access. My crosstabs and unions are getting too complicated to crunch in real...
6
by: ampo | last post by:
Hello. Can anyone help with cross-domain problem? I have HTML page from server1 that send xmlHTTPRequest to server2. How can I do it? Thanks.
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.