Linked Server Problem

Folks i've two SQL SERVER, SERVER1 and SERVER2. I am updating a table at SERVER1 by joining a table with SERVER2. The query works fine, but update fails.

UPDATE a
set col1=b.col2
FROM mytable a JOIN SERVER2.mydb.dbo.mytable b ON a.id=b.id
WHERE a.date=getdate()

I get the following error:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Plz advise!You need to start the "Distributed Transaction Coordinator" service on both servers.

Roby2222|||It's already running on both of the servers.|||Check whether any firewall rule is obstructing the DTC access.

To work around this behavior, install network DTC access on both servers:

Click Start, and then click Control Panel.
Click Add or Remove Programs, and then click Add/Remove Windows Components.
In the Components box, click Application Server, and then click Details.
Click to select the Enable network DTC access check box, and then click OK.
Click Next, and then follow the instructions that appear on the screen to complete the installation process.
Stop and then restart the Distributed Transaction Coordinator service.
Stop and then restart any resource manager services that participates in the distributed transaction (such as Microsoft SQL Server or Microsoft Message Queue Server).|||Thanx for the guidance, Satya.

I would let ya know after i restart the machines(on production).

Howdy!

Linked server and replication

Hello,

I have one server using SQL Server Express (Server1\SqlExpress - Sql Authentication) and another one using Sql Server 2005 (Server2 - Windows Authentication)

My Server2 is the publisher for a replication and Server1\SqlExpress is the client for this replication.

For any reason that has been discussed in details with Raymond Mak in some other threads, some triggers and stored procedures cannot be replicated via the replication, so I use a post replication script.

To avoid having to maintaint coherent my source control, my master DB and the post replication script I wanted to create a script that will extract from the master DB the source code of the problematic triggers and stored procedures.

To do that, here the kind of script I use (example for the stored procedure):

DECLARE @.query NVARCHAR(max)

select @.query = routine_definition from openquery(MASTER,

'

SELECT routine_definition FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_name = N''spU_GUI_AppliquePerteAgrement''

')

EXEC (@.query)

So my script will be run on the subscription client and will get the script to the master db using a linked server. So, here is the step I have followed :

Create a domain user (let's call it localAdmin)

Set localAdmin as a local administrator for Server1 and Server2

Update the properties of my subscription to impersonate localAdmin on the publisher and on the subscriber

Create a linked server on Server1, linked to Server2. (for security, I have tried either "Be made without using a security context" and "Be made using the current security context")

Then I am able to run my script on Server1\SqlExpress but when I launch the replication, here is the error I receive : "Login failed for Autorite NT \ Anonymous Login"

Do you have any idea of what can happen ?

Thanks,

Pierre-Emmanuel

When you configure to use "Be made using the current security context", SQL Server uses windows NT kerberos delegation to authenticate client to the linked server. If the delegation configuration is incorrect, you will get error ""Login failed for Autorite NT \ Anonymous Login".

You can check you configuration against the recommendations,

(1) http://msdn2.microsoft.com/en-us/library/ms189580.aspx

(2) SQL Server setion in http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

If using client account to authenticate to the linked server is not a requirement, you can configure to use SQL login for the linked server configuration. "Be made using this security context".