Post reply

Linked server

  • February 5, 2007 at 5:17 am

    #98048

    Hi All

    I need to retrieve data from a server(MyServer) and so I created a linked server(MyServer) on each server to get the data from that server.

    When I execute the query to get the data, all the other servers return the data, but only one server doesn't return the results, it just hangs there.it takes the other servers less than a second to get the data, but on this server after 6 mins, its still executing.

    Any idea why this could be happening? Any help would be highly appreciated.

    Thanks

    Anchelin

  • February 5, 2007 at 5:36 am

    #687711

    if you're making use of linked servers you should have some idea of the most basic steps to check a failure!!

    • Can you login to the remote box directly as the user defined in your linked call?
    • Does the login have the correct permissions and database access
    • Have you checked you've set your linked server correctly
    • Is the remote server in mixed mode
    • Have you configured the surface config to allow remote access
    • Is your call being blocked  on the remote server / can you see your connection when monitoring the remote box
    • Are you being blocked by a firewall / policy etc.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 5, 2007 at 5:48 am

    #687715

    Hi

    I'm a newbie in SQl server so the last 3 points doesnt ring a bell. the first 4 i did.

    Help PLEASE!?

     

  • February 5, 2007 at 7:09 am

    #687738

    generally the default options on install of sql 2005 does not allow remote access, you must configure sql 2005 to allow this. Check in BOL under installation.

    the other two are self explanitory I would have thought?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 5, 2007 at 11:40 pm

    #687920

    Is your call being blocked  on the remote server / can you see your connection when monitoring the remote box

     

    Yes this should be checked primarily is your source database geting locked when u fire the query using linked server. This might happen if ur DTC is not properly authenticating to the remote server and this mite end up locking your database in the primary server. You need to check the configuration of the DTC also.

    whats the OS that u r using? is it Windows 200o or 2003?

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • February 6, 2007 at 12:57 am

    #687926

    dtc is usually pretty good at putting up an error message which is why I didn't list it, however, anything is possible!

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 6, 2007 at 3:00 am

    #687956

    I don't usually like posting a link but the msdtc stuff is too lengthy to write down, have a look at this http://blogs.msdn.com/sqlblog/archive/2006/11/15/we-upgraded-our-servers-to-windows-2003-sp1-and-now-our-data-driven-application-fails-with-strange-errors.aspx

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 7, 2007 at 10:04 pm

    #688453

    I too faced the same problem with DTC. In fact my first post in this forum was for the same problem and i have found the same solution as given in the link and have written in the post that i have raised. u can check that also for ur problems.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply