sqlcmd.exe - Shared Memory Provider: No process is on the other end of the pipe

Question

I have read numerous posts and articles about the error:

Shared Memory Provider: No process is on the other end of the pipe. Communication link failure.

...including these:

Error message: (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

https://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

I can connect easily and always from SSMS, both from Mixed Mode and Windows Authentication. But whenever I try to run a sql script via sqlcmd.exe, I get the error shown above.

Here is what I am running from windows command shell:

"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe" -S localhost -U sa -P myPassword -i "F:\<mypath>\<myfile>.sql"

Here is what I have tried/verified:

1. I am running SQL Server version 14.0 on Windows 10.
2. Mixed mode authentication is enabled, and I can connect from SSMS.
3. I have tried specifying 127.0.0.1 instead of localhost in my cmd-line.
4. Shared Memory protocol is enabled.
5. Named Pipes protocol is enabled.
6. TCP/IP is enabled.
7. My SQL Server service is running - I can connect from SSMS.
8. I have opened port 1433 inbound and outbound in my windows firewall, and SQL Server (as an application) is allowed by my firewall. (Even when I turn my firewall OFF I still get this same error.)
9. Remote connections are enabled.
10. SQL Server Browser service is enabled and running.
11. SQL Server Agent is enabled and running (though I do not think this is necessary).
12. I have restarted the SQL Server service and rebooted several times.

MOST IMPORTANT: This must have something to do with my input file, which is over 200 Mb. When I run the same cmd-line statement as above, but instead point to test.sql, which contains nothing more than select getdate(), it works perfectly. No errors, no connection problems.

After everything I have tried, I forever get that same error. What else can I try?

Answer 1

I finally figured it out thanks to this post. I had to add -a 32767 as a cmd-line param, presumably because of the massive size of my input script. Infernal sqlcmd could have given a more truthful error message instead of pretending it couldn't connect. The connection was never the problem. Anyway I hope this can help someone else.

Answer 2

as suggested by @HerrimanCoder the -a 32767 parameter solves the issue, in my case I was running a script 2 scripts, 1.4GB and 1.6GB respectively.

I had a brand new installation for SQL server 2019, on my personal computer. It didn't have the Named Pipes and the TCP/IP enabled, I turned them on, tried again, failed, then added the parameter and executed smoothly.

Thanks for the help.

Answer 3

I experienced the same, I tried enabling the TCP IP, Shared Memory and Name Pipes it did not help for me, what I followed is from this medium post

It says to add a parameter and it worked for me like a charm, checkout the complete steps.