Post reply

Linked server stored proc never returns

  • July 22, 2005 at 3:16 pm

    #69487

    I am really puzzled by this one.  I have two servers: Server1 and Server2.  Server2 is defined as a linked server on Server1.   Furthermore, on Server2 I have a database (MyDB) with a table I am interested in (MyTable).  The table is fairly large, but manageable -- 3 mln. records/150 MB.  Now watch this:

    1.  When I run this query on Server2 (locally) it ruturns in about 2 min:

    SELECT * INTO #temp FROM MyDB.dbo.MyTable

    2.  When I run this query on Server1 (using linked server), it returns in 3 min:

    SELECT * INTO #temp FROM Server2.MyDB.dbo.MyTable

    3.  Same result if I do INSERT/SELECT instead of SELECT INTO:

    INSERT #temp SELECT * FROM Server2.MyDB.dbo.MyTable

    Here comes the kicker.  If I wrap SELECT statement inside the stored procedure on Server2 like this:

    CREATE PROC MyProc AS SELECT * FROM MyDB.dbo.MyTable

    and then run this statement:

    4.  INSERT #temp EXEC Server2.MyDB.dbo.MyProc

    It will be running for hours and never comes back! 

    Any ideas?

    Thank you!

  • July 24, 2005 at 2:18 am

    #576620

    Hi,

    Try putting SET NOCOUNT ON as the first statement in ur proc.

    HTH

    Thanks and Regards,

    Dilip

  • July 25, 2005 at 1:09 am

    #576666

    I asked the same question a couple of weeks ago:

    Insert into problem with linked server

    I never solved the problem...   And (at least in my case) "SET NOCOUNT ON" doesn't work either, I have tested it. It was however suggested to me that I use openquery instead. Maybe that's a possibility for you?

     

  • July 25, 2005 at 3:26 am

    #576689

    Hi,

    In my end, this works like a charm..

    insert #tempcust EXEC [server2].northwind.dbo.usp_test

    Some key points to confirm at your end:

    1. Go to Start->Run->dcomcnfg.exe

    2. This opens the COM+ window. Right click my computer->Check MSDTC tab->Security Configuration->Network DTC Access should be enabled..More importantly Allow Inbound and Outbound should be enabled depending on the requirement whether you are performing a 2-way distributed tran or one way.

    3. Repeat the same step 2 on your server 2 which is participating in the linked server chain..

    HTH...If not, please check the logs SQL as well as Event Log and if there's any errors, pls put it down here so that further troubleshooting can be done..

    Best of luck..

    Dilip

  • July 25, 2005 at 4:06 am

    #576694

    Hi Dilip,

    I am through step 1 and a window ("Distributed COM Configuration Properties") opens. But I can't locate "My Computer" here... I apologize if this is easy, I am a beginner at server administration...

    I use Windows 2000 servers. I am not sure what the original author of this thread uses...

  • July 25, 2005 at 4:52 pm

    #577019

    Thank you very much for all the suggestions.  Windows 2003 turned out to be the culprit in my case.  The procedure for configuring Win 2003 outlined in the discussion referenced above by Jesper solved my problem.  You can find it here http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=197923

  • July 25, 2005 at 5:57 pm

    #577025

    Jesper,

    At my end, both SQL boxes are Win 2003, sorry in win 2000 it has to be start->control panel->admin tools->Component Services..Expanding that u can find the My computer and then u can follow the steps..The link which u have put in ur post by Scott, it explains the settings very clearly.

    Thanks and Regards,

    Dilip

  • July 26, 2005 at 1:32 am

    #577074

    I have found My computer, right clicked, chosen properties and found the MSDTC tab. But there is nothing about security...

    Thanks for your help anyway, I appreciate it.

  • July 26, 2005 at 2:02 am

    #577085

    Same problem, I had with my Win 2003 box, had to install sp1 for win 2003. pls check whether ur win 2k box has the latest sp on it..i believe SP4 .. good luck buddy

  • July 26, 2005 at 2:10 am

    #577087

    Both my servers have Windows 2000 SP4...

  • July 27, 2005 at 8:19 am

    #577561

    i dont know have a win2k box to check this..if i get one, will let u know but i believe there might be some setting or mite be something in registry wherein u can enable/disable the security configurator..by the way does it give u any error when u try to run distributed transactions thru query analyzer between the win 2k boxes

  • July 27, 2005 at 8:23 am

    #577563

    No errors or results are returned, the query just never times out...

    Thanks for your time - I believe you are on the right track, as I have a colleague who got it to work on one server and not on the other...

Viewing 12 posts - 1 through 11 (of 11 total)

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