Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    ODBC over a WAN (#N/A)

    Not sure if this belongs in this forum, but here goes. I have an Excel workbook that uses data queries to retrieve data from a SQL Server database. Everything works great over a LAN connection, i.e., when the users are in the same building as the server, but it almost never works for remote users. The error message mentions something like 'server status 08001', which the SQL Server DBA tells me means it timed out. Within the Excel VBA code I've tried increasing the Application.ODBCTimeout property, but it makes no difference.
    The IT guys say that Excel is not really built to do that kind of stuff, but I'm not buying that because I think the fact that the ODBC call was originated from Excel is not relevant. And if it *is* just an ODBC/network issue, then I would think it would have to be possible (even if slow). I've used another product called Brio that connects over a WAN via an ODBC connection, and I've seen that pull back large amounts of data (> 10 MB).
    If anybody has any comments/suggestions/pointers, they would be much appreciated. Or even opinions on whether what I'm trying to do is possible!

    Thanks,
    Colin.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC over a WAN (#N/A)

    Have you tried looking at the settings of the ODBC *driver* (Control panel, ODBC), maybe it is in the driver's settings?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ODBC over a WAN (#N/A)

    Thanks for the reply. Yes, that's been one of the things I've been experimenting with - e.g., changing how the server is referenced (server name, IP address, etc.).

    Actually the current hypothesis relates to one of the settings. When an ODBC connection is defined, it seems the default protocol is TCP/IP, which is apparently also referred to as TCP/IP Sockets. You wouldn't ordinarily see it - you'd need to press the 'Client Configuration' button on the second screen. One of the other choices is 'Named Pipes', which is usually the default protocol on SQL Server. For our server, both protocols are supposedly defined, but I'm going to have the DBA change the default to TCP/IP and see if that does the trick. I read that over a WAN (or a slow LAN), TCP/IP gives much better performance than Named Pipes.

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    St. Louis, Missouri, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC over a WAN (#N/A)

    I've had similar issues with ODBC (not necessarily using Excel) in which the SQL Server driver settings on the client must be changed to access a server that is in a different part of our corporate network. Go into the Client Configuration settings, make sure TCP/IP is selected instead of Named Pipes. You may need to uncheck the Dynamically determine port box and enter a port number (the SQL Server port is 1433). Sometimes you must map the server using the IP address instead of the assigned name--must be a DNS issue or something. Anyway, I'm not an expert in this area but these are the things that I've done in the past to resolve connection issues. Could also be that the port is blocked somewhere by the network admins but not in your network area (Blaster virus prevention, etc.) Maybe you could check in one of the other forums for ODBC experts...

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ODBC over a WAN (#N/A)

    Steve - thanks for the detailed response. I had made sure that TCP/IP was selected. When I had the DBA switch the default protocol from Named Pipes to TCP/IP, that at lease fixed the problem with connections coming in over a VPN. The corporate WAN is a horse of a different color, though. I think I'm running into some of the issues you alluded to. To make the troubleshooting more interesting, I'm finding that it's inconsistent. Sometimes it works, sometimes I can't even ping the IP address of the server. Anyway, 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
  •