SQL Server – error 64 : Server-name no longer available

I ran across a crazy issue this am that I’ve never seen before. I was setting up SQL Server Express 2008R2 on a smaller server at home, the likes of which I have done many times before with no issues. Then I did the following, per my usual config routine:

  • Opened ports 1433 and 1434 on this server for “listening” for SQL requests
  • Opened a hole in the firewall for SQLBrowser.exe
  • Enabled TCP/IP listening for those same ports in SQLServerManager10.msc (running on Windows 10 Professional operating system).
  • Enabled remote connections to the Server process
  • Set up a login account for Administrator with full rights to the SQL server processes

So then I went to test the setup from my laptop, fired up SQL Server Management Studio, and connected to the SQL Server.  Connection made – check.  Opened up a known scalar function to modify – check.  Run the alter script – check.  Run it again to double check the persistence of the network connection – ERROR 64.


I had never seen that one before, but I knew it was network or network protocol related.

I remoted into this particular server and started double-checking all settings that would seem to have any relevance to that issue.

After A LOT of Googling and matching snippets of information together, I finally found resolution with the following regedit modification for the local registry.

  • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters
  • Create a DWORD “Sesstimeout” key with a decimal value of 360.


It seems the session-layer of the connection was not persistent enough for remote connections.  Why doesn’t Microsoft take care of this by default with a config script? We might never live long enough to know…

Problem solved, for now.  If I see it again, I will update this post.

Find all triggers in a SQL Server database

Use the following small script to find all hidden triggers in a SQL Server database.  Pretty much all competent developers using SQL agree that using triggers is a bad practice, but few know how to find them before they have wasted a lot of development and testing time not understanding why things aren’t working the way they want them to (because of triggers doing behind the scenes work, undesirably).

Trigger the horse, with Roy Rogers.
-- Finds all "hidden" triggers in a database. Using triggers is not a best practice.
USE <database_name>;
SELECT  name AS [TriggerName]
FROM    sys.objects
WHERE   OBJECTPROPERTY(object_id, 'IsTrigger') <> 0;