SQL Pass Through Query fails to connect on some machines.

[john471]

I have a Pass Through query which works fine on my PC. I tested it on two other PCs in my office and two further PCs in other (distant) sites. One of the other PCs in the office can run the query with no problem, but the other three get an error message :-

ODBC--connection to 'SQL Serversyd05-pwfm' failed

That is all it says.

The connection string is:-
ODBC;DRIVER=SQL Server;SERVER=syd05-pwfm;UID=SECRET;PWD=NoSay;DATABASE=TCS;Initial Catalog=TCS

My mahine is running WIN2K, the other two machines in this office I tested it on are WINNT (one works the other doesn't).
Both WINNT machines seem to have the same version on SQL Server driver (3.70.09.61)

The following code gives the expected result on my PC and the other two in this office. I can't test the code on the remote machines, just now as their users have gone home for the weekend.

Private Sub MyTest()
    Dim con As New ADODB.Connection
    Dim rsEmp As ADODB.Recordset
    
    con.Provider = "SQLOLEDB.1"
    con.ConnectionString = "Extended Properties=""NETWORK=DBMSSOCN;DRIVER=SQL Server;SERVER=syd05-pwfm;UID=SECRET;PWD=NoSay;DATABASE=TCS;Initial Catalog=TCS"""
    con.CommandTimeout = 30
    con.ConnectionTimeout = 60
    con.CursorLocation = adUseServer
    con.Open
    
    Set rsEmp = New ADODB.Recordset
    rsEmp.Open "SELECT EMP.LAST_NAME FROM EMP;", con, adOpenStatic, adLockReadOnly, adCmdText 'Synchronous is default
    If Not (rsEmp.BOF And rsEmp.EOF) Then
        rsEmp.MoveFirst
        Do Until rsEmp.EOF
            Echo True, rsEmp.Fields("LAST_NAME").Value
            rsEmp.MoveNext
        Loop
    End If
    rsEmp.Close
    Set rsEmp = Nothing
    con.Close
    Set con = Nothing
End Sub

Both the WINNT machines in this office also have a DSN set up to this server, and if I change the Passs Through query to use the DSN, then both WINNT machines work. However, I have several dozen users in 4 sites in 4 states, and can't be configuring DSNs on all their machines. I understand that the DSNs could could be created using VBA code, but I'm not sure our IT police would look favourably on that if they caught me !!!

Connection string using DSN:-
ODBC;DSN=PEWFM;Description=Prod eWFM;UID=SECRET;PWD=NoSay;DATABASE=TCS;Network=DBMSSOCN;Address=syd05-pwfm,1433

issuing SELECT @@VERSION to the server yields :-

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


What to try, what to try, what to try ?????

::Edit::
Ultimately I want the Pass Through query/queries to be the Record Source for some reports.

 

[john471]

Anybody seen anything like this before?

Am I missing some crucial point on a DSN-less connection as to why (on the one PC) it would work in code using ADO but not for a pass-through query ?

Any helpful suggestions as to what I should look at or try ?

 

[john471]

Success !

After many trials and tribulations, I finally got it working; with thanks to the DSN Stripper utility linked to in This Thread

and a bit of playing around, the following connection string worked on every PC I tested it on in my home office, including the one that refused to work before; so the way I see it, I am at least one step closer - now I just have to ensure it works at my distant sites....

Working Connection String:-
ODBC;DRIVER=SQL Server;SERVER=syd05-pwfm;UID=SECRET;PWD=NoSay;DATABASE=TCS;Network=DBMSSOCN;Address=syd05-pwfm,1433

Thought I'd post back incase it assists anyone else.