Update Queries on Linked Server

I am trying to query on a linked SQL server from a laptop which has MSDE residing on it.
However when I try to insert, or edit records I get the message that the recordset is not updatable.

Satya, I haven't heard of the 2nd point before. Can't we update records present in a heap?

Please elaborate.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Does MSDE support distributed transactions?

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I am trying to run a transaction in an SP residing on MSDE. The SP makes an insert statement on the remote linked SQL Server.
I get the following error message :

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

please advice me.

I am not not sure how to Make sure that the DTC service is enabled for network access.
Am not able to locate the registry setting you specified.

My OS is Win2K.
I tried the registry thru regedit as well as the setting stated above. No luck.
Any other options ... ?

Copy/paste from a similar question. It might repeat some things already said but I paste it in anyway:
-----------------------
1.
MSDTC has to be running on both the SQL Server and the client machine/server (web/app server, workstation etc.) to be able to work.

2.
If the MSDTC service is running on both machines but still doesn't work then it's most likely due to a name resolution issue or a firewall. MSDTC needs to be able to do name lookups (in both directions) on the servers involved to be able to function.

To check this try and ping the SQL server by name from the web/app server and vice versa ping the web/app server by name from the SQL server.

Use the DTCping.exe utility to verify:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306843

3.
If DNS or WINS are miss-configured or traffic is blocked in routers or something then add name and IP address entries in "lmhosts" and "hosts" files on the involved SQL server and web/app server or fix the DNS or WINS.

4.
If there is a firewall between the servers look at this article on what ports to open:

INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall (Q250367)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q250367

/Argyle

My latptop on which MSDE resides in on DHCP so if I keep it in LMHOST or HOST file, then today it would work.. but tomorrow if IP address changes then it may not work... Is there any place where this information needs to be present apart from HOST file on server?

Also I have a question as will distributed transaction work over VPN?

Let me thank this forum for the solutions it's offering. They have been extremely helpful.
I tried out all the above points... and after changing in the HOSTS file on both machines, they are now accessible by names rather than IP addresses. And the distributions transactions are running fine.

However, as pointed out earlier, my application scenario is such that one machine's IP address will change whenever it gets onto N/W. In that case, how do we make modifications in the HOSTS file on the server ?
It may not be technically advisable to update the HOSTS file on the server

This is the last point of concern for this particular post.
Please help us out.

If you are using DNS for name resolution and you are not using dynamic updates (as per Windows 2000 or later) then you can't successfully use DHCP...

The other option is to use WINS for name resolution (i.e. create a WINS server and set the WINS server on both clients)

Cheers
Twan

Could we get some more information on this topic ?
Maybe a website or something of how to set up a WINS server and how it works ....

Hi ya,

http://www.microsoft.com/ntserver/techresources/commnet/WINS/WINSwp98/WINS02-12.asp is an overview document from MS, there is lots of stuff on the Microsoft site. search for either "WINS" or "Windows Internet Naming Service"


Cheers
Twan

I understand that WINS server config could be a possible solution.
However, what are the implications involved in doing the same after VPN connectivity ?
Keeping in mind that the laptops can connect to the internet by any means eg : cable modem, dial-up etc. and then using VPN, connect to the corporate N/W.
If WINS works over VPN, then it should not b a problem. However, if vica-versa, then we are not sure what ip address the laptop can have and whether that would be added in the WINS server.

Ah sorry oversight... didn't read about the VPN thing... I'd suspect that in that case the VPN server in the corporate office may/should be able to register a device with WINS? The VPN server must authenticate the client somehow...?

Cheers
Twan

hi iam aslo face the same problem ,
please can any one help me
thank you

quote:Originally posted by nilayinc

I am trying to run a transaction in an SP residing on MSDE. The SP makes an insert statement on the remote linked SQL Server.
I get the following error message :

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

please advice me.

Hi,

I got the Same Problem. Have u found the Solution for this. It's Very Urgent Pls Help me.

Regards,

Bala.