5

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

JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245
  • 1
    I had a 242 MB script with over 850K insert statements. I just modified the script by added a GO statement after every insert statement. So total number of lines became 1,700K. Then the sqlcmd utility successfully ran. Without the GO statement, I was getting the error - "TCP Provider: An existing connection was forcibly closed by the remote host. Communication link failure" – Varun Sharma Jul 18 '22 at 10:35
  • May have been a slightly different cause for the same error. Interesting. Were the INSERT statements all the same table or different tables? – JustBeingHelpful Jul 22 '22 at 19:40
  • Yes they were all for same tables. – Varun Sharma Jul 22 '22 at 21:26
  • 1
    I have no idea why you'd get the same error, but you can probably increase the performance/speed of your query(ies) by doing bulk insert commands, meaning put multiple records in the VALUES section of your commands. INSERT INTO (x, y) VALUES (1,2) .. bulk would be .. INSERT INTO (x, y) VALUES (1,2), (2,3); – JustBeingHelpful Jul 23 '22 at 07:30

4 Answers4

3

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\
JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245
1

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.

Johann Contuzi
  • 41
  • 3
  • I had this issue with the Linux client. Turns out 5,762,070 statements at once is too many. My system can handle about 1.5 million rows before tripping up. – Aaron Mason May 30 '23 at 03:21
0

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.

Dustin Morrison
  • 97
  • 2
  • 4
0

[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\

Ola
  • 1