Link Server Problem from SQL 7 to SQL 2000

• YeePee

  Mr or Mrs. 500

  Points: 561

  More actions

  August 13, 2004 at 9:16 am

  #65218

  Hi,

  I am having a linked server from sql 7 to sql200 through a remote login and password.

  I need to execute a procedure residing on sql2000 and insert the data to a temporary table in sql 7.

  This is what I did

  Step 1. sql2000 has the stored procedure

  create procedure spname

  select column1 from table1

  give permission for login account account1 to execute this procedure

  Step 2. Created a linked server on sql 7 pointing to sql200 with

  remote login account account1

  execute below stmt in sql7

  create table #t1 ( name1 varchar(20) null)

  insert #t1 exec sql2000.dbname.dbo.spname

   

  When I execute this stmt on sql7 it works fine: exec

  sql2000.dbname.dbo.spname

  So the way I have defined the linked server is correct.

  but when I execute

  insert #t1 exec sql2000.dbname.dbo.spname

  it just runs for ever does not give error message.

   

  This was working when both where sql7's

  Thanks a lot for help.

  regards

  Elias

   

   

   

• ~nano

  SSC Eights!

  Points: 804

  More actions

  August 15, 2004 at 11:37 pm

  #519031

  Hi Elias

  Have you ever let the procedure come to an end??? I had a similar problem lining SQL7 & SQL2000 - A Query that was executed locally with a replica of the tables on the linkserver took just under 2 Seconds. When I switched to use the production tables on the linkserver the exact same query need over 15 minutes to finish.

  When looking at the Plan I realized, that due to the differnece in sorting (SQL7 & SQL2000 work about sorting differently) My Query was retrieving all of the data onto the local machine, then sorting it around, so it would fit SQL7, and then doing the actual Query. No work load was carried out on the remote server except reading the whole table.

  Have you configured the linkserver right??? You need to configure the linkserver, so that the workload can be shared or rather, so that sorting and filtering can be done on the remote server, which speeds things up, as only the filtered, sorted data has to be retrieved. Read BOL on configuring the linkserver - using remote sort, compatible sorting etc. After configuring my linkserver, I got my Query time back to just under 2 Seconds.

  Hope this points you in the right direction.

• YeePee

  Mr or Mrs. 500

  Points: 561

  More actions

  August 16, 2004 at 7:50 am

  #519105

  Hi There,  

   The linked server was configured correctly.I found ou thte problem and  solved it . The Problem was that my SQL 2000 machine was running on Windows 2003 and it did not have Distributed Transaction Cordinator installed.So the values returned were not reaching my SQL 7 on Windows 2000  (Refered to Microsoft KB article 817064&nbsp  .

   

  I had to install  DTC and run that service. Windows 2003 does not install DTC by default.

   

  Thanks for your time 

  Elias