Dynamic connection string for pass through query

  • Hi all

    I've got a pass thorugh query which gets a list of users from SQL (various servers) that's stopped working now we've upgraded from SQL2008 to SQL2012.

    My original code was:-
    Private Sub Server_Name_Change()
      server = Me.Server_Name.Text
    '----- Update connection string for query to get database list -----
      CurrentDb.QueryDefs("DataBaseList").Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
      Me.Database_Name = ""
    '----- Update connection string for query to get latest users -----
      CurrentDb.QueryDefs("UserList").Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
      Me.ListOfUsers = ""
    End Sub

    That has now stopped working.

    I've tried various options found online but they don't work with all servers.
    I've currently got:-
    CurrentDb.QueryDefs("DataBaseList").Connect = "ODBC;driver=SQLNCLI11;SERVER=" & server & ";Initial Catalog=Master;Integrated Security=SSPI;"
    but that doesn't seem to work at all.

    I need something that will work with the following:-
    SQL 2012 Enterprise
    SQL 2012 Developer
    SQL 2008 Standard
    SQL 2012 Business Intelligence

    I know the issue is with the connection string but I can't seem to get to the bottom of the issue.

    Any help on this would be greatly appreciated.


    This connection string:-
    "ODBC;driver=SQL Server Native Client 11.0;SERVER=" & server & _

    works on the SQL 2008 Standard servers and one of the servers running Business Intelligence editions (but not the others running the same edition).

    Now I'm very confused.

  • On the servers where it doesn't work, do you get any sort of error message?

    Also, what security model are you using?  Trusted_Connection=Yes suggests it is integrated security.  But Integrated Security=SSPI suggests you are .NET - so if that isn't available that would cause a failure.  Another possibility is that your current Integrated Security User doesn't have permission to access Master.  Mostly throwing out possibilities I guess...

    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • We use integrated security on all the servers (mixed mode) and I'm a SysAdmin on the servers in question.
    The thing I'm struggling to understand is the fact that the drivers are identical on the servers but the code only works on certain servers.

  • Problem solved!!!

    There was nothing wrong with the connection string at all (apart from the tinyest of typos).

    This line:-
    CurrentDb.QueryDefs("DataBaseList").Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
    is perfectly valid except for the fact that the database name should be master not Master (note the case difference on the "M").

    This explains why the code was running on some servers (case-insensitive) but not on others (case-sensitive).

  • Yowch!  I'm really happy I don't have to deal with case sensitive servers.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • We're deliberately setting ours up as case-sensitive.
    Our main application is case-sensitive for the databases and a few associated bits and pieces so we're making any new servers that use that data case-sensitive.
    It's causing all kinds of short-term chaos (keeps us busy though :crazy:)

    On the up-side, it's enforcing some good coding standards for aliases, etc.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply