Linked Server Creation - Connect sql sever 2008 from 2000

• Ramkumar.K

  Default port

  Points: 1405

  More actions

  September 23, 2010 at 10:44 pm

  #100311

  Hi,

  Since this may be concerned as a kiddish question. But this is required.

  I need to connect the sql server 2008 database from sql server 2000. Since the client has been using the 2000 version database and they want to pull some data from sql server 2008 database. I have tried to create the linked server for this with below T-SQL statements.

  EXEC sp_addlinkedserver

  @server = 'LS',@datasrc = 'ServerName',

  @provider = 'SQLOLEDB', @srvproduct = '', @catalog = 'databaseName'

  EXEC sp_addlinkedsrvlogin 'LS','false',NULL, 'Username', 'password'

  EXEC sp_serveroption 'LS','data access','on'

  EXEC sp_serveroption 'LS','rpc out','on'

  EXEC sp_serveroption 'LS','rpc','on'

  The linked server object is created. But when I am trying to fetch the objects with below query

  select top 5 * from [CCM7Dev].OEDB70_intermediary.dbo.company

  I have received the below error.

  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

  I am suspecting whether it is possible or not for below reasons.

  >Version compatiability problem

  >Provider mismatch or updagradation required

  >Connection problem.

  Note: I have used the valid credential to access the server. Openrowset method may not suit to my requirement.

  Kindly advise me on this.

  Thanks in advance.

  Ramkumar.K

  Ramkumar . K
  Senior Developer
  ######################
  No Surrender... No Give Up....
  ######################

• javpinero

  SSC Journeyman

  Points: 85

  More actions

  November 10, 2010 at 8:17 am

  #1248910

  Didi you ever find a solution to this? I am having the same problem.

• Earl Downs

  SSC-Addicted

  Points: 437

  More actions

  May 11, 2011 at 3:23 pm

  #1323404

  I thank to yoy for the script above, it so helpfull to me.

  I have the enterprise edition for sql server 2008 and standar for 2000. No aditional problems.

  Regards.

• ramkumar533-1142700

  SSC Rookie

  Points: 43

  More actions

  May 12, 2011 at 4:15 am

  #1323554

  Thanks Man. Atlease for you it has worked. :-).

  May be the SQL Server version name you specified could help to investigate further on this.

  Thanks.

• Luk - ARG

  SSChasing Mays

  Points: 621

  More actions

  January 19, 2012 at 12:37 pm

  #1436128

  This solution worked fine for me as well.

  Thanks a lot.

  Lucas