April 21, 2017 at 8:33 am
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()
Stop
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 = ""
Me.Database_Name.Requery
'----- Update connection string for query to get latest users -----
CurrentDb.QueryDefs("UserList").Connect = "ODBC;DRIVER=SQL Server;SERVER=" & server & ";DATABASE=Master"
Me.ListOfUsers = ""
Me.ListOfUsers.Requery
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.
::edit::
This connection string:-
"ODBC;driver=SQL Server Native Client 11.0;SERVER=" & server & _
";DATABASE=Master;Trusted_Connection=Yes;"
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.
April 21, 2017 at 2:58 pm
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...
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 24, 2017 at 1:46 am
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.
April 24, 2017 at 4:59 am
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).
April 24, 2017 at 8:35 am
Yowch! I'm really happy I don't have to deal with case sensitive servers.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2017 at 8:48 am
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