Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24

Thread: SQL Server 2000 - absolute beginner! Please help me

  1. #16
    Join Date
    Sep 2002
    Posts
    5,938
    Any specific error?

  2. #17
    Join Date
    Sep 2002
    Location
    DALLAS
    Posts
    25
    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 ?

  3. #18
    Join Date
    Sep 2002
    Location
    Castellanza, Italy
    Posts
    15
    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!

  4. #19
    Join Date
    Sep 2002
    Posts
    6
    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
    Last edited by ljmou9; 11-21-2002 at 08:48 PM.

  5. #20
    Join Date
    Sep 2002
    Posts
    6
    Sorry, David!

    I posted my reply without seeing yours!

    Jun

  6. #21
    Join Date
    Sep 2002
    Location
    Castellanza, Italy
    Posts
    15
    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

  7. #22
    Join Date
    Nov 2002
    Posts
    84
    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

  8. #23
    Join Date
    Oct 2002
    Location
    Ann arbor , MI
    Posts
    5
    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

  9. #24
    Join Date
    Nov 2002
    Location
    Denver
    Posts
    1

    Permissions

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •