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.

SQL_Server_Cannot_Connect

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.

reg_Edit

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