SQL Server Performance

Update Queries on Linked Server

Discussion in 'General Developer Questions' started by nilayinc, Sep 3, 2003.

  1. nilayinc New Member

    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.
  2. satya Moderator

    Check two things,
    1. The user specified has necessary read and write permissions.
    2. That the table you are trying to edit has a primary key.

    The table you're trying to update must have a primary key or unique constraint defined on it to be updateable.



    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. gaurav_bindlish New Member

    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
  4. satya Moderator

    Gaurav

    Yes its updateable, I mean to say check whether any duplicate is involved.
    I have seen this error occurs when trying to set duplicate rows on LS.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. gaurav_bindlish New Member

    Does MSDE support distributed transactions?

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

    As far as distributed transactions are concerned, I believe MSDE supports them.
    But I have not had to implement a system that uses distributed transactions in conjunction with MSDE.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. satya Moderator

    To confirm it again I chunk thru my library and it (KBA) confirms that includes transaction-logging, merge replication support, distributed transactions (so you can easily process transactions between a local database and a central database when replication is not sufficient), stored procedures, triggers, and automatic error recovery (via the transaction capability) etc.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. nilayinc New Member

    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.
  9. satya Moderator

    Nilayinc

    Make sure that the DTC service is enabled for network access. Depending on the OS version and the OS installation the DTC could be running but without network access.

    Also check the registry [HKLMSoftwareMicrosoftmsdtcSecurityNetworkDTCAccess ] key settings.

    It seems you've posted multiple (duplicate) threads, in your earlier posthttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1559 and check for above reigstry key also
    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  10. nilayinc New Member

    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.
  11. satya Moderator

    To enable or check Network DTC access go thru:
    Control panel--> Add/Remove Windows components ---> Application Server ---> Enable Network DTC Access - Check the box and complete.

    So have you checked the registry for the above key using regedt32 utility?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  12. nilayinc New Member

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

    Have you rebooted the box after registry settings?
    THat should take affect the changes.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  14. satya Moderator

    One more thought :

    This error is often seen when you have any firewall enabled between the 2 boxes.
    And Distributed transactions need NetBIOS name resolution between machines to work properly. You can enter this manually WINS or LMHOSTS entries.

    And see set xact_abort on is defined in the set of statements.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  15. Argyle New Member

    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
  16. satya Moderator

    I would suggest to test using DTCPing as referred by Argyle, which may result some information.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  17. nilayinc New Member

    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?
  18. satya Moderator

    Yes it will work over VPN, as long as you enabled DTC network access.
    Have you followed thru all the steps above.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  19. nilayinc New Member

    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.
  20. Twan New Member

    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
  21. nilayinc New Member

    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 ....
  22. Twan New Member

  23. nilayinc New Member

    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.
  24. Twan New Member


    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
  25. karri New Member

    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.
  26. satya Moderator

    So do you think none of the above suggestions didn't helped to resolve the issue?

    Check MSDTC is started, no firewall is involved and others etc.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  27. Balashanmugam New Member

    Hi,

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

    Regards,

    Bala.
  28. satya Moderator

    Bala
    Have you followed whatever has been mentioned in this thread?
    Also search the forums for relevant information, this has been discussed many times here before.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page