SQL Server 2000 - absolute beginner! Please help me

Hello there...
Pending receiving the full version from the higher powers at my work, I have downloaded the Evaluation version of SQL 2000, and set up an instance on my desktop machine. This was set up on an administrator account, and works fine.
My main aim with SQL 2000 is to upsize all the companies piddling little Access 97 databases into an SQL backend and keep the 97 frontends. I have migrated some of the tablesets into SQL, and linked to them in Access 97 and they work fine.
However, the problems are when I try and set up ODBC. When I enter ODBC as a different user on the server itself, the ODBC list of available SQL Servers shows me the correct server information - my PC ID no, followed by the server name. However, when I try and connect with another machine on the network, it only displays the PC ID no of the server, and not the server name. When I attempt to add the server with just the PC name, it doesnt find the server - same when I add the name of the database to the end of the PC ID no.
I'm sure there is something really basic I have not done, but I'm totally stumped now. I am using Windows Auth, and have set up the login of the other PC I am trying to connect, as an authorised user.
Please help me - I am just a doofus, please feel free to laugh at me...

Not laughing - we all started somewhere!

First thing to check.

Are the machines members of a domain or not. Windows auth across machines requires a domain.

Yes they are all part of the same NT domain.
Cheers for not laughing, I have training for this booked for Jan, but a project to complete prior to them - IT eh.

Did you install sql server on those pcs with problem? What's error message?

No I'm not going to be installing SQL server on those machines, only using ODBC to connect Access 97 databases to them - they are extremely tight for user licences in this company, and they refuse...
Hope I have understood/explained correctly.

Do you have odbc driver for ms sql on those pcs?

try using system ODBC DSN ....

Go to drivers tab to see if sql server driver is there.

Hi there,
I do have SQL drivers on the other PCs. I'm not sure what you are suggesting when you say "Use ODBC DSN"... please could you elaborate.

Then create system dsn that points to your sql server.

Hi... I have tried to create a System DSN on the client machines, and have one set up on my server that works. However, I still am unable to "see" the instance on a remote machine - only the server name.:confused:

Have to type it.

Hi... I'm afraid I have tried that too... and it doesn't work when you type it either.
I have tried it from a machine with SQL Server Client software on it, and the Client network utility picks it up on TCP/IP - server name and instance. However, it still won't set it up in ODBC.

Choose tcp/ip netlib in odbc.

When I am configuring the Client configuration on ODBC, it is set to TCP/IP already, and to dynamically determine the port - the same as the one working connection I have set up. It still doesn't work.

Any specific error?

Try PING to the server by name.
If this works, try forcing the ODBC TCP/IP port# to the port being used by the SQL server.
To identify the port number SQL server is listening to, run the follwoing statement in Query Analyser connected to your server.

master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocket NetLib\Tcp', 'DefaultPort'

just a thought..
HTH.,.

By the way, have you installed a named instance of SQL 2000 ?

Two suggestions:

1. check that all client PCs have at least MDAC 2.1 libraries installed (see http://www.microsoft.com/data)...these are freeware libraries

2. Use file DSNs for defining the ODBC connections when you attach the SQL tables in Access 97. This has the great advantage that the attached table remembers the full path to the SQL database (name of server, name of database) so that when you distribute the Access front ends to users you do not have to set up anything on their PCs.

I have a lot of Access 97 users connected to SQL (7 and 2000) and have migrated many databases and applications.

We are now slowly moving to Office XP.

Good luck!

Danzarak,

On one of your replies, you mentioned that when you use ODBC System DSN, it worked on one machine, but it did not when trying from another machine. System DSN is machine specific.

What you need to do is to create a file DSN, store the file DSN on a network location (specify the location using UNC, not mapped drive since users may map the Network drive using different drive letter). Give all users who need to use that ODBC DSN proper permission. After creating your ODBC File DSN, link your SQL database from any of your Access database, it should work for all the PCs and the users who have the proper SQL database access permission.

Difference between System DSN, User DSN and File DSN: System DSN only works on the machine where it is created for all the users, User DSN only works for that user on all the machine, File DSN will work on all the machines and for all the users as long as the DSN file is located on Network Drive specified with UNC and users have permission to access the DSN.

Hope this is helpful.

Jun

Sorry, David!

I posted my reply without seeing yours!

Jun

For tjmou: your reply was clearer, anyway, so I don't see why you should say sorry!

For everyone: actually, what I discovered (by chance) is that using attached tables in Access 97, if you attach them using File DSNs, the other clients do not need to have access to any DSN, everything you need is memorised in the MDB or MDE file (obviously they must have access to the SQL database through their domain login).

Regards

Hi,

Did you give any instance name while installing. Are you giving the server name with instance name

Other checks

Check for HOSTS LMHOSTS files whether the servername and IP address are there
on the machines



Thanks,
Anu

I guess , it may be name resolution issue of some kind. Enter the IP address followed by the PC name in the c:\winnt\system32\drivers\etc\hosts file

Like these entries

127.0.0.1 localhost
<IP of the PC> PC name
.
.
.

HTH
-Jay

Danzarak,

In the event that you still have a question regarding this, I noticed in one of your postings that you said
"I have tried it from a machine with SQL Server Client software on it, and the Client network utility picks it up on TCP/IP - server name and instance. However, it still won't set it up in ODBC."

I've had this similar problem. What resolved it was giving the user logged into the client machine admin rights to their own box. My specific situation is such that users are given this right and that right, however, while I can fully configure an odbc connection, I cannot save it. Thus, it's worthless. File DSN's would likely work in this case, although I'm not a big fan of them. See if you can get the user added as an admin to their local box, and I believe it will remedy your situation.

Hope it helps.