Prev: database integrity checks failing
Next: Paging Size set up for sql 2005 best performance
From: RG on 14 Jul 2010 10:40
A stored procedure running from server A database db1 updates both local
tables, as well as, remote ones, on server B database db2 via a db link. All
updates are part of the same distributed transaction.

I have got it to work on one server. In other words, I have made all
provisions to make this work like turn off rpc security, configure msdtc in
windows components.

However, when I changed the remote server to be server C database db2...
The two inserts done on the local server succeeded. But delete comman
against remote table never comes back. No errors or locks.

What could be the cause?

Thanks in advance
From: Erland Sommarskog on 14 Jul 2010 18:13
RG (RG(a)discussions.microsoft.com) writes:
> A stored procedure running from server A database db1 updates both local
> tables, as well as, remote ones, on server B database db2 via a db link.
> All updates are part of the same distributed transaction.
>
> I have got it to work on one server. In other words, I have made all
> provisions to make this work like turn off rpc security, configure msdtc
> in windows components.
>
> However, when I changed the remote server to be server C database db2...
> The two inserts done on the local server succeeded. But delete comman
> against remote table never comes back. No errors or locks.
>
> What could be the cause?

Linked server is a good thing to shorten your life with. And then add
distributed transactions on top of it...

I'm afraid that I don't know the answer, because MS DTC remains a
mystery to me.

But a few questions:

o Are all servers, A, B and C part of the same domain?
o All run SQL Server? All run the same version of SQL Server?
o A, B and C are three different machines?`That is, no named instances?
o Do A, B and C all run the same version of Windows?
o Do all three servers have the same service account?
o Are MSDTC configured equally on all three boxes?

I'm not saying that all these matters, but they are things to look for.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: RG on 14 Jul 2010 20:34
I suppose you want to know if server B is different than C. Both of them
have the same os, sql server version and patch level.

These servers are in a workgroup. But I have made appropriate provisions
for this by disabling msdtc authentication and turned off rpc authentication.

I am successfully running this setup between server A and server B.
However, I am facing the problem between server A and server C.

Now that I think about it, the only thing that I didn't do to server C that
I did to server B is reboot it. Hower, I did restart msdtc and sql services.
Could it be that it wasn't enough?

Thanks again for your help

"Erland Sommarskog" wrote:

> RG (RG(a)discussions.microsoft.com) writes:
> > A stored procedure running from server A database db1 updates both local
> > tables, as well as, remote ones, on server B database db2 via a db link.
> > All updates are part of the same distributed transaction.
> >
> > I have got it to work on one server. In other words, I have made all
> > provisions to make this work like turn off rpc security, configure msdtc
> > in windows components.
> >
> > However, when I changed the remote server to be server C database db2...
> > The two inserts done on the local server succeeded. But delete comman
> > against remote table never comes back. No errors or locks.
> >
> > What could be the cause?
>
> Linked server is a good thing to shorten your life with. And then add
> distributed transactions on top of it...
>
> I'm afraid that I don't know the answer, because MS DTC remains a
> mystery to me.
>
> But a few questions:
>
> o Are all servers, A, B and C part of the same domain?
> o All run SQL Server? All run the same version of SQL Server?
> o A, B and C are three different machines?`That is, no named instances?
> o Do A, B and C all run the same version of Windows?
> o Do all three servers have the same service account?
> o Are MSDTC configured equally on all three boxes?
>
> I'm not saying that all these matters, but they are things to look for.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Erland Sommarskog on 15 Jul 2010 04:29
RG (RG(a)discussions.microsoft.com) writes:
> I suppose you want to know if server B is different than C. Both of them
> have the same os, sql server version and patch level.
>
> These servers are in a workgroup. But I have made appropriate
> provisions for this by disabling msdtc authentication and turned off rpc
> authentication.
>
> I am successfully running this setup between server A and server B.
> However, I am facing the problem between server A and server C.
>
> Now that I think about it, the only thing that I didn't do to server C
> that I did to server B is reboot it. Hower, I did restart msdtc and sql
> services.
> Could it be that it wasn't enough?

I don't know. But so much I can say is that I have been entirely
unsuccessful to get MSDTC to work in my workgroup at home. And it is
not the only problem I have. Windows authentication does not always
work, and in some situations I am not able to mount network disks.

In short: in workgroups things are much more willy-nilly than in a domain.

You could use gpedit.msc to see if there are any differences in security
settings beween B and C. Or bite the bullet and build a domain...


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: RG on 15 Jul 2010 11:01
It appears that as the server C problem is due to locking as the db in
production and active transacitons are coming in. To test, I created the
copy of prod db on the same server (C). This worked.

"Erland Sommarskog" wrote:

> RG (RG(a)discussions.microsoft.com) writes:
> > I suppose you want to know if server B is different than C. Both of them
> > have the same os, sql server version and patch level.
> >
> > These servers are in a workgroup. But I have made appropriate
> > provisions for this by disabling msdtc authentication and turned off rpc
> > authentication.
> >
> > I am successfully running this setup between server A and server B.
> > However, I am facing the problem between server A and server C.
> >
> > Now that I think about it, the only thing that I didn't do to server C
> > that I did to server B is reboot it. Hower, I did restart msdtc and sql
> > services.
> > Could it be that it wasn't enough?
>
> I don't know. But so much I can say is that I have been entirely
> unsuccessful to get MSDTC to work in my workgroup at home. And it is
> not the only problem I have. Windows authentication does not always
> work, and in some situations I am not able to mount network disks.
>
> In short: in workgroups things are much more willy-nilly than in a domain.
>
> You could use gpedit.msc to see if there are any differences in security
> settings beween B and C. Or bite the bullet and build a domain...
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: database integrity checks failing
Next: Paging Size set up for sql 2005 best performance