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?

Aroma

Do you see any related messages in the SQL Server error log? – Butacaine

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.

Aroma

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.

Peptone

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.

Adieu

Answer 4

In my case, my application could not connect to the database instance even though I had no problem connecting via SSMS. It was simply because the user name I had in my connection string was not there in the user list in the database (someone removed it). SQL server should have simple said something like "Could not find the user name 'xx'" instead of this convoluted one!!

Kimmie