SQL query failing because of commented part of T-SQL query

I am executing below query but I am facing issue.

Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along with remote connections and I am able to execute the code on local machines (Ex: If I connect to SQL 2008 R2 machine and execute on SQL 2008 R2 Successful, If I connect to SQL 2014 Machine Successful on SSMS on SQL 2014.Issue is only when I connect to SQL 2008 R2 and execute on SQL 2014.When I remove the commented part I am able to execute the code successfully using SQLCMD.) I am not aware of any compatibility issues of this case.


Msg 121, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

But when I removed the commented part and execute the query it is running successfully in fraction of seconds. Please let me know what is wrong with the commented part. I have pasted the T-SQL query below.

```
/
* Summary: Creates source database schema for BCP Import. Only created PK on the tables
* No other constraints are replicated
* Parameters:
* tempdbname
* Called by: PROCS.uspEngagement_Import
* Remarks:
* Copyright: Copyright 2012 by XXXXX XXXXX. All rights reserved.
* Author: XXXXX
* Creation Date: July 07 2012
* Revision History:
*
* XXXXX July 31 2012 Removed IF EXISTS as this is required for non sysadmin user
* XXXXX July 31 2012 Removed reference to sys.database_files as it returns NULL for non sysadmin users
* XXXXX Aug 26 2012 Added Logic for FILESTREAM, ROWGUIDCOL and UNIQUE Columns. Note FILESTREAM
Can be created only if there is a UNIQUE ROWGUIDCOL
* XXXXX Sep 08 2012 Removed logic to create default constraints. The defaults are not required besides
ar

Given that the script works without the header comment block but fails with it (but only for remote / TCP connections), the issue is most likely due to a parsing error. For example, SQLCMD and SSMS do their own pre-parsing of the submitted script (to break it into batches), and sometimes that pre-parsing doesn't work correctly (e.g. "GO" in 2nd half of nested block comments breaks batch parsing in SSMS and SQLCMD). To narrow it down, you can try the following tests:

-
Highlight the entire /.../ comment block at the top and then hit Control-K, Control-C to comment each line out via inline comments (i.e. --):

--/********************************************************************************
--* Summary: Creates source database schema for BCP Import. Only created PK on ...
--...
--*********************************************************************************/

-
Remove just a portion of the comment block. So maybe start by removing most of it (keep the top 3 lines and the bottom ***/ line). Then, if it works, keep adding back in the lines, starting with line #4, until it stops working. Meaning, the comment block would start out as being just the following:

/********************************************************************************
* Summary: Creates source database schema for BCP Import. Only created PK on ...
* No other constraints are replicated
*********************************************************************************/

If that works, add in the 4th line so that the comment block is now:

/********************************************************************************
* Summary: Creates source database schema for BCP Import. Only created PK on ...
* No other constraints are replicated
* Parameters: 
*********************************************************************************/

O.P. tried method #2 above and was able to get the script to work remotely with only the following line (4th from the bottom) removed:

/*******...
  Computed column definition being hard coded now as xp_cmdshell cannot read from sys.computed_columns
...*******/

O.P. then used inline comment (method #1 above) to narrow down a subset of the offending line and was able to get the following to work:

/*******...
  Computed column definition being hard coded now as --xp_cmdshell cannot read from sys.computed_columns
...*******/

At this point it is still unclear as to the exact root cause of the issue. I was unable to reproduce this error using the exact same comment block, testing in both SSMS 2008 R2 and SSMS 2012, even enabling SQLCMD Mode and forcing a TCP/IP connection (verifying via SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;). @MartinSmith asked in the discussion if there was "any sort of network security between the client and the server that might take exception to xp_cmdshell". That still could be a possibility, hence more testing is needed in order to determine what layer, specifically, is unhappy with xp_cmdshell (or some other part of that line that is to the right of the newly added --).

UPDATE

While the root cause has not been identified, here is more info to hopefully help narrow this down:

-
The O.P. ran the following two statements via SSMS, SQLCMD, and .NET SqlClient:

-
Running just xp_cmdshell (by itself) gets a "missing parameter" error, not the timeout

-
Running xp_cmdshell 'cls' gets the timeout error. The error includes "HRESULT: -2146232060".

SELECT CONVERT(VARBINARY(30), -2146232060 );
-- 0x80131904

Searching on "HRESULT: 0x80131904" finds that it is a not-very-specific SQL Server error (but mostly related to SharePoint, for some reason). Here are some of the variations on it:

-
https://social.technet.microsoft.com/Forums/office/en-US/24e3d1ea-7827-4e19-b099-fd44e7c0f9ec/error-exception-from-hresult-0x80131904?forum=sharepointadminprevious


My problem was solved by rebooting all servers in the farm, including SQL Server, which would have truncated TEMPDB, as one article suggests.

-
https://social.technet.microsoft.com/Forums/sharepoint/en-US/823ce071-72a8-4d6d-9bef-068cbe2b602d/error-exception-from-hresult-0x80131904?forum=sharepointadminprevious


We saw several errors on the SharePoint application showing up for Application EventIDs that led us to a VMWare forum that suggested disconnecting the CD/DVD drive from the SQL and SharePoint servers. Since doing that the environment has been stable and there have been no HRESULT: 0x80131904 errors. From other forums it appears the issue is related to the location from which the media is used for installation.

-
https://social.technet.microsoft.com/Forums/office/en-US/21627022-f910-4bc8-a6f6-6e70f9b0ebff/exception-from-hresult-0x80131904?forum=sharepointadminprevious


the root cause of the problem was that the location where our SQL server log files are being written to ran out of drive space (i.e. this being the SQL

StackExchange Database Administrators Q#141606, answer score: 15