locked
Is (local) really local? RRS feed

  • Question

  • I have one outstanding problem to address before I can ship my product.  In all my searches, I have not seen this problem mentioned.  It seems that you guys are my last hope.  Sorry if it has already been asked and answered.

     

    When connecting locally to a named SQL 2000 instance (SP2, SP3, SP3a, and SP4), I am noticing a lot of network traffic that I can't explain.  It happens on every machine I've tried.  Here is the setup:

    • Conn. String Server name "(local)\InstName"
    • SNU has no network protocols enabled (DISABLENETWORKPROTOCOLS=1)
    • CNU defined with TCP, Named Pipes, and Shared Memory, etc.
    • Network cable plugged in
    • ADO Provider=SQLOLEDB
    • Using UDL file UI's Test Connection button to test

    I am seeing DNS and NetBios name lookups for "(local)" (without the instance name) all of which fail of course.  After 5 seconds or so, the connection succeeds and everything works as expected.  It's that 5 seconds...

     

    Any of the following changes cause the connection to be instantaneous:

    • Unplug the network cable
    • Add TCP or Named Pipes to the server
    • Use the machinename\InstName format
    • Disable NetBios over TCP
    • Use “(local)”  (default instance) 

     So the real question is; Why is “(local)\NamedInst“ not treated (at least initially) as a shared memory candidate.  Why does it go to the network to resolve the name but does not for the default instance?

     

     

    Thursday, May 25, 2006 10:02 PM

Answers

  • ChristoLee,

    You can use "select net_library from sysprocesses where spid=@@spid" to verify whether current connection is local or not.

     

    What you observed is a known issue, new netlibrary shipped with SQL Server 2005 (SNAC) has the fix.

     

     

     

     

     

    Sunday, May 28, 2006 1:47 AM

All replies

  • Friday, May 26, 2006 12:25 AM
  • Yeah, as that document suggest, using localhost or . would be better. (I personally prefer using . - but localhost has much better factors and less ambuguity.)
    Saturday, May 27, 2006 12:38 AM
  • Hi Jens,

    Thanks for the reply.  I did come across this in my prior searches.  Since it is written for SQL2005, I am not sure what might apply to SQL2000 and what might not...  I saw another 2005 statement that SM doesn't work with MDAC 2.8 and below.  Not sure if this applies to 2000 as well. 

    Anyway, it doesn't realy address the issue, and based on the limited evidence seems to be wrong on several levels.  I'm sure it is not, it just appears that way.  For example, everything I read says that SM is tried first.  If that is so, then when connecting to (local)\someinstance why does it go to the network to lookup the name "(local)" before eventualy making the SM connection.  It is as if it is trying TCP and NP first (which are disabled on the server).  I see DNS lookups followed by NBNS lookups in my packet sniffer traces.  Using (local) for the default instance seems to work fine, but not for a named instance.  Are these name lookups happening in ADO or OLEDB before getting to the code that decides which protocol to use?

    The reason this is a problem for me is that we would like to install with network protocols disabled for non-networked computers.  If the user does plug in a netowrk cable, every single database access is > 5 seconds.  Unplug the cable or enable a protocol (which won't be used) and everything works fine.  It seems backwards that I would need a network to run local-mode... 

    Any help you can provide will be very much appreciated.  I can help someone there recreate this if you are interested.

    Regards,
    Chris

     

    Saturday, May 27, 2006 1:34 AM
  • Doesn't localhost imply TCP?  Is this new for SQL2005?
    Saturday, May 27, 2006 1:37 AM
  • ChristoLee,

    You can use "select net_library from sysprocesses where spid=@@spid" to verify whether current connection is local or not.

     

    What you observed is a known issue, new netlibrary shipped with SQL Server 2005 (SNAC) has the fix.

     

     

     

     

     

    Sunday, May 28, 2006 1:47 AM
  • Thanks much!

    I used the profiler to see how the connection was eventualy made and it did use shared memory, it just did a lot of network lookups before it got there...

    I'll save your query for future use.

    Tuesday, May 30, 2006 6:32 PM