I cannot for the life of me set up a linked server.  I am trying to do what I
think must be the simplest, easiest linked server set up there is: Link an
instance of SQL Server 2000 to another instance of SQL Server 2000.  I've
read everything I can find in BOL, on the web, in my sys Admin book.  I'm
missing something somewhere.

Would someone be willing to step me through the process?  I am about to
scream!  This is the most befuddling thing I have tried to do in a long time.

Still need to understand more about your network.  But perhaps this simple
config will work.

On the General tab:

For the name, use the name you use to refer to the server on the network.
Make sure you can ping that server and that you can connect via Query
Analyzer.  (I find this works best with SQL Authentication, which we'll get
to on the next tab.)

For the Server type, pick SQL Server, obviously.  For some bizarre reason,
Jet 4.0 is the default provider.

On the Security tab, I have had the best luck with leaving the local login
blank, and using the "Be made using this security context:" option.  In the
Remote login / password areas, I use the same credentials I used above in
Query Analyzer when verifying that I can connect to that server with that
login/password.

On the Server Options tab, I typically have the following items checked:

- Data Access
- RPC
- RPC Out





Show quoteHide quote

Thanks for the reply.  I've done just what you said and things seem to be a
little better but I'm not out of the woods yet.

Now I get Error 7202: Could not find server 'SV1173' in sysservers.  Execute
sp_addlinkedserver to add the server to sysservers.

I thought that setting up the linked server through Enterprise Manager would
have added SV1173 to sysservers, no?  When I look at the info on
sp_addlinkedserver, it looks like it does the same things as right-clicking
on Linked Servers in Enterprise Manager.

False Alarm.  I checked sysservers and found SV1173 there.   I had typed
SV1137 in my sp when it should have been SV1173.

Now I'm right back where I was yesterday.  Enterprise Manager will not let
me save my sp because it thinks that ANSI_NULLS and ANSI_WARNINGS need to be
set because it thinks I am trying to execute a heterogeneous query.  But I'm
not, am I?  Both servers are SQL Server 2000.

CLR Triggers and inserted table
SQL script, split digits and letters
Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or..?
Query help - resultset too large... impossible!
Used Disk Space reporting and sending Email