query from sql server 7 to 2000

• Ted Jackson

  Grasshopper

  Points: 13

  More actions

  May 9, 2006 at 8:22 am

  #93906

  I need to query a sql 2000 database from a different sql 7 server.  I'll be doing this using query analyzer and eventually setting up a dts package.

   

  Any help is appreciated.

• Ted Jackson

  Grasshopper

  Points: 13

  More actions

  May 10, 2006 at 5:59 am

  #636968

  --after googleing this to death I came up with the following that worked very well

   

  --using the sp_addlinkedserver stored procedure, update master.dbo.sysservers table of the ---server you are on with the other server to query

  USE master

  GO

  EXEC sp_addlinkedserver 'ServerName', 'SQL Server'

  GO

  --query the other server using the openquery function

  Select * from OpenQuery (ServerName, 'SELECT * FROM ServerName.dbo.TableName')

  --rsuser will have to be given rights to whatever table is queried