Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listing SQL Databases in VB (VB6/SQL2000)

    I have a VB Application that checks for the existance of a specified database on a specified SQL Server.

    I think I need to list the databases to be able to see if the specified one is in the list.

    I don't want to use the SQLDMO method as, if the user chooses not to install MSDE (i.e. uses a networked server instead) they won't have the SQLDMO.dll - Im nowhere near expert enough at installshield to install the dll even if the user chooses not to install the whole MSDE (free SQL) app.
    Therefore, the only other method I know is to connect to the server and look in the databases table of the master database.

    However, i'm worried about permissions and at this stage my app is nowhere near complete enough to test it now.

    Does anyone know an API or something that lists databases on a specified SQL server?
    or (more of an SQL question, but I couldnt see a section for that)........
    Does anyone know if the master database on an SQL server has any security restrictions by default regarding reading from a table?

    Any help or suggestions would be great thanks.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Listing SQL Databases in VB (VB6/SQL2000)

    I don't think this thread helps with your specific question, but just for cross-reference, Enumerating SQL Servers (VB .NET 2003).

    Does the ADOX object model let you address a server, or only a particular database?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listing SQL Databases in VB (VB6/SQL2000)

    Thanks for your reply.
    First bit - you're right, that is the SQLDMO method which i'm trying to avoid in case the user does have the dll.

    Second bit, if I've got your drift, yes the ado active x control lets you connect to any database on any server, but the server will only let you in if you have adequate permissions or have provided an adequate login and password through the ADO model.

    At the moment i'm going with using ADO and opening sysdatabases from the master table. If I finish and manage to test the security of the SQL server then I will post back with my findings - in the mean time, if anything crops up to list databases on an SQL Server not using SQLDMO feel free to post it. Thanks.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Listing SQL Databases in VB (VB6/SQL2000)

    ADOX = ADO Extensions for DDL and Security. However, it appears that ADOX starts with the same type of connection string as ADO, rather than at a higher level, so it doesn't help.

    What is the plumbing in Windows' "Data Sources (ODBC)" control panel applet that lets you sniff out SQL Servers? If the tools called by that dialog help, maybe there is are related API calls?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listing SQL Databases in VB (VB6/SQL2000)

    I think you are right. That would be whatever populates the dropdown in the attached pic.

    Also, I think i've just made my question redundant.........

    I haven't used that control for a while as I got in the habit of adding file DSNs in manually.
    The dropdown will only populate if the user has a security level adequate enough to view it, so whatever my method I will need to make sure I set up the security for the user on the SQL server itself - therefore I may as well use ADO.

    Problem solved. Thanks for reminding me about the ODBC control panel, it hadnt occured to me every method of accessing the list would need the right security levels to do so.

Posting Permissions

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