CODEWITHSUNDEEP
sql-serversql-server-2008sqlcmdsql-server-native-client
JustBeingHelpful
JustBeingHelpful

Reputation: 18980

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link failure

Below, you'll notice some Windows Command Prompt commands that I'm trying to call from the 64 bit Program Files folder for Microsoft SQL Server 2008

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>SQLCMD -S "SQLI2B2" -d
 "I2B2_CRC-DEV" -i "C:\SVN\ONT\SQL SERVER\DX_SQLSERVER.sql" -E

HResult 0x2746, Level 16, State 1 TCP Provider: An existing connection was forcibly closed by the remote host.

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link failure.

c:\Program Files\Microsoft SQL Server\100\Tools\Binn>

Now, the database (server) I'm connecting to is SQL Server 2008. I had several directories under the "Microsoft SQL Server" folder. 90, 100, 110, and 120. Only 100 and 110 had the .\Tools\Binn\SQLCMD.EXE file. I noticed the following drivers in \\Control Panel\All Control Panel Items\Administrative Tools\Data Sources (ODBC)\Drivers\, so I don't know if the client driver is correct for my server version.

Name                            Version
SQL Server                      6.01.7601.17514
SQL Server Native Client 10.0   2009.100.1600.01
SQL Server Native Client 11.0   2011.110.3000.00

The -E in the command implies to use Windows Authentication, instead of SQL Server Authentication (with an actual user and password).

What could be causing this error, and what can fix it?

Notes:

This was found on Windows 7. The following resolution only applies to earlier Operating Systems.

https://msdn.microsoft.com/en-us/library/ms187005.aspx

Upvotes: 5

Views: 10645

Answers (4)

Ola
Ola

Reputation: 1

[TCP Provider: An existing connection was forcibly closed by the remote host.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Client unable to establish connection. Process Exit Code 1. The step failed]

This works fine for me. I installed latest SSMS and that installed latest sql native client, i then map the path in environmental variable. also, remember to move the new path up so that it will be the first to use. \Control Panel\All Control Panel Items\System\Advanced system settings\Environment Variables\

Upvotes: 0

Johann Contuzi
Johann Contuzi

Reputation: 51

Varun Sharma mentioned in the first answer, that he added a GO statement after each line. That is too much. I have added a GO every 1000 lines and it worked for me.

Upvotes: 1

Dustin Morrison
Dustin Morrison

Reputation: 157

I got this error due to SQL Server not liking one of my commented out lines of SQL. My advice is to start with the simplest code to validate that it is not a connection issue and then work up from there.

The commented out line was --EXEC master..xp_cmdshell 'bcp ... for which the remote computer did not have permission to run. There is an almost identical line later on in the code that was accepted, so I think this is some vague security patch error. You would think a commented out line would have no affect on connectivity issues, but here we are.

Upvotes: 0

JustBeingHelpful
JustBeingHelpful

Reputation: 18980

First error:

TCP Provider: An existing connection was forcibly closed by the remote host.

Communication link failure

Smaller files will run, but if the file is too big, you'll still receive this exception. The only solution I could find was to break the file containing the T-SQL commands into multiple smaller files.

Second error:

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link failure.

There must be other files in the "Binn" folder that SQLCMD.EXE needs to run besides the executable file itself. Either append to the existing SYSTEM "PATH" environment variable or add a new USER "PATH" environment variable in \\Control Panel\All Control Panel Items\System\Advanced system settings\Environment Variables\.

Variable name: PATH
Variable value: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\

Upvotes: 5

Related Questions