Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: ODBC connection - lost tables

    I have a SQL Server database that I connect a front end to using an ODBC connection. Our LAN folks upgraded the server recently and now I can no longer see any of the tables through the ODBC connection that the user used for login has permission in SQL Server Enterprise Manager to see - throught the ODBC connection the user can only see things like:

    dbo.spt_datatype_info
    dbo.spt_datatype_info_ext
    dbo.spt_fallback_db
    dbo.spt_fallback_dev
    dbo.spt_fallback_usg
    .
    .
    .
    INFORMATION_SCHEMA.CHECK_CONSTRAINTS
    INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
    etc.

    I've tried deleting the user for the connection and re-establishing it with owner permissions. I've tried deleting the dsn and re-establishing that as well but nothing so far.

    Please help!!!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sounds like your default database for the user is master...

    is it?

    Can you edit the connection and look?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    Do you mean throught the ODBC Administrator or through SQL Server Enterprise manager?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you're connecting a client to sql server...it'll be on the client...

    And since you're seeing the INFORMATION_SCHEMA views...they live in master...

    You'll need to change the default database to where your data is, I think (only sometimes, mind you)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Posts
    5
    I'm not sure how to change the master on my client machine - could you walk me through that?

    I sit next to some Oracle folks and from what they know of SQL Server there is a master dbase with sub dbases - the one I'm accessing being a sub dbase. The master dbase peers into the subs through views. It sounds to them as though the master dbase doesn't have the proper permission to see all the tables or perhaps the view is incorrect. Is this close to what you are talking about?

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    You control the default database when you create the dsn - look at your dsn settings for default database (it defaults to master).

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hold on....don't listen to the Oracle guys....different world

    been there...

    there are no "Sub" databases..there are instances (a word they should know) in sql2k...

    Hold on...I'm on win2k ok?

    go to start>Settings>control panel

    2xclick on data sources

    Find your dsn...2x click on or configure...

    Go 2 or 3 clicks on NEXT...you should see the default db....

    btw...how do you distribute the odbc to the clients?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2004
    Posts
    5
    You are the man!!! I found the default database and that was it. Thank you very much!

    For those of you reading and don't know where that is on Windows 2K and XP open your ODBC Administrator -> add a User DSN -> select a driver (in this case SQL) -> type in a name and a server, description is optional -> set up authentication as needed -> at the top of the next screen is where you change your default database -> leave default settings on the next screen -> test and you're done.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Great...and good luck...


    But how do you plan to distribute the connection?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2004
    Posts
    5
    Actually what I have is a document in a shared folder that walks the user through the ODBC setup. All users login to the dbase with the same user info and their data access is controlled through the front end. Not the best solution but the data isn't sensative and the cost was zero since me and another guy set it up in-house.

    Thanks again!

Posting Permissions

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