SQL Server 2000 Linked server query fails on a workgroup?

• fx1974

  Valued Member

  Points: 73

  More actions

  June 5, 2008 at 2:56 pm

  #121884

  Hi all

  SQL Server experts, I need help please! Here is the scenario:

  I install SQL Server 2000 on two machines in a workgroup (e.g. SERVER1 and SERVER2). Both are default instances.

  Opening a session in SQL Query Analyzer to SERVER2, I add a linked server pointing to SERVER1 using sp_addlinkedserver. e.g. sp_addlinkedserver @server = 'SERVER1', @srvproduct = 'SQL Server'

  Then add a remote login using sp_addlinkedsrvlogin.

  e.g. sp_addlinkedsrvlogin @rmtsrvname = 'SERVER1', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'sa_password'

  Now when I do a simple query from SQL Query Analyzer like this:

  select * from [SERVER1].table1.dbo.field1

  it says 'SQL Server does not exist or access is denied.'

  I checked sysservers and SERVER1 is there. If i login to SQL Query Analyzer and perform the same query (excluding the [SERVER1] qualifier), records are returned.

  Performing a similar task in a domain environment does not reproduce the problem. Are there any know issues with running distributed queries in a workgroup? Tried to do a web search but leads me nowhere.

  Any information is appreciated.

  Thanks!

  Bernard