Self Linked Server

Hi,

I am setting up a linked server on my local SQL server 2000 development
machine.
The server is linking to itself and I am setting this up soley for learning
purposes.

I first used the command

Exec sp_addlinkedserver @server='link' , @srvproduct='SQLServer OLEDB
Provider' , @provider='SQLOLEDB' , @datasrc='Developement'

which worked (ie added the linked server - I verified this by running EXEC
sp_linkedservers), but when I ran the following query

select CustomerID, CompanyName from link.northwind.dbo.Customers

I was given the error message

SQL Server does not exist or access denied.

This seemed strange as my security credentials should be the same on the
linked server and the local server as they are basically both the same
machine.
I am using intergrated security.
Incidently the method above i got from my wrox press sql server 2000 book
and this eems to work fine in the book.

secondly I used the following command to add a linked server back to itself

EXEC sp_addlinkedserver
@server = 'self',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = NULL,
@location = NULL,
@provstr = 'DRIVER={SQL Server};Server=(local); Initial
Catalog=master;Integrated Security=SSPI;'

I then ran my query

select CustomerID, CompanyName from link.northwind.dbo.Customers

which returned the results I was expecting.

I would like to ask what I did wrong in the first example.
as the user accounts are the same on the linked server and local machine
(they are basically both the same instance) I am a little stumped.

thanks in advance

cheers

martin.

Hi, Martin

Is your computer name "Development" ? If no, replace the @datasrc with
your computer name.

If yes, check the current setting for the logins mapping, using
Management Studio or Enterprise Manager, in the Security page of the
linked server properties. The third option should be checked "Be made
using the login's current security context".

Razvan