this may be a simply question but I hope its not.
I run some very long winded SQL operations on my local PC, which is hard wired to an ADSL modem for internet. The SQL Server and databases are ALL local on my PC, and the processing seems fine. (as fast as normal).
However, if my internet drops out - which happens perhaps a few times a night (at later hours usually), my SQL connection also drops with the familiar Connection lost error. (An error one would get if connecting to SQL over a network)
For me this does not make sense, my SQL connection string refers to the local instance only and no processing is over a network of any kind (I have VPN sometimes active, but not always when this happens)
I can run the same SQL processing without the modem connected with no issues at all. (Although sometimes it can take many many hours so I prefer to have the modem connected)
Could this possibly be due to the extra SQL Services ie Browser - that is somehow affected by the modem losing its internet connection?
(I would like to know that my ISP-provided modem is not doing any funny business in the background - like examining my data / traffic / etc)
Any help appreciated
Try this connection string:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=DATABASE_I_USE;Data Source=127.0.0.1\INSTANCENAME
This should force the traffic onto the loopback adapter, which will allow it to avoid cases where your physical card's network link may drop.
If that does not work, try explicitly installing a loopback adapter.